Laravel, search exact same substring in eloquent query

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.

Answer

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);