3

I have these 2 tables,

customers                keys

 id   name                id    key   customerid      date
 ___________             ____________________________________
  1    name1               1     1        1        2016-11-17
  2    name2               2     1        1        2016-11-19      
                           3     2        1        2016-11-18
                           4     1        1        2016-11-18

I want to get for every customer the ids of each key(group by key) having the max date

Customers::with(['keys => function($query){
    $query->groupBy('key')
          ->select('id',DB::raw('max(date)'));
}])
->get();

Now I'm getting the max date and the id of the first row not the id of the record having the max date .

In mysql :

select a.id, a.date, a.key
from keys a
where a.customerid = 1
and a.date = 
    (select max(date)
     from keys b
     where b.key = a.key
     and b.customerid = a.customerid)

This query returned the result I want but for 1 specified customer, I need to integrate it in my laravel query.

Nour Nehme
  • 31
  • 2
  • @Machavity my question is not a duplicate, I need my answer in laravel framework not mysql, all mysql queries are not working for my case in laravel. I updated my question . – Nour Nehme Nov 18 '16 at 14:21

0 Answers0