How to make filters with multiple checkboxes using Where or Having operator?

[AdrianoLuiz]

I’m trying to create a SQL query for filtering cars based on selected options. Currently, my query retrieves cars that have either one or another selected option. However, I want to modify it to only show cars that have all the selected options.

Here is my current SQL query:

The part that I believe needs modification is related to the ‘anuncio_opcionais’ (advertisement options) table, where I’m using the ‘in’ operator to check for selected options. How can I adjust the query to achieve the desired result?

would this be possible with the query builder? Or do I have to custom make?

[famousmag]

Hey @AdrianoLuiz,

If I understand correctly you want all the check boxes to be checked? (not 1 or 2 of them but 3 of them must be checked)

Is it possible to have a hidden text input on client side that it’s value will become true only if all check boxes values are checked, then pass this text input as an input parameter and complete the AND condition in you SQL query?

Just asking if this is doable. …

If the condition is this (checkbox1.checked && checkbox2.checked && checkbox3.checked) it works…

<input id="allCheckboxesTrue" name="allCheckboxesTrue" type="text" class="form-control" value="false" dmx-bind:value="(input1.checked&amp;&amp;input2.checked&amp;&amp;input3.checked)?true:false">

                    <div class="form-check">

                        <input class="form-check-input" type="checkbox" value="" id="input1" name="input1">

                        <label class="form-check-label" for="input1">Checkbox-1</label>

                    </div>

                    <div class="form-check">

                        <input class="form-check-input" type="checkbox" value="" id="input2" name="input2">

                        <label class="form-check-label" for="input2">Checkbox-2</label>

                    </div>

                    <div class="form-check">

                        <input class="form-check-input" type="checkbox" value="" id="input3" name="input3">

                        <label class="form-check-label" for="input3">Checkbox-2</label>

                    </div>

[mebeingken]

I think you are looking at a custom query for this.

[franse]

Hey @AdrianoLuiz
Late here in Argentina, so don’t know if I understand you correctly.
I see you use a split formatter so I assume you use comma separated values, but maybe you can play somehow with this:
Here I go:

ON SC

ON AC

(where checkbox has “1” as a static value)

In your case

ScreenRecorderProject15

Something like that?

[AdrianoLuiz]

Hi, thanks for the answers. I’m going to give you an example. I have several vehicles registered. On the registration screen, I can click on which options this car has, such as an alarm, 4 doors, and electric windows. What I want to do is a search that returns the vehicles with all the options that I marked. For example, if I mark an alarm and electric windows, the search should only return cars that have these two options selected. Do you understand?

[AdrianoLuiz]

that’s right but I’m using a pivot table to persist the data

[franse]

Can you give examples on how “opciones” are received in the query?
Will try something later…

[AdrianoLuiz]

ex: 1,11

Table

[famousmag]

Hey @AdrianoLuiz,

I found this JS solution

(from: https://www.js-craft.io/blog/javascript-includes-multiple-values/)

In your case:
myArray = Car options value array
and
s1 = checked filter options value array
Since we’re talking about arrays, all we have to do is to translate this into wappler’s formatter.
I am not good on this but if I need to I can work it out. I hope it helps you find the solution…

[AdrianoLuiz]

Thanks for the answer but I want to do this in Wappler’s query builder

[franse]

Some test, maybe you can play with this:

select 
anuncioid
from checkboxgroup4
where opcionesid in (items*)
group by anuncioid
having COUNT(distinct opcionesid) = Nº items*

The results are the expected.
But, tried to bind the parameters “items” (:p1 as an array) but have no results, need to pass 1,2 as ‘1’,‘2’.
Maybe someone can help to improve this.

[AdrianoLuiz]

Thank you my friend for trying to help, here I was not successful either

[franse]

Hi Adriano!
Have you find a way?
I could swear I was able to do something like this in the past.

I follow this:

Filtering Database Query with Multiple Checkboxes

You can use multiple checkboxes in order to filter your database queries. In our example we already created a server action with database query which shows the products in a dynamic table. It shows all the products currently, but we want to be able to filter them by category: [Screenshot_16] First let’s add a form. Click the insert (in our case insert before) button, in order to place the checkboxes above the table on the page: [Screenshot_17] Open Forms category and add a From: [Screen…

And the schema is right

@Teodor

You just need to store the amenities in a relational table, which stores the record ID and the amenity associated with it, just as:

product1 - amenity1
prodcut1 - amenity2
product1 - amenity3
product2 - amenity2
...
product99 - amenity1

But i’m not able to get the result.