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?

Answer 1

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.

Answer 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();