My code

$datas = DB::table('patrol_gate_surveillance_transactions as a')
    ->leftJoin('client_locations as b','a.client_location_id', 'b.id')
    ->leftJoin('clients as c', 'b.client_id', 'c.id')
    ->select('a.client_location_id as location_id', 'b.name as location_name', 'c.name as client_name',
        DB::raw('SUM(CASE WHEN a.type = 1 THEN employee_qty ELSE 0 END) as e_qty_in'),
        DB::raw('SUM(CASE WHEN a.type = 2 THEN employee_qty ELSE 0 END) as e_qty_out')
    )
    ->groupBy('location_id', 'location_name', 'client_name')
    ->get();

I want to get the last created_at and updated_at only

My table

dd()

i need created_at and updated_at only for parsing data to blade

CodePudding user response：

I have no idea why you are not using relationships, but please, use them, that is the idea of using Eloquent...

If you just want the last row (based on created_at), you must do:

$datas = DB::table('patrol_gate_surveillance_transactions as a')
    ->leftJoin('client_locations as b','a.client_location_id', 'b.id')
    ->leftJoin('clients as c', 'b.client_id', 'c.id')
    ->select('a.client_location_id as location_id', 'b.name as location_name', 'c.name as client_name',
        DB::raw('SUM(CASE WHEN a.type = 1 THEN employee_qty ELSE 0 END) as e_qty_in'),
        DB::raw('SUM(CASE WHEN a.type = 2 THEN employee_qty ELSE 0 END) as e_qty_out')
    )
    ->groupBy('location_id', 'location_name', 'client_name')
    ->latest()
    ->first();

I have used latest() and first().

latest: it will just do ORDER BY created_at DESC
first: you want to get only one row, so you use first (it will return a Model, not a Collection)

CodePudding user response：

After joining tables:

DB::table( ... )
    ->latest('id') /* order id by decending */
    ->first() /* get the first row */
    ->value('created_at');

Docs: https://laravel.com/docs/9.x/queries