Find the matching row in laravel

Question

Here is my Table

Id   | No   |Group
1    | 1    |Alpha
1    | 1,2  |Alpha
1    | 2,4,5|Alpha

How can i find the the row which has No as 5 using laravel eloquent

$Match = MyModel::whereIn('No', array(5))->get();

But it didn't return any rows.

When i try to see the query executed it shows me

select * from `table` where `No` in (5)

How can i do this in php and laravel

You really should normalize your database, because searching for comma-separated values in a table will always be a painful exercise, and never efficient because the database can only ever do a full table scan and never use indexes when searching for that data — Mark Baker, Mar 07 '15 at 11:28
If you really absolutely have to do this, then use Laravel's [DBRaw](http://fideloper.com/laravel-raw-queries) with MySQL's [FIND_IN_SET()](http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_find-in-set) function — Mark Baker, Mar 07 '15 at 11:29
Ah, I should do it.. Can't do it in eloquent ? If so can you tell me how to do it in find_in_set — AngularAngularAngular, Mar 07 '15 at 11:30
See [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/q/3653462) — eggyal, Mar 07 '15 at 11:30
Probably a bad idea, but you could use LIKE if they're all under 10. — Edd Turtle, Mar 07 '15 at 11:31
It's not an issue with Eloquent.... Eloquent simply assumes that your database is sensibly designed, it doesn't provide any special tools that can automagically make a badly designed database look like a well designed database — Mark Baker, Mar 07 '15 at 11:31
@MarkBaker Yes i agree. Can you help me in laravel's dbraw with mysql's findinset ? — AngularAngularAngular, Mar 07 '15 at 11:37
Why can't you normalize the data properly? That's the correct solution to your problem.... using FIND_IN_SET() is really just a hacky workround for an unnormalized database — Mark Baker, Mar 07 '15 at 11:39

Mark Baker · Accepted Answer · 2015-03-07T11:55:54.010

As a pure Eloquent workround, you might be able to do something like:

$id = 5
$Match = MyModel::with(array('No' => function($query) use($id) {
   $query->where_id($id);
}))->get();

Using Raw and FIND_IN_SET, something like:

$Match = MyModel::whereRaw(
    'find_in_set(?, `No`)',
    [5]
)->get();

(Untested)

But this will never be an efficient query because it can't use indexes; and there are many other reasons why a comma-separated list is a bad idea (such as lack of referential integrity)

The real solution will always be to normalize your database properly

Find the matching row in laravel

1 Answers1