Question 15390 in Laravel Question posted in
The official Laravel Documentation can be found here.

laravel – how get limit one record

can’t get last record from table,
my table has many records me need get only one last..

i have this query:

me need get last join query records

->leftJoin('vtask as avt', function($q) {
                    $q->on('avt.vehicle_id', '=', 't.vehicle_id')
                        ->on('avt.shift_start', '<=', 'dqe.time')->orderBy('avt.shift_start', 'desc');
                })
                ->leftJoin('vreplace as avr', function($q) {
                    $q->on('avr.vehicle_id', '=', 't.vehicle_id')
                        ->on(DB::raw("avr.time"), '>=', 'dqe.time')->orderBy('avr.time', 'desc');
                })

DB::table('dqevent','dqe')
                ->join('transport as t','t.id', '=', 'dqe.transport')
                ->join('vehicle as v','v.id', '=', 't.vehicle_id')
                ->leftJoin('vtask as avt', function($q) {
                    $q->on('avt.vehicle_id', '=', 't.vehicle_id')
                        ->on('avt.shift_start', '<=', 'dqe.time')->orderBy('avt.shift_start', 'desc');
                })
                ->leftJoin('vreplace as avr', function($q) {
                    $q->on('avr.vehicle_id', '=', 't.vehicle_id')
                        ->on(DB::raw("avr.time"), '>=', 'dqe.time')->orderBy('avr.time', 'desc');
                })
                ->leftJoin('ar as r','r.id', '=', DB::raw('COALESCE(avt.route_id, avr.route_id)'))
                ->leftJoin('driver as ad', 'ad.id', '=', DB::raw('COALESCE(avt.driver_id, avr.driver_id)'))
                ->selectRaw(
                    "dqe.id,
                    r.short_name,
                    dqe.lat,
                    dqe.lon
                ")
                ->orderBy('dqe.id','desc')->paginate(15);

get last record from my table 🙂

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    i change my code and insert new index in my table

    Migrate file

    $table->index(['driver_id', 'vehicle_id', 'shift_start', 'shift_end', 'route_id']);

    Controller

    $avt = DB::raw("(select max(avt.id) from vtask  avt where avt.vehicle_id = t.vehicle_id and avt.shift_start <= dqe.time)");

->leftJoin('vreplace as avr', function($q) {
                        $q->on('avr.vehicle_id', '=', 't.vehicle_id')
                            ->on(DB::raw("avr.time"), '>=', 'dqe.time')->orderBy('avr.time', 'desc');
     ->on('avt.id', '=', $avt)
                    })

  2. 0

    To return one last record from your "dqevent" table, you can modify the query with ORDER BY, LIMIT and first:

    DB::table('dqevent','dqe')
    ->join('transport as t','t.id', '=', 'dqe.transport')
    ->join('vehicle as v','v.id', '=', 't.vehicle_id')
    ->leftJoin('vtask as avt', function($q) {
        $q->on('avt.vehicle_id', '=', 't.vehicle_id')
            ->on('avt.shift_start', '<=', 'dqe.time')->orderBy('avt.shift_start', 'desc');
    })
    ->leftJoin('vreplace as avr', function($q) {
        $q->on('avr.vehicle_id', '=', 't.vehicle_id')
            ->on(DB::raw("avr.time"), '>=', 'dqe.time')->orderBy('avr.time', 'desc');
    })
    ->leftJoin('ar as r','r.id', '=', DB::raw('COALESCE(avt.route_id, avr.route_id)'))
    ->leftJoin('driver as ad', 'ad.id', '=', DB::raw('COALESCE(avt.driver_id, avr.driver_id)'))
    ->selectRaw(
        "dqe.id,
        r.short_name,
        dqe.lat,
        dqe.lon
    ")
    ->orderBy('dqe.id','desc')
    ->limit(1)
    ->first();

    You can read about SQL syntax rules on example of PostgreSQL (almost identical to MySQL, Oracle and MS SQL from point of view SQL99 STD).

    SELECT with WHERE filtration

    LIMIT and OFFSET pagination

    ORDER BY ASC/DESC sorting

    GROUP BY and HAVING aggregation

    UNION / INTERSECT / EXCEPT combination

    JOIN joining and more joining

    I know you may use mybatis, doctrine orm, linq, yalinqo, timetoogo pinq, hibernate + spring data jpa, jooq or laravel query builder frameworks, but they are just syntax sugar for SQL99 subdialect.

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

Back To Top
Search