Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to get lastest created_at and updated_at

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

table

dd()

i need created_at and updated_at only for parsing data to blade
enter image description here

Tags:

2

Answers


  1. 0

    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)
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search