1

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!

maxiw46
  • 131
  • 1
  • 3
  • 11

1 Answers1

3

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();
Alexey Mezenin
  • 158,981
  • 26
  • 290
  • 279
  • Thank you. That's a lot easier than expected. One more thing: How can handle customers that do not have a contact yet? For those I receive the following errror: "Trying to get property of non-object" – maxiw46 Feb 16 '18 at 08:27
  • @maxiw46 use the `optional()` helper when you're displaying the data `optional($customer->latestContact)->address` or just check it manually with `@if (!empty($customer->latestContact))` – Alexey Mezenin Feb 16 '18 at 08:49
  • 1
    Works great! Thank you so much! – maxiw46 Feb 16 '18 at 08:56