query after "limit()"?

I want to do something like read the last item and put the where condition on it, inside the relationship. Something like this:

if (request()->flm_long_ticket) {
    $sot->whereHas('status_work', function($query) {
        $query
            ->where('status_work_code', 'OPN')
            ->orderBy('created_at', 'desc')
            ->limit(1)

			// here I tried to "where condition" the last item of OPN            
            ->where('created_at', '<', now()->subhours(2));
    });
}

But, of course is not working cause limit is work in the last step of queries. How can I achieve this? I really want to do this inside "sql" area, not after it's a collection...

Tray2

1 year ago

Level 73

That does not work, you have a order by created_at and limit 1, that means it fetches the newest records, one record only. There is no need to put any additional where clauses after. If you need to filter more you should add the condition between the original where and the order by.

   $query
            ->where('status_work_code', 'OPN')
            ->where('created_at', '<', now()->subhours(2))
            ->orderBy('created_at', 'desc')
            ->limit(1)

1 like

ioiofadhil

1 year ago

Level 1

@Tray2 U r right! But I was trying to put the "where" logic only for the last "OPN" item, not all of it and take the last. Turns out a SUBQUERY is the answer for my case!

$sot->whereHas('status_work', function($query) {
    $query
        ->where('id', function($query) {
            $query->select('id')
                ->from('sot_status_work')
                ->whereColumn('service_order_ticket_id', 'service_order_ticket.id')
                ->where('status_work_code', 'OPN')
                ->orderBy('id', 'desc')
                ->limit(1);
        })
        ->where('created_at', '<', now()->subHours(2));
});

Big thanks to you for helping my problem! If you have any other way to do this, I will very much appreciate it!

Tray2

1 year ago

Level 73

@ioiofadhil You can chain the where clauses.

Record::query()
  ->where('tenent_id', $request->tenant_id)
  ->where('type_id',  $request->type_id)
  ->where('size', 'L')
  ->orderBy('prize')
  ->limit(10)
  ->get();

1 like

Please or to participate in this conversation.