Laravel 5 eloquent whereIn

问题:

Hope anybody can help me, I need to search for items that have category id = x in the database

Example table items id,cats,name etc... cats = '1,19' or maybe just '19' or maybe '1,9'

So for this example I need a to search for items that have cats with 9

I tried this but when I search for 9 it also shows 19

$items = Items::where(function($query)use($cat) {
    $query->where('cats', 'like', '%,'.$cat->id.'%');
    $query->orWhere('cats', 'like', '%'.$cat->id.',%');
    $query->orWhere('cats', 'like', '%'.$cat->id.'%');
    })->orderBy('updated_at', 'DSC')->get();

I also tried something

$items = Items::whereIn(explode(',', 'cats'), $cat->id)->get(); 

but it doesn't work

Appreciate any help to find the easiest and shorts way of doing this, regards

回答1:

It's quite hard to understand what you want to achieve but I'll try. First of all as @particus mentioned the best way is to create pivot table when you don't need to worry about such things.

But the solution if you have list of ids in a columns separated by coma is not storing values like

1,2,3

but always adding , at the beginning and at the end, so it should be in this case:

,1,2,3,

This way, if you have in your table ,19,2,3, and you want to search for value 9, you should use look for ,9, string, for example:

$id = 9; 
$items = Items::where('column', LIKE '%,'.$id.',%')->get();

Now for above string no record will be found, but if you have ,9,2,3, or just ,9, the desired record will be found.

回答2:

Assuming you're using MySQL, you can use the FIND_IN_SET function.

$items = Items::whereRaw("FIND_IN_SET(".$cat->id.", cats)")->orderBy('updated_at', 'DESC')->get();

Please note, this will not use any indexes defined on the cats column. Storing array like data in a field is usually a big red flag. You would benefit by normalizing this out now, rather than trying to work around the current design.