Laravel, search exact same substring in eloquent query

Question

I have a table like this

Teacher Table

What I am trying to do is to get the row which contains the subjects 1(or any other number like 7,8 etc.)

This is what I have tried in my controller.

public function allTeachers($sub_id) //receiving $sub_id(to be searched)
  {
        $teachers_all=Teacher::where('subjects','like','%'.','.$sub_id.'%')->latest()->paginate(50);
        dd($teachers_all);
    }

The problem here is that, I am getting all the rows which contains subjects as '1',e.g. if it is '3,11,22' or '41,5' it gets selected. But what I am trying to achieve is it should only return where subjects string contains '1' followed by any other number after ',' or '1,2,44,31,23' etc. I am using laravel, I hope I made the question clear.

Storing comma separated values in a db column is almost never a good idea. You should normalize your data, create separate entries for each of the values, which makes getting data a whole lot easier — brombeer, Feb 08 '22 at 09:53
@brombeer i thought it reduces the number of rows in a table, thus making it an effective approach :D — a_man, Feb 08 '22 at 09:56
public function allTeachers($sub_id) //receiving $sub_id(to be searched) { $teachers_all=Teacher::where('subjects','like',$sub_id.','.'%')->latest()->paginate(50); dd($teachers_all); } — spartyboy, Feb 08 '22 at 09:57
But next time use a many to many relationship by creating a new table — spartyboy, Feb 08 '22 at 09:58
It _does_ reduce the number of rows but that doesn't make it an effective approach ;) — brombeer, Feb 08 '22 at 10:01
There is a solution to this but it won't be optimal if there is like 1000rows returned.. would you want that — spartyboy, Feb 08 '22 at 10:04

Dan · Accepted Answer · 2022-02-08T10:14:18.883

The solution to your question would be either to use find_in_set or concat to fill some missing commas and then search for the value:

Teacher::whereRaw('find_in_set("' . $sub_id . '", subjects) <> 0')
    ->latest()
    ->paginate(50);

or

Teacher::whereRaw('concat(",", colors, ",") like "%,' . $sub_id . ',%"')
    ->latest()
    ->paginate(50);

That being said, @bromeer's comments hold true in any case. MySQL isn't around comma-separated values in fields. Both examples shown above aren't an ideal solution. You should look into relationships a bit more.

I suggest using a many-to-many relationship in your case. For that, create a pivot table called teacher_subject and add the relation to your Teacher model:

public function subjects()
{
    return $this->belongsToMany(Subject::class);
}

To find any teachers teaching a specific subject, use whereHas like this:

Teacher::whereHas('subjects', function (Builder $query) use ($sub_id) {
    $query->where('id', $sub_id);
})->latest()->paginate(50);

This is working Perfectly, I got your point about eloquent relationships ,however can you explain a little what <> 0 is doing in the above (first) method. — a_man, Feb 08 '22 at 10:06
I've added an example on how you could leverage MySQL's relations in Laravel - for good measure. — Dan, Feb 08 '22 at 10:16

Laravel, search exact same substring in eloquent query

1 Answers1