Laravel 8 mysql query with join to get latest record only if older by specific condition

Question

Hello i have a query im trying to run with laravel 8 where i have 2 tables (drivers and cards) 1 driver can have multiple cards. Each card has a expiration. Im trying to get a list of the drivers and the cards that are expiring within 30 days or already expired HOWEVER i need to get only the latest expiration 1 and see if that is expiring within 30 days since they can have uploaded a newer one expiring later

So currently i have this query

DB::table('drivers')
    ->join('driver_card', 'drivers.driver_id', 'driver_card.driver_id')
    ->select('driver_card.*', 'drivers.email', 'drivers.first_name', 'drivers.last_name')
    ->where('drivers.is_active',1)
    ->where('driver_card.is_active', 1)
    ->where('drivers.id_company', $id_company)
    ->where('driver_card.expiration_date', "<=", date('Y-m-d', strtotime('+30 days')))
    ->paginate(25);

The problem with this is if "Joe" has a card that expires in 25 days but they also have a card that expires in 180 days the 25 day expiration card shows up but it shouldnt since the latest expiration card they have is greater than 30 days. Hope that makes sense anyone know how to do this.

So i have a raw sql Query that works how i wanted:

  select a.first_name, a.last_name , b.* from drivers a 
  join driver_card b on a.driver_id = (
      select driver_id from driver_card where  id_company = 5     order by expiration_date DESC limit 1
   )
  where a.is_active = 1
  and b.expiration_date <= '2022-05-31'
 and b.driver_id NOT IN (select driver_id from driver_card     where expiration_date > '2022-05-31')

Answer 1

use group by and having to sortout your result

like below

DB::table('drivers')
    ->join('driver_card', 'drivers.driver_id', 'driver_card.driver_id')
    ->select('driver_card.*', 'drivers.email', 'drivers.first_name', 'drivers.last_name',DB::raw('case when max(`created_at`)<date("Y-m-d", strtotime("+30 days")) then 1 else 0 end as `isTrue`'))
    ->where('drivers.is_active',1)
    ->where('driver_card.is_active', 1)
    ->where('drivers.id_company', $id_company)
    ->where('driver_card.expiration_date', "<=", date('Y-m-d', strtotime('+30 days')))
->groupBy('driver_card.expiration_date')
->having('isTrue',1)
    ->paginate(25);
    

Answer 2

Try adding another join to the same cards table to pick only rows with a higher date for each driver

DB::table('drivers as d')
    ->join('driver_card as c', 'd.driver_id', 'c.driver_id')
    ->leftJoin('driver_card as c1', function ($join) {
                $join->on('c.driver_id', '=', 'c1.driver_id')
                     ->where('c.expiration_date', '<', 'c1.expiration_date');
     })
    ->select('c.*', 'd.email', 'd.first_name', 'd.last_name')
    ->whereNull('c1.driver_id')
    ->where('d.is_active',1)
    ->where('c.is_active', 1)
    ->where('d.id_company', $id_company)
    ->where('c.expiration_date', '<=', date('Y-m-d', strtotime('+30 days')))
    ->paginate(25);

Laravel get latest record for each group

Answer 3

So i was able to figure it out if this may help anyone:

 DB::table('drivers')->join('driver_card', 'drivers.driver_id', 'driver_card.driver_id')
        ->select('driver_card.*', 'drivers.email', 'drivers.first_name', 'drivers.last_name')
        ->where('drivers.is_active',1)
        ->where('driver_card.is_active', 1)
        ->where('drivers.id_company', $id_company)
        ->where('driver_card.expiration_date', "<=", date('Y-m-d', strtotime('+30 days')))
        ->whereNotIn('driver_card.driver_id', DB::table('driver_card')
            ->select('driver_id')
            ->where('expiration_date', '>', date('Y-m-d', strtotime('+30 days')))
            ->get()
            ->pluck('driver_id')
        )
        ->paginate(25);

This basically gives what i wanted where if they have a expiration date for any card over 30 days then skip that driver because that means renewal has been uploaded already