CODEWITHSUNDEEP
phplaravel
TJ909
TJ909

Reputation: 15

Laravel Collection group by with max value

I have what is hopefully a quick question regarding Laravel's collections. What is the best way to group by a certain id and only take entries meeting a certain criteria? Using the meetings table example below, I am trying to take the most recent meeting for each company_id. In other words, I would like to reduce the array below to only ids: 2, 5, and 8.

id | company_id | date       | topic  | attendees
1  | 1          | 2015-06-30 | Intro  | 5
2  | 1          | 2015-09-30 | Update | 2
3  | 1          | 2013-03-30 | Update | 4
4  | 2          | 2014-03-30 | Intro  | 3
5  | 2          | 2015-01-15 | Update | 8
6  | 3          | 2012-10-15 | Intro  | 12
7  | 3          | 2015-06-15 | Update | 5
8  | 3          | 2015-09-15 | Update | 2

Upvotes: 1

Views: 2959

Answers (2)

user1669496
user1669496

Reputation: 33058

Here is how I usually handle these situations. You group by the company id in a subquery and join on the max date and where the company id's match.

$subQuery = "(SELECT
                company_id,
                MAX(DATE) AS `date`
            FROM meetings
            GROUP BY company_id) max_date";

$res = DB::table('meetings')
    ->select('meetings.*')
    ->join(DB::raw($subQuery), function($join) {
        $join->on('max_date.company_id', '=', 'meetings.company_id')->on('max_date.date', '=', 'meetings.date');
    })
    ->get();

Upvotes: 0

trincot
trincot

Reputation: 350252

Try something like this:

$latest = DB::table('meetings as latest')
     ->whereNotExists(function ($query) {
         $query->select(DB::raw(1))
               ->from('meetings')
               ->whereRaw('company_id = latest.company_id')
               ->whereRaw('date > latest.date');
     })
     ->get();

The idea is to only take those records where there does not exist a record for the same company that has a later meeting date.

Upvotes: 1

Related Questions