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

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

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!

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.

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!

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.

1 Like

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