Join using "OR"

[gauravojha]

@czim

First of all let me start by saying, this is perhaps the best package I have used in terms of usability.

Secondly, I would like to apologize. I really am in a fix, and I dont know where to post this to get a response on.

I have created a simple Filter structure from the examples to learn how to use this. Now if you look at the structure in the attached file, you will see that there are two strategies "methods" and "materials".

When I pass in an array to materials (say [1,2]), it means that to return all products with material_id 1 or 2. Similarly in case of methods.

But if I pass both, say materials=1 and methods=2, the resulting query is an "AND" operation, that is show all products with material_id = 1 AND method_id = 2.

But I dont see any help as to perform an OR operation. I will paste the query below for reference.

Query which is fired now (notice the and)
select distinct * from products left join method_product on method_product.product_id = products.id left join material_product on material_product.product_id = products.id where method_product.method_id in (1, 2) and material_product.material_id in (3) order by price_per_metre asc limit 10 offset 0

Query which I would like to be fired
select distinct * from products left join method_product on method_product.product_id = products.id left join material_product on material_product.product_id = products.id where method_product.method_id in (1, 2) or material_product.material_id in (3) order by price_per_metre asc limit 10 offset 0

Now, I can fire raw sql queries to achieve this, but I dont want to. I would very much like to use this package instead. I hope there must be some way to do so. If there is, then can it be included in the examples section?
ProductFilter (copy).txt

[czim]

For this simple use-case, you can definitely use the package as is. Just use Eloquent's orWhere.

So:

$query->orWhereIn('method_product.method_id', $value)
    ->distinct(); // Might have multiple matches per product

$query->orWhereIn('material_product.material_id', $value)
    ->distinct(); // Might have multiple matches per product

However, I realize that with more complicated combinations, such as an X or (Y and Z) type setup, is harder to do. You can easily do this within a filter strategy by using an anonymous function and only using orWhere within it; but to combine some strategies with or and some with and is not easily possible.

Let me know if you need this, and I'll see if I can think of some useful implementation for this.