How to make the directory filter work according to the OR principle?

Question

I have a problem with filtering elements. I have a product catalog and have an item filter. The filter has a set of checkboxes. The number of products corresponding to the property is indicated in parentheses in the filter. Visually it looks like this:

It looks like everything is fine, but there is a problem. If I check the box in the "genre" section for "horror", and in the "specials" section I check the box for "multiplayer". That's what I see:

This is the wrong logic. It displays a list of products that have genre = horror AND specials = multiplayer, while I'm looking to display products according to the logic of genre = horror OR specials = multiplayer. According to the logic I need, not one game should be displayed, but four games!

This is how the records I need are being pulled out now:

{% set games = craft.entries().section('games') %}

{% set genreGet = craft.app.request.getQueryParam('genre') %}
{% if genreGet %}
    {% do games.genreGame(genreGet|split(',')).all() %}
{% endif %}

{% set specialsGet = craft.app.request.getQueryParam('specials') %}
{% if specialsGet %}
    {% do games.specialsGame(specialsGet|split(',')).all() %}
{% endif %}

Is there any way to make this work on the OR principle?

---

---

---

---

FINAL! Thanks to the guys Moritz Lost and Mats Mikkel Rummelhoff. They suggested how to do it better. Here is the final solution:

{% set games = craft.entries().section('games') %}

{% set elements = [] %}
{% set fields = [] %}

{% set genres = craft.app.request.getQueryParam('genres') %}
{% if genres %}
    {% set elements = elements|merge(genres|split(','))%}
    {% set fields = fields|merge(['genreEntriesGame'])%}
{% endif %}

{% set specials = craft.app.request.getQueryParam('specials') %}
{% if specials %}
    {% set elements = elements|merge(specials|split(','))%}
    {% set fields = fields|merge(['specialsEntriesGame'])%}
{% endif %}

{% if elements|length > 0 and fields|length > 0 %}
    {% do games.relatedTo({
        element: elements,
        field: fields
    }) %}
{% endif %}

{% set games = games.all() %}

The only thing I did not use the array syntax (genre[], specials[]) syntax as MoritzLost advised. When I use array syntax (genre[], specials[]) in input fields, my URL becomes very long. The names of the parameters in the url are repeated very many times then. Therefore, I used JS to process the selected checkboxes, took their id and wrote them in a separate input field, thereby getting something like this genre=4536,1457 in my URL. So I used |split(',')

Answer 1

You can use a relatedTo parameter to limit your query to all games that are related to one of multiple elements. This is assuming that both specialsGame and genreGame are entries fields or another kind of element relation field (like tags or categories).

The following code assumes you're using element IDs for the values of your checkboxes.

{% set gamesQuery = craft.entries().section('games') %}

{% set genre = craft.app.request.getQueryParam('genre') %}
{% set specials = craft.app.request.getQueryParam('specials') %}
{% set selectedFilters = [genre, specials]|filter %}

{% if selectedFilters %}
    {% do gamesQuery.relatedTo({
        element: selectedFilters,
        field: ['genreGame', 'specialsGame']
    }) %}
{% endif %}

The documentation on relations has more examples on how to narrow your query further. Note that OR logic is the default in this case (so the query is limited to all games that are limited to one of the specified elements).

If you want to support multiple selection for both filters, change your input names to use array syntax (specials[] and genre[]), this will cause PHP to parse those as arrays. Then merge those arrays together:

{% set genre = craft.app.request.getQueryParam('genre') ?? [] %}
{% set specials = craft.app.request.getQueryParam('specials') ?? [] %}
{% set selectedFilters = genre|merge(specials)|filter %}

Answer 2

For multiple select/checkboxes fields, you can use the search index.

{% set games = craft.entries().section('games').search('genreGame:horror OR specialsGame:multiplayer').all() %}

Note that you would need to enable searching on those fields in the CP first: https://craftcms.com/docs/3.x/searching.html#configuring-custom-fields-for-search

Answer 3

Generally, and as @MoritzLost's answer suggest, using elements (i.e. entries or categories) and the relatedTo param instead of Checkboxes for categorisation, is definitely going to be a better idea in most cases, and the approach I'd recommend.

That said, one option for using an OR operand to query entries on multiple, non-relational custom fields, is by creating a so-called "advanced" element query.

Essentially, an advanced element query is any element query that uses one or several methods from Craft's base query builder's – like .where(), .join(), .distinct() etc.

The biggest thing to know about advanced element queries is that unlike "regular" queries, you will usually have to specify the full column name for any custom field(s) used in their methods. For a custom field with the handle genreGame, the actual column name will look something like field_genreGame_asfljksa. That last part is a random suffix – this is possible to determine programmatically, but it should also be fine to hard code it as it's not supposed to ever change between environments.

Essentially, here's how an advanced query that'll almost do what you need could look, using .orWhere() statements:

{% set games = craft.entries().section('games') %}
...
{% do games.orWhere(['=', 'field_genreGame_asfljksa', '["horror"]']) %}
...
{% do games.orWhere(['=', 'field_specialsGame_xsaoefasj', '["multiplayer"]']) %}

The "almost" is due to the fact that since you're going to be querying on Checkboxes fields, you'll need to account for entries that have more than one value checked (i.e. selected) in those fields.

Additionally, Checkboxes fields store their selected values in the content database table as JSON (note the ["horror"] in the example query above, instead of just horror). This means that you can't actually use a simple = operator for the .orWhere() comparisons.

One option for adding support for multiple, serialized values is to create LIKE statements for all your possible matches found in the genreGet and specialsGet comma separated GET variables, i.e. something like this:

...
{% for genre in genreGet|split(',') %}
    {% do games.orWhere(['like', 'field_genreGame_asfljksa', '"' ~ genre ~ '"']) %}
{% endfor %}
...

If that seems a bit squishy and/or verbose, I concur!

Another option is looking into the JSON_EXTRACT() and JSON_CONTAINS() SQL functions, but this stuff does get gnarly quite fast IMO.