IN vs FIND IN SET

[t11]

I have a row which holds some values in a comma-separated form
For example 2,3,6,8
Each one of the values represents a foreign key from some other tables.
I am trying to filter these values using an array. There are two ways of doing this
One is the way @Teodor is presenting with filtering using an array and the IN clause

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…

the issue with this approach is that it does not bring results for values that are not at the start. I mean that if the row has 2,3,6,10 and the array contains only the 10 it won’t return anything

The other is @mebeingken using FIND IN SET

GET array for use with IN clause of custom query action

@George and @patrick, Thanks, this was very helpful. For anybody needing to do this in the future, here is what will work inside a wappler custom query: [01%20PM] I found you can use the mysql find_in_set function in lieu of the IN clause. It should be noted that this shouldn’t be used on large sets as the performance will degrade. find_in_set(cast(distro_tags.tag as char), :P4) In my example this adds a WHERE condition requiring the tag field from the distro_tags table to be one of the …

In both cases, although the FIND IN SET should work I am not getting the results I should. Does anyone of you have worked with any of the above to filter results using an array from a row that contains comma separated values?

Thank you!

[sid]

I assume you are trying to do this in a server action’s custom query.
From what i understand, you are trying to search/compare one set of data in other set of data. As far as I know there is no way to search/compare two separate sets directly in SQL.
You will have to loop in some manner.

[t11]

Hi @sid,
thank you for your answer.
Yes, I am thinking that I might have set up the database structure wrong. For what I needed, saving comma-separated values might not be the best approach.

Thank you again!

[sid]

From my experience, using comma separated values sometimes becomes necessary too. Although it should be rare, but it doesn’t mean that its wrong.

I don’t know the scope of the work you are doing, but if you can restructure the DB schema to avoid this, it would be the better approach.

[t11]

I wanted to be able to filter various part numbers of products based on their properties.
Since the products are very different between them, the filters vary and they are very different between each product category. This is why when I was creating a part number I was saving the foreign key of each filter in one row using comma-separated values. But this way it is more difficult to filter them.

So I am going to save each filter foreign key in a new row and use the product’s and the part-numbers’ ids as a foreign key to filter them they way I need.

Thank you