Laravel: How to get data from 3 tables with relationship

Question

I have 3 Tables:

Customers

id
name

Sales

customer_id
sale_date

Contacts

customer_id
contact_date

There aren't any update operations in the contacts table. Each process opens a new record in the contacts table. So, a user can have more than one records in the contacts table.

Here are my relations in models:

Customer

public function contacts()
{
    return $this->hasMany(Contact::class);
}

public function sales()
{
    return $this->hasMany(Sale::class);
}

Contact

public function customer()
{
    return $this->belongsTo('App\Customer', 'customer_id');
}

Sale

public function customer()
{
    return $this->belongsTo('App\Customer');
}

I would like to have the latest record of the contacts table and make it join with the other related tables.

Here is the query which I have tried:

$record = Contact::groupBy('customer_id')
        ->select(DB::raw('max(id)'));

$result = Customer::query();
$result->where('is_active', 'YES');
$result->with('sales');
$result->whereHas('contacts', function ($q) use($record){
        return $q->whereIn('id', $record)->where('result', 'UNCALLED');
    });
return $result->get();

In the blade file, I get some result in foreach loops. However, I am unable to get the related data from the sales and contacts table.

@foreach($result as $item)
@foreach($item->sales as $sale) // Has no output and gives error: Invalid argument supplied for foreach() 
@foreach($item->contacts as $contact) // Has no output and gives error: Invalid argument supplied for foreach()

Can anyone help me how to display the sale and contact date? Or any idea for how to improve this code quality?

Have you tried the `protected $with` property in your eloquent model? — Salim Djerbouh, Sep 03 '19 at 14:09
Quick note; relationship names should reflect how many results are being returned. You have `contact` and `sale`, both of which use `hasMany()` and returns more than one, so those really should be `contacts` and `sales` to avoid confusion. `customer` is good, as `belongsTo()` inherently returns a single record. — Tim Lewis, Sep 03 '19 at 14:09
@TimLewis Yes, you're right. Sorry for the misspell. I corrected it. — Madame Green Pea, Sep 03 '19 at 14:20
No worries! Just make sure to fix references too; `$item->sales` instead of `$item->sale` and `$item->contacts` instead of `$item->contact` — Tim Lewis, Sep 03 '19 at 14:21
@MadameGreenPea how are you sending the results to the view ? — N69S, Sep 03 '19 at 14:23
@N69S The query function I shared above is in the file which I'm using it as a repository. I call the function from the controller and then return view. `return view('admin.customer.list', compact('result'));` — Madame Green Pea, Sep 03 '19 at 14:32

dparoli · Accepted Answer · 2019-09-03T14:48:33.397

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.

Thanks. But, I still cannot get the sale date and contact date in the view file. So, unfortunately, the result is the same. I don't understand why. — Madame Green Pea, Sep 03 '19 at 14:58
Well the idea is simple: with a `hasMany` relationship you can declare others `hasOne` and `hasMany` on the same model with additional conditions, try to play with that idea and your data, your queries should be easier. — dparoli, Sep 03 '19 at 15:03

score -2 · Answer 2 · answered Sep 03 '19 at 14:09

-2

I'm not sure, but can you try to do the following:

return Customer::where('is_active', 'YES')
    ->with([
        'sale', 
        'contact' => function ($query) use($record) {
            return $query->whereIn('id', $record)->where('result', 'UNCALLED');
        }
    ])->get();

answered Sep 03 '19 at 14:09

pascalvgemert

1,247
1
13
28

This will return all active `Customer` records, but the original question is adding additional filtering using `->whereHas()`, so this is an incorrect approach. You cause use `->with()` at the same time as `->whereHas()` to limit the returned results of `->contact`, but omitting it is wrong. – Tim Lewis Sep 03 '19 at 14:17

Laravel: How to get data from 3 tables with relationship

2 Answers2