Laravel – How to query if an array field in DB contains a value


I have a field in my model Person called jobs that I’m casting as an array. jobs is an array of ids related to a Jobs table. I want to be able to query Person and return all that have a certain id in their jobs array. I see that Laravel has a whereIn clause which checks if a database value is in an array but I need the opposite – to check whether a database array contains a value.

Am I stuck having to use where('jobs', 'like', '%"' . $job_id . '"%')?


I’m not sure there’s an opposite however if you’re simply looking to make the query a bit more reusable, you could make it a local scope by adding this to your Person model:

 * Scope a query to only include persons with given job id.
 * @return \Illuminate\Database\Eloquent\Builder
public function scopeHasJob($query, $jobId)
    return $query->where('jobs', 'like', "%\"{$jobId}\"%");

The name of the scope hasJob may interfere with the parent QueryBuilder method has so you might have to come up with a different name for it.

Now you can use Person::hasJob($job->id). However rather than storing the job ids in a column as an array, you should consider creating a pivot table to map the relationships between a person and job. You can do this using php artisan:

php artisan generate:pivot persons jobs
php artisan migrate

Then you need to add the relationship into your Person model:

 * The person's jobs
public function jobs()
    return $this->belongsToMany('App\Job');

So you can query your Person model by Job like this:

Person::whereHas('jobs', function ($query) {
    return $query->whereId($job->id);

Answered By – Callam

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published