How to make a scope with a raw SQL query in Laravel 7?

user_15639021 0
Viewed 541

Problem

I am trying to make a scope in my model with this SQL query:

SELECT *
FROM ro_container_events
WHERE (container_id, location_timestamp, id)
IN (
    SELECT distinct container_id, MAX(location_timestamp) AS lts, MAX(id) AS rce_id
    FROM ro_container_events
    GROUP BY container_id
)

The model name is ContainerEvent and the name of the table in my database is ro_container_events.

Also, I know that the SQL query is valid because I ran it in my MySQL administration tool (HeidiSQL), and it returns the good rows.

What I've tried

My scope (in my ContainerEvent model) currently looks like this:

public function scopeLatestEventForContainers($query)
    {
       return $query->select(DB::raw('
            SELECT *
            FROM ro_container_events
            WHERE (container_id, location_timestamp, id)
            IN (
                SELECT distinct container_id, MAX(location_timestamp) AS lts, MAX(id) AS rce_id
                FROM ro_container_events
                GROUP BY container_id
            )'
       )
       );
    }

But it doesn't return any rows?

My research

I've been searching about this topic for a while but can't seem to find what is wrong with my scope...

The Laravel documentation says that we can use :

DB::raw('...')

In order to make a specific SQL query.

And I've seen on some other threads that I should be able to make a scope with the following :

return $query->select(DB::raw('...');
laravel-backpack laravel mysql sql php
2 Answers

try Eloquent:

//as your where in select this must return just one row with 3 field
$in =  ContainerEvent::distinct('container_id', 'location_timestamp', 'id')->where(fn($q) => {
   'id' => ContainerEvent::max('id')
   'location_timestamp' => ContainerEvent::max('location_timestamp')
])->get();

but it may be wrong because there is ambitious in db structure.

user_59872590

I just try your SQL syntax using Laravel query builder, and the result using method toSql() returns the exact thing that you need.

SELECT * FROM ro_container_events WHERE (container_id, location_timestamp, id) IN (
    SELECT distinct container_id, MAX(location_timestamp) AS lts, MAX(id) AS rce_id
    FROM ro_container_events
    GROUP BY container_id
)

Try this function:

public function scopeLatestEventForContainers($query)
{
    return $query->whereIn(\DB::raw('(container_id, location_timestamp, id)'), function ($q) {
        $q->distinct()->select(\DB::raw('
            container_id,
            MAX(location_timestamp) AS lts,
            MAX(id) AS rce_id FROM `ro_container_events`
        '))->groupBy('container_id');
    });
}

Hope that's help :)

Best, vreedom18

user_86530540
Related