Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

PJijin's avatar

Eloquent query to filter same column values

Hey

I want to get the product_id from this table

Table product_filter

I want to get the product_id from this table. And the conditions are filter_id needs to be 4 AND 3. There may be multiple filter_id, the filter values need to exist.

I can do this SQL query

SELECT f1.product_id
FROM product_filter AS f1
   JOIN
   product_filter AS f2
   ON f2.product_id = f1.product_id
   AND f1.filter_id=2
   AND f2.filter_id=4;

looks like this query is not efficient if there are more filters. Need to make this dynamic.

So my question is how can i perform this Laravel eloquent.

0 likes
14 replies
Tray2's avatar

Yes that query is fubar.

You want to get product filters that has the filter_id of 2 or 3.

In SQL I would do something like this

SELECT product_id
FROM product_filter
WHERE filter_id IN (2, 4);

In Eloquent the same query would look something like this

ProducFilter::whereIn('filter_id', [2, 4])->get();

And to make it more dynamic you just excehange the [2, 4] with a variable passed into the request something like

public function(Request $request)
{
     $filters = $request->filters;
     $products = ProductFilter::whereIn('filter_id', $filters])->get(): 
}

You should of course validate the request->filters before using it in your query

1 like
PJijin's avatar

I want product filters that have both the filter_id values. 2 and 3.

Tray2's avatar

Show me some example data from the table

PJijin's avatar

@tray2

I want to get the product_id that has filter_id 4 AND 2 .

The output will be 42. if I use whereIn then 50 will be there too.

Tray2's avatar

So you only want product_id 42?

SELECT pf.product_id
FROM product_filter pf
WHERE pf.filter_id = 2
AND EXISTS (SELECT pfi.product_id 
                      FROM product_filter pfi 
                      WHERE pfi.product_id = pf.product_id 
                      AND pfi.filter_id = 4);
PJijin's avatar

Yes. Like that. I want to pass array of filter_id to eloquent and it needs to return the product that has all the filter Id values in that.

Tray2's avatar

Not sure that is possible but give this a try

$filter = $request->filter

$products = DB::table('product_filters')
                ->selectRaw("product_id WHERE pf.filter_id in ?' 
                             AND EXISTS (SELECT pfi.product_id  
                             FROM product_filter pfi 
                             WHERE pfi.product_id = pf.product_id 
                             AND pfi.filter_id in ?", $filter, $filter)
                ->get();
willvincent's avatar

I think the way to accomplish this is by getting the count of occurrances of product_id where filter id is one of the required values, and then only selecting where that count is the same number as the number of filter ids..

For example:

SELECT v.product_id
  FROM (
      SELECT product_id, count(*) AS matches 
        FROM product where filter_id IN (2,4)
    GROUP BY product_id
  ) AS v
WHERE v.matches = 2

Obviously to build this dynamically you'd need to replace the IN (2,4) with a placeholder and pass in the array, and replace the where clause to use the count of the array rather than 2.

The output will be product id(s) and can then be used to populate a whereIn clause to load product models.

1 like
ajithlal's avatar
ProductFilter::with('prodcut')->whereHas('product', function($query) {
    $query->whereIn('product_filters.filter_id',array(2,3))
})->get();

Hope this helps.

PJijin's avatar

@ajithlal Does this check the count of the filters. It returns all the products that contain any of the filter value.

siempie93's avatar

@pjijin have you manage to get this working? I have the exact same issue, and need help!

siempie93's avatar

@tray2 But what if there are more than two filters. I also need help with this

Please or to participate in this conversation.