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

Question

I want to achieve this in Laravel:

SELECT * FROM products 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;

I wrote this, but clearly there is something wrong:

$products = new Product();
$products = $products->join(
    Price::whereNotNull('price')->select('product_id', DB::raw('min(price) as lowest'))->groupBy('product_id'), 'products.id', '=', 'product_id'
    )->orderBy('lowest')->get();

The error I got:

ErrorException in Grammar.php line 39:
Object of class Illuminate\Database\Eloquent\Builder could not be converted to string.

I'm currently using join(DB::raw('(SELECT product_id, MIN(price) AS lowest FROM prices WHERE price IS NOT NULL GROUP BY product_id) AS q1'), 'products.id', '=', 'q1.product_id') as a workaround. Just wondering how to do this in the Eloquent way? Thanks.

Answer 1

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.

Answer 2

In Eloquent, follow me, you need to think a little bit differently from normal mysql query. Use Model instead of complicated query.

First of all, you need to create Product and Price models with relationship between them:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
    public function prices()
    {
        return $this->hasMany('\App\Price');
    }
} 

class Price extends Model
{
    public function product()
    {
        return $this->belongsTo('\App\Product');
    }
}

Then, you need to select all products:

$products = \App\Product::get();
foreach ($products as $product)
{
    $minPrice = $product->prices->min('price');
    echo 'Min price of product ' . $product->name . ' is: ' . $minPrice;
}

EDIT

If you have a problem with the performance, just get the product's id.

$products = \App\Product::get(['id']);

If you don't like this way of Eloquent, you may use Query Builder like that:

$result = \App\Price::join('products', 'prices.product_id', '=', 'products.id')
                ->select('product_id', 'name', \DB::raw("MIN(price) AS min_price"))
                ->groupBy('product_id')
                ->orderBy('min_price')
                ->get();

or you can do like that:

\App\Price::join('products', 'prices.product_id', '=', 'products.id')
        ->selectRaw("name, MIN(price) AS min_price")
        ->groupBy('product_id')
        ->orderBy('min_price')
        ->get()