-1

I have a column in database wich represents the languages as shown below:

userId    languages
1         de,fr,en
2         de,fr
3         de

I want to filter the results based on multiple languages, so for example I need the users who speaks de and fr (both).

I tried to use FIND_IN_SET but the first parameter accespts only one language.

I want to mention that the table is a third party and the filter paremeters are dynamic. The example is just some mock data, the explain the principle

Mugurel
  • 198
  • 10
  • 1
    Now you see why placing comma seperated lists in a single cell is not recomended – RiggsFolly Jun 22 '20 at 09:45
  • Do you have a query example that you have tried to code – RiggsFolly Jun 22 '20 at 09:46
  • As several others have said, you should consider normalisation. MySQL and most other structured database engines are extremely efficient at filtering data through indexes - your `FIND_IN_SET` function however renders any index on that data column unusable. You're asking the database engine to examine *every row* in the database table individually *every* time you query it, and you're asking it to perform a very expensive operation (several CSV lookups). If you normalise your database, you can apply some indexes and perform a `HAVING` or `WHERE` in `~O(log N)` rather than `~O` effort. – wally Jun 22 '20 at 09:55
  • You'll know when you've hit the cost of no normalisation here when (assuming you have enough records) lookups take an exceedingly long time. Run an `EXPLAIN SELECT ...` on your query and you'll see how many rows it has to examine (every row, the way you're doing it) and how many indexes were used (none). – wally Jun 22 '20 at 10:00

2 Answers2

1

Direct Answer to the question:

SELECT userId FROM users WHERE find_in_set('de',languages) AND find_in_set('fr', languages)

Suggestion: But you should try to follow Normalization for proper maintenance of database. Please read about 1NF initially and later check the entire concept of normalization.

Chinni Srikar
  • 30
  • 5
  • +1 to normalisation. You'll ultimately hit severe performance issues at any sort of scale with the current table design. SQL (and MySQL by implication) is built on the principle of normalisation, and is wonderful once you understand it! Keep being curious, and keep learning my friend! – wally Jun 22 '20 at 10:05
  • this works fine for this example but the filter is dynamic – Mugurel Jun 22 '20 at 10:08
  • Yes ideally this would be the perfect solution but the table I am depending on is a third party. :D I just need to know how to make the selection not the proper design – Mugurel Jun 22 '20 at 10:19
  • if it is some mock test and you do not consider about the performance, I have a question. Are you running this directly in SQL shell or using it via another programming language? If it is via a programming language, you can write a small function, which adds the find_in_set parts for each queried language – Chinni Srikar Jun 23 '20 at 01:30
0

Then simply do the 2 tests in the where with an AND

SELECT id from <table>
WHERE find_in_set('fr', languages) > 0
  AND find_in_set('de', languages) > 0
RiggsFolly
  • 93,638
  • 21
  • 103
  • 149