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

Question

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 . '"%')?

Answer 1

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);
});