1

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: enter image description here

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:

enter image description here

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(',')

8
  • If you include the template code you're using to output the filters it's easier to provide an answer.
    – MoritzLost
    Nov 19, 2022 at 18:20
  • Get rid of the split and change your inputs as mentioned in my answer. The |split filter always returns an array, which will be empty if the query param doesn't exist. So [genres, special] will result in [[], []] – an array with two empty arrays. This is not falsy, so the relatedTo is always applied. So you're asking Craft for all games related to nothing, which matches nothing.
    – MoritzLost
    Nov 22, 2022 at 13:03
  • If you want to support multiple selection, adjust the inputs to use array syntax (genre[], specials[]) and use the second code example in my answer.
    – MoritzLost
    Nov 22, 2022 at 13:05
  • @MoritzLost I have updated the question again (for the third time). Experienced a naked solution. Take a look
    – Dimi
    Nov 22, 2022 at 13:08
  • 1
    @MoritzLost iI have completed the question. You solution is best))) Thx.
    – Dimi
    Nov 22, 2022 at 14:09

3 Answers 3

4

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 %}
9
  • thx for answer, but ... First. genreGame and specialsGame not entries fields! it is a checkboxes fields. I'm afraid your solution won't work (((( Second. URL from which I take the values looks like this: site.com/games?genre=action%2Crpg%2Chorror%2Cadventure&specials=single%2Cmultiplayer%2Cgamepad%2Ccards%2Cachievements therefore, to convert the received data, I use specialsGet|split(',')
    – Dimi
    Nov 21, 2022 at 11:16
  • @Dimi Yeah, that's why you need to include information on your fields as well as the template for your filter form in the question, without that we can only guess what kind of field you're working with ;) All the information on filtering checkboxes with multiple values can be found in the documentation. For filtering across multiple fields with OR-logic, check out my answer here as a starting point: craftcms.stackexchange.com/a/38648/11705
    – MoritzLost
    Nov 21, 2022 at 11:26
  • @Dimi As a sidenote, you should only execute the query once, so use {% do games.genreGame(genreGet|split(',')) %} without the all() at the end.
    – MoritzLost
    Nov 21, 2022 at 11:27
  • i cant understand construction andWhere. my English is not very good))) Need good example. I try to do this: {% set games = craft.entries().section('games').andWhere(['or',['=', '[[field_genreGame]]', ???WHAT_HERE??? ],['=', '[[field_specialsGame]]', ???WHAT_HERE???]]) %}. What should I substitute instead ???WHAT_HERE??? And in general, am I moving in the right direction?
    – Dimi
    Nov 21, 2022 at 17:29
  • i try to do this: {% set games = craft.entries().section('games').andWhere(['or',['=', '[[field_genreGame]]', ['horror'] ],['=', '[[field_specialsGame]]', ['multiplayer]']]) %} but this not work. i get error "Undefined array key 1"
    – Dimi
    Nov 21, 2022 at 18:00
1

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

1
  • No, using search is a very slow method. He won't do here. It's easier for me to redo the type of fields from checkboxes to entries and implement the solution that MoritzLost proposed
    – Dimi
    Nov 21, 2022 at 10:15
1

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.

2
  • Great answer! This is exactly why I avoid checkboxes fields for anything that might need to be searchable. Categories are much easier, especially since it allows editors to add categories without touching the project config. For what it's worth, storing all selected categories always struck me as a weird choice – it would make more sense to store the options in a separate table and use a junction table (or maybe even utilize the relations table) to store the selected options. Though maybe that would be difficult with the current architecture of the content table.
    – MoritzLost
    Nov 22, 2022 at 9:05
  • 1
    @MoritzLost 100%. Some other big drawbacks with using non-relational fields for this stuff is if you ever need to rename an option somewhere down the line, or if you need options translated for different sites. Nov 22, 2022 at 9:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.