Additional constraints using one-of-many relations in Laravel

One of many is a new type of relationship that has been added to Laravel in the latest minor version which adds some amazing possibilities. The relationship adds the ability to load a single record from a hasMany relation using an aggregate to determine the “sorting” of the records. For example, this allows you to get the record with the newest created_at date from a hasMany relation.

This is a feature I’m very excited about as it will allow me to easily make a relation to fetch the best price for a product on my price comparing website. I simply have to define a new relation on my Product model like so:

public function lowestPrice(): HasOne
{
    return $this->hasOne(ProductPrice::class)->ofMany('price', 'min');
}

Behind the scenes, a fairly complex query is being built. This relation will use nested join queries to get the id of the related record with the lowest price and fetch only that record.

select * from `product_prices`
    inner join (
        select MIN(id) as id, `product_prices`.`product_id` from `product_prices`
        inner join (
            select MIN(price) as price, `product_prices`.`product_id` from `product_prices`
            where `product_prices`.`product_id` = 1
            group by `product_prices`.`product_id`) as `lowestPrice`
        on `lowestPrice`.`price` = `product_prices`.`price` and `lowestPrice`.`product_id` = `product_prices`.`product_id`
        group by `product_prices`.`product_id`) as `lowestPrice`
    on `lowestPrice`.`id` = `product_prices`.`id` and `lowestPrice`.`product_id` = `product_prices`.`product_id`
where `product_prices`.`product_id` = 1

Additional constraints

This was all great, but I wanted the lowest price to be for a store that had the product in stock. So, I added the following constraint to the relation.

return $this->hasOne(ProductPrice::class)->ofMany('price', 'min')
    ->where('in_stock', true);

Suddenly, most of the products in the database started returning null as their lowestPrice record. Which was not the expected result.

It seemed that any extra clauses added to the relation are added directly to the end of the generated query. Meaning the database will first get the record with the lowest price and then check if the record it found matches the condition. If this is not the case, no records are returned.

The solution

There is currently no documented example on how to do something like this, so I had to dig through some framework code to find a solution. And I found it in one of the test cases for this new feature.

It turns out the ofMany function allows to pass the column and aggregation method as an array and then pass a query modifier function as a callback. This way, the callback will be applied to the most inner join query of the relation, allowing you to further filter the records.

So my relationship defenition became this:

return $this->hasOne(ProductPrice::class)
    ->ofMany(
        ['price' => 'min'],
        static function (Builder $query) {
            $query->where('in_stock', true);
        }
    );

And this is the resulting query where, as you can see, the in_stock condition was added to the inner most query:

select * from `product_prices`
    inner join (
        select MIN(id) as id, `product_prices`.`product_id` from `product_prices`
        inner join (
            select MIN(price) as price, `product_prices`.`product_id` from `product_prices`
            where `in_stock` = 1 and `product_prices`.`product_id` = 1
            group by `product_prices`.`product_id`) as `lowestPrice`
        on `lowestPrice`.`price` = `product_prices`.`price` and `lowestPrice`.`product_id` = `product_prices`.`product_id`
        group by `product_prices`.`product_id`) as `lowestPrice`
    on `lowestPrice`.`id` = `product_prices`.`id` and `lowestPrice`.`product_id` = `product_prices`.`product_id`
where `product_prices`.`product_id` = 1