The difference between find_in_set() sql and whereIn laravel

Question

I have an eloquent query in Laravel that looks something like this:

return $query->with('status', 'bank')
             ->whereHas('status', function (Builder $builder) {
    return $builder->whereIn('bank_status', ['MANUAL', 'VALIDATED,ACTIVATED']);
});

I am looking for any possible values that will match the given: ['MANUAL', 'VALIDATED,ACTIVATED'] I do have comma seperated values .

I am not sure if its the right choice to use whereIn or should I go with rawsql find_in_set() which is the best to use in this case ?

If anyone has an answer would be nice to convert my query builder using find_in_set() thanks

score 0 · Answer 1 · answered Oct 25 '17 at 08:16

0

try this type of query in laravel

$search =  VALIDATED;

->whereRaw("find_in_set('".$search."',MANUAL)")

answered Oct 25 '17 at 08:16

Bilal Ahmed

4,005
3
22
42

Basically I want to get all the data from db where status is MANUAL OR VALIDATED,ACTIVATED or smth else. – Oct 25 '17 at 08:18

score 0 · Accepted Answer · answered Oct 25 '17 at 08:20

0

If you have comma separated values in your column you should use find_in_set() rather than in. If your column row has following value 'VALIDATED,ACTIVATED' and you will search for ACTIVATED using IN it will fail and return no rows.

return $query
    ->with('status', 'bank')
    ->whereHas('status', function (Builder $builder) use ($needle){
        return $builder->whereRaw('FIND_IN_SET(?, bank_status)', [$needle]);
});

More about your question - FIND_IN_SET() vs IN()

answered Oct 25 '17 at 08:20

lchachurski

1,770
16
21

Yeah sounds just about right so $needle in this case are my values that I will look for like ['ACTIVATED,VALIDATED' , 'SEND] – Oct 25 '17 at 08:31
1

Exactly, but if you want to pass an array, then you might want to change `[$needle]` to `$needle` and also change variable name to `$params` for better naming. – lchachurski Oct 25 '17 at 08:33

The difference between find_in_set() sql and whereIn laravel

2 Answers2