In Laravel How to Get Data in One Query from 3 Tables

Laravel Eloquent - Get data from 3 tables with consecutive foreign keys

Basically, it is not as much related to laravel it needs knowledge of the database.
You can join 3 tables like this.

$join = DB::table('a')->join('b', 'b.b_id', 'a.b_id')->join('c','b.c_id','c.c_id')->select('c.c_id','c.c_cval')->get();

Laravel: How to get data from 3 tables with relationship

If you want the latest record of the contacts you can declare another relationship on the Customer model, e.g.:

public function latest_contact()
{
    return $this->hasOne(Contact::class)->latest('contact_date');
}

BTW you can always declare one or more hasOne additional relationship if you have a hasMany in place the foreign key used is the same.

In this way you can retrieve latest_contact eager loaded with your Customer model:

$customer = Customer::with('latest_contact')->find($id);

Or use this relationship in your queries, something like that:

$customers = Customer::where('is_active', 'YES')
    ->with('sales')
    ->with('contacts')
    ->whereHas('last_contact', function ($q){
        return $q->where('result', 'UNCALLED');
    })->get();

Or that:

$customers = Customer::where('is_active', 'YES')
    ->with('sales')
    ->with('contacts')
    ->with('last_contact', function ($q){
        return $q->where('result', 'UNCALLED');
    })->get();

If you want you can declare last_contact with the additional where:

public function latest_contact()
{
    return $this->hasOne(Contact::class)
        ->where('result', 'UNCALLED')
        ->latest('contact_date');
}

This way all other queries should be easier.
I hope this can help you.

How can I fetch value from 3 tables using laravel relationship?

You'll want to use a whereHas to query the relationship.

$categoryId = 1;
$productQuery = function ($query) use ($categoryId) {
    // This $query object will be for the Product models, so we can treat it as
    // such.
    // We can query like we would on a Product, like Product::where([...]).
    $query->with('category')->where('category_id', $categoryId);
};
$blogs = Blog::whereHas('product', $productQuery)
->with(['product' => $productQuery])
->get();

I've set the category ID to a variable, in case you need to change it during runtime.

Also, note that the with is completely optional.

If you exclude it, your query will run exactly the same, just without constrained eager loading. The effects of this are just that you will have to perform more database requests. The benefits come if you never actually need the relationship, then it won't have been fetch unnecessarily.

If you're curious what the SQL command will be, it will be:

SELECT * FROM `blogs`
WHERE EXISTS (
    SELECT * FROM `products`
    WHERE `blogs`.`product_id` = `products`.`id` AND `category_id` = ?
)

In simple terms, it will select everything from the blogs table.

It's then going to query the products table, using an inner join, to select products that have a corresponding blog entry.

The second part of the where clause is going to just get the specific character. The ? is because category_id can be any integer.

Laravel Eloquent, How to access eloquent on 3 tables with where?

Since this is concatenation problem, i would not use standard relationships for this. Instead let mysql solve this, by creating a custom query in the ORM.

The strategy is to join all the rows, group it by the name of the product. Sum the quantity and create the where condition for the dates.

$items = Item::select('name', DB::raw('SUM(details.qty) as quantity'))
    ->join('details', 'items.id', '=', 'details.item_id')
    ->join('productions', 'productions.id', '=', 'details.production_id')
    ->whereBetween('productions.date', [now()->subMonth(), now())
    ->groupBy('details.name')
    ->get();

You could loop the data like so.

foreach ($items as $item) {
    $item->name;
    $item->quantity;
} 

---

Related Topics