Eloquent query distinct sub id

Question

I have these models

I want to make a query that shows me all the products whose stock quantity> 0 and that does not repeat the products.

My query:

$stock_products_limit = Stock::distinct('product_id')->where('quantity', '!=', 0)->get();

This would be much easier using a size chart relating it to stocks ... but for now I don't have it

I need the model to return me, and then do a foreach:

@foreach($stock_products_limit as $stock_product)
 @foreach($stock_product->product->product_images as $i=>$product_image)
  ...
 @endforeach
...
@enforeach

In my models I have the hasMany and belongsTo relations made

How could I make the query? I've been trying the distinct, group by ... but nothing works for me. It only removes the ones with quantity 0 and repeats the product ID ...

Example of the query I want:

SELECT DISTINCT(stocks.product_id)
FROM stocks
INNER JOIN products ON stocks.product_id = products.id
WHERE quantity != 0
ORDER BY product_id
LIMIT 10;

Another example query (but LIMIT doesn't work with IN)

SELECT * from products where id in (SELECT DISTINCT(product_id)
    FROM stocks
    INNER JOIN products ON stocks.product_id = products.id
    WHERE quantity != 0
    ORDER BY product_id)

Kevin Bui Kevin Bui · Accepted Answer · 2021-01-14T22:30:29

Instead of making the Stock model as the starting point, you might want to use the Product model. Then you don't even have to think about using DISTINCT. Let's use whereHas

return Product::whereHas('stocks', function ($query) {
    $query->where('quantity', '>', 0);
})
->limit(10)
->get();

Eloquent query distinct sub id

1 Answers