How to express subQuery with MIN() without raw SQL?

There are container and weights related as one-to-many. Is it possible to translate this query to Query Builder chain without raw SQL:

SELECT c.*, w.weight
FROM containers AS c
JOIN weights AS w ON w.id = (
    SELECT MIN(id) 
    FROM weights 
    WHERE container_id = c.id
)

Thank you in advance.

jlrdw

6 years ago

Level 75

I usually put the aggregate in a raw, Just example:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')
                ->selectRaw('count(dc_pets.petid) as countOfPets')
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

Results basically give:

ownerid, oname, countOfPets

Like:

5|Bob|3
4|Greg|9
2|Rob|1
// more

There are examples in the query Builder section of the docs.

doublevas

6 years ago

Level 1

Thank you. But I requested expression without raw :)

I already have working expression with

->join('weights', 'weights.id', '=', DB::raw("(
    SELECT MIN(id) 
    FROM weights 
    WHERE container_id = c.id
)"))

but raw is what I am trying to avoid

jlrdw

6 years ago

Level 75

Then why don't you try examples in the chapter: https://laravel.com/docs/6.0/queries

But I prefer eloquent queries, supposed to be the same, but seems to work better.

But only this part is raw

->selectRaw('count(dc_pets.petid) as countOfPets')

A derived needs raw.

doublevas

6 years ago

Level 1

Thus coming to the conclusion that the initial query cannot be fully converted into query builder expression. Negative result is the result. Thanks.

Please or to participate in this conversation.