Laravel Eloquent how to join on a query rather than a table?

Solution:

If you want to make a single query for efficiency reasons in this case Eloquent is not going to help you much, it is possible but is a hassle. For cases like this you have QueryBuilder.

DB::table('products')
    ->join(
        DB::raw('(SELECT product_id, MIN(price) AS lowest FROM prices GROUP BY product_id) AS q1'),
        'products.id', '=', 'q1.product_id'
    )
    ->orderBy('q1.lowest')->get();

If you change get() for getSql() you get the following

select * from `products` inner join
(SELECT product_id, MIN(price) AS lowest FROM prices GROUP BY product_id) AS q1
on `products`.`id` = `q1`.`product_id` order by `q1`.`lowest` asc

Unfortunately as far as I know you can’t use a subquery without DB::raw, nevertheless it is not insecure as long as you don’t put user input in the query. Even in that case you can use it securely by using PDO.

As for Eloquent, your product model doesn’t even have a price field (it probably has a prices() function returning a relationship object) so it makes no sense to get a Product model with a single price asociated to it.

Edit:

You can also eager load the relationship, i.e. (assuming you have the model relationship set as Trong Lam Phan’s example)

$products = Product::with('prices')->get();
foreach ($products as $product)
{
    $minPrice = $product->prices->min('price');
    // Do something with $product and $minPrice
}

This will only run a single query but the min() operation is not done by the database.