Laravel WhereIn with multiple options in the field itself

Question

Normally a whereIn in Eloquent compares a value from a field to an array with options. I like to reverse that and compare a option to multiple options in the field:

field contains 'option1,option2,option3'

Model::whereIn('field', 'option1')->get();

Is this possible?

Answer 1

You can make your query using LIKE:

Model::where('field', 'LIKE', '%option1%')->get();

Documentation on the syntax is available here: http://dev.mysql.com/doc/refman/5.7/en/pattern-matching.html

If you always add a comma , even after the last choice, like option1,option2,option3, you can use a bit of a more robust filter:

Model::where('field', 'LIKE', '%option1,%')->get();

And a comma at the start (or any other separator if that matters) would make it even better:

Model::where('field', 'LIKE', '%,option1,%')->get();

Otherwise you can have issues if one of your option is similar to another one at the end (if you have fish and goldfish as possible categories, using LIKE ',fish,' will guarantee that you don't match goldfish, while LIKE 'fish,' would match both fish and goldfish).

I'd recommend to store your categories like that: /fish/goldfish/water/ and then filter using LIKE '%/yourcategory/%'