WhereIn in Query doesn't work for array, Any solution?

I have defined scopeFilter in Model which has following code

public function scopeFilter($query, array $filters)
    {
        $query->when($filters['subjects'] ?? null, function ($query, $data) {
            //Subjects ids will be comma seperated string eg : 127,125
            $subject_array = explode(',', htmlspecialchars_decode($data));
            $query->where(function ($query) use ($subject_array) {
                $query->whereIn('subjects_ids', $subject_array);
            });
        })->when($filters['topics'] ?? null, function ($query, $data) {
            //topics ids will be comma seperated string eg : 127,125
            $topic_array = explode(',', htmlspecialchars_decode($data));
            $query->where(function ($query) use ($topic_array) {
                $query->whereIn('topics_ids', $topic_array);
            });
        })->when($filters['search'] ?? null, function ($query, $search) {
            $query->where(function ($query) use ($search) {
                $query->where('name', 'like', '%'.$search.'%');
            });
        });

    }

Here is how I'm using this in controller

$exams = Exams::filter(\Request::only('search', 'subjects','topics'))
            ->orderBy('status', 'DESC')
            ->orderBy('updated_at', 'DESC')->transform(....);

If this is passed in URL query then the result will be empty. Am I doing anything wrong here?

Basically the are three filters, the search works fine but subjects and topics won't work.

Sti3bas

6 years ago

Level 53

@manjumjn try to change:

$query->where(function ($query) use ($subject_array) {
   $query->whereIn('subjects_ids', $subject_array);
 });

to:

$query->whereIn('subjects_ids', $subject_array);

manjumjn

6 years ago

Level 1

It's still the same. here is how the query->tosql() will look like

"select * from `exams` where `subjects_ids` in (?)"

Sti3bas

6 years ago

Level 53

@manjumjn make sure $subject_array contains the array of valid ids.

manjumjn

6 years ago

Level 1

@sti3bas Yes, it does has a valid list of ids. Those id's are from db itself.

mstrauss

6 years ago

Level 14

Hi @manjumjn

A couple of things I noticed. First, I don't see any return statements. Second, shouldn't the fallback value of your null coalescing operator be false as opposed to null? Or, just use the PHPissetfunction as, per the docs it will:

isset — Determine if a variable is declared and is different than NULL

I actually haven't seen null coalescing operator used in conjunction with the when clause before. Traditionally, you see a more direct comparison like below:

$query->when($filters['subjects'] === true, function ($query) {
    return $query->...
});

Below is my suggestion:

public function scopeFilter($query, array $filters)
    {
        $query->when(isset($filters['subjects']), function ($query, $data) {
            //Subjects ids will be comma seperated string eg : 127,125
            $subject_array = explode(',', htmlspecialchars_decode($data));
            return $query->where(function ($query) use ($subject_array) {
                $query->whereIn('subjects_ids', $subject_array);
            });
        })->when(isset($filters['topics']), function ($query, $data) {
            //topics ids will be comma seperated string eg : 127,125
            $topic_array = explode(',', htmlspecialchars_decode($data));
            return $query->where(function ($query) use ($topic_array) {
                $query->whereIn('topics_ids', $topic_array);
            });
        })->when(isset($filters['search']), function ($query, $search) {
            return $query->where(function ($query) use ($search) {
                $query->where('name', 'like', '%'.$search.'%');
            });
        });

    }

Please or to participate in this conversation.