Using find_in_set() in laravel where clause

Question

I know I can use find_in_set as bellow:

select `id` from `questions` where FIND_IN_SET(8, categories);

But I am using laravel and I would like to write this query there. I have tried this:

$q_category = 8;
$object = DB::table("questions")
  ->select('id')
  ->where(DB::RAW("FIND_IN_SET($q_category, categories)"), '!=', null)
  ->get();

to get the records that have 8 in their categories column (which contains comma seperated category IDs)

But I get records that do not have the category id too.

where am I going wrong?

Answer 1

I see several potential problems with your current code. First, you should be binding the $q_category value to the call to FIND_IN_SET. Second, the check for a successful call to FIND_IN_SET is that the return value is greater than zero, as opposed to non NULL.

$q_category = 8;
$object = DB::table("questions")
    ->select('id')
    ->whereRaw("FIND_IN_SET(?, categories) > 0", [$q_category])
    ->get();