CODEWITHSUNDEEP
laraveljoineloquent
maxiw46
maxiw46

Reputation: 131

Laravel Eloquent Join but only show max row from relationship

I have two Models Customer, Contact with the following relation:

// Customer Model
public function contacts() {
    return $this->hasMany('Contact', 'customer_id')    
}

Contacts look like this:

id | created_at | body | customer_id
1  | 2018-01-03 | text | 1
2  | 2018-01-01 | text | 2
3  | 2017-12-25 | text | 1
4  | 2017-10-13 | text | 2
5  | 2017-10-03 | text | 2

Now I want to create a view with a list of all my customers including the latest contact per customer with only one row per customer. It shall look something like this:

Customer ID | Name | Last Contact Date
1           | John | 2018-01-03
2           | Max  | 2018-01-01
...         | ...  | ...

I already tried to achieve this with something like

// Customer Model
public function contactsList () {
    return $this->contacts()
                ->leftJoin('contacts', 'customers.id', '=', 'contacts.customer_id')
                ->groupBy('customer.id')
                ->orderByRaw('contacts.created_at desc');
}

but it's not what I expected. Can someone help me with this?

Thanks a lot!

Upvotes: 1

Views: 229

Answers (1)

Alexey Mezenin
Alexey Mezenin

Reputation: 163968

To make it work you need to create a new hasOne() relationship in Customer model:

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

Then use it:

$customersWithLatestContact = Customer::with('latestContact')->get();

Upvotes: 3

Related Questions