Multiple conditional joins

[scott-wmm]

Hey,

I need the ability to do multiple joins with a conditional 'AND' on each one.

For example the current query builder reads:

            $products
                ->join('product_to_categories', 'products.id', '=', 'product_to_categories.product')
                ->where(function($query) use($categories) {
                    foreach($categories as $category) {
                        $query->orWhere('product_to_categories.category', $category);
                    }
                });

However this returns all results that match all the given categories. I need to be able to only return the results that match both. Changing the 'orWhere' to simply 'where' returns no results as the singular JOIN can't match multiple conditions.

The raw query I'm looking for is roughly:

JOIN product_to_categories AS ptoc1
ON ptoc1.product =products.id AND (ptoc1.category_id='4')
JOIN product_to_categories AS ptoc2
ON ptoc2.product =products.id AND (ptoc2.category_id='5')

I've spent a few hours experimenting, can this be achieved using Pixie?

Thank you.

[usmanhalalit]

@TCB13 can you please check the issue?

[TCB13]

@scott-wmm I might be getting the wrong ideia on this, can you provide us with a dump of a a test database / data where I can do some tests?