0

I have a table with a column containing comma separated values. I want to know how to build the eloquent to filter a query.

Example Table

id name some_values status
1 London 0,0,1,null,0 online
2 Paris null,null,null,null,null online
3 Dublin 0,null,null,null,null online
4 Wien 1,null,null,null,null online
5 Madrid 1,null,null,null,null offline

Example. A query should return all entries with status online and 1,x,x,x,x. How do I do that?

My thought was to work with a regular expression. but I have the feeling that this could lead to performance problems. The table should contain about 250.000 entries.

With Laravel Eloquent: ->where('some_values', 'regexp', '1,[null|0|1],[null|0|1],[null|0|1],[null|0|1]')->get();

And also my pattern has problems with the fields that contain a value null null. so 1,null,1,0,0 would not be recognized.

Thank you in advance!

Maik Lowrey
  • 15,957
  • 6
  • 40
  • 79
  • 2
    `where('some_value', 'like', '1,%')` ? – lagbox Jan 15 '22 at 15:29
  • @lagbox Thank you. And if i try to fetch rows with the pattern `x,x,x,1,x`? – Maik Lowrey Jan 15 '22 at 15:37
  • Does this answer your question? [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – Progman Jan 19 '22 at 22:01
  • @Progman Thank you! that is a very useful reference. It helps me to rethink my existing structure. This would not completely answer the question, but it would make it obsolete. – Maik Lowrey Jan 20 '22 at 06:49

0 Answers0