Laravel : Query Comma Separated Column using Eloquent and MySQL find_in_set
Introduction
It is not uncommon to store comma separated values in a mysql column in a Laravel Project
colours : "red,blue,green,yellow,black,white"
users : "77,4,5,688,5454,342,32,332"
tags : "mysql,laravel,css,html"
Querying the Column
MySQL Function FIND_IN_SET() can be used to query
$search;
ModelName::whereRaw("FIND_IN_SET($search,colours)");
ModelName::whereRaw("FIND_IN_SET($search,users)");
ModelName::whereRaw("FIND_IN_SET($search,tags)");
Querying the Column - With Protection against SQL Injection
$search;
ModelName::whereRaw("FIND_IN_SET(?,colours)",[$search]);
ModelName::whereRaw("FIND_IN_SET(?,users)",[$search]);
ModelName::whereRaw("FIND_IN_SET(?,tags)",[$search]);
As Scopes in the Model
class ModelName extends Model{
public function scopeContainsTag($query,$tag){
return $query->whereRaw("FIND_IN_SET(?,tags)",[$tag]);
}
}
You can call the scope while querying the model from a controller
public function index(Request $request){
ModelName::containsTag($request->tag_name)->get();
}
Note
There are other ways of storing data in a column other than using comma separated,
Read Laravel Documentation Array & JSON Casting
Top comments (0)