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.
CodePudding user response：
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);
CodePudding user response：
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);