Combine AND and OR in a ElementQuery

Question

I have a category with 2 levels. And I want to be able to let the user do a search based on those. The problem is that it needs to be AND between all level 1 categories, and OR within child categories. So let's say I have a category structure like this:

- Elevator
- Air condition
- View
  - Ocean
  - Mountains
  - Garden

Now when a user selects elevator, ocean and mountains I want to return all entries that have elevator AND (ocean OR mountains)

I tried this: $entries->categories(['and', 695, ['or', 698, 697]]);, but that doesn't work, as it seems to just skip the ['or', 698, 697] part.

Edit for bounty: In addition to the reason given above, there is also a change in the requirement since we added multiple categories. So for one category group, the operator needs to be OR, and some groups should have AND.

Answer 1

If I understand properly, you should be able to do this:

$entries = \craft\elements\Entry::find()
    ->section('yourSection')
    ->category(['and', 695])
    ->relatedTo([698,698])->all();

Assuming category is the name of your relational field, you can use that to pass the and condition (though with one element only it wouldn't be needed) then relatedTo is an or by default (docs)

And as per your edit, pass and condition to your custom fied and or conditions to your relatedTo.

It would be pretty much the same in Twig.

Answer 2

Not able to comment, so trying an answer. Did you tried to build a custom hierarchy by scrolling the content of each level in a custom object ?