How to make a scope with a raw SQL query?

Hi guys,

So I am trying to make a scope 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 )

My scope 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?

Thanks!

jlrdw

5 years ago

Level 75

    public function scopecountPets($query, $petsearch = '')
    {

        if (ChkAuth::userRole('admin') === 'admin') {
            $sql = "SELECT COUNT(petid) as total FROM dc_pets WHERE petname LIKE :sch";
            $params = [':sch' => $petsearch . "%"];
            
        } else {
           $userid = Auth::user()->id;  //Ignore this is custom code
            $sql = "SELECT COUNT(petid) as total FROM dc_pets WHERE petname LIKE :sch AND ownerid = :userid";
           $params = [':sch' => $petsearch . "%", ':userid' => $userid];
        }
        $sth = DB::getPdo()->prepare($sql);
        $sth->execute($params);
        $kount = $sth->fetch(\PDO::FETCH_OBJ);
        return $kount->total;
    }

Just example....

Please or to participate in this conversation.