CODEWITHSUNDEEP
phpmysqlarrayslaravel-5
Brian Reeves
Brian Reeves

Reputation: 345

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

Upvotes: 10

Views: 29358

Answers (5)

Rosh
Rosh

Reputation: 491

I am adding a little more info to Zubayer Hossain's answer

The data types have to match:
// [1, 2]
->whereJsonContains('players', 1)   // Works.
->whereJsonContains('players', '1') // Doesn't work.

// ["1", "2"]
->whereJsonContains('players', '1') // Works.
->whereJsonContains('players', 1)   // Doesn't work.

whereJsonContains can be used in cases where we need to check if a value matches a json encoded field in our table.

Courtesy : https://newbedev.com/php-wherejsoncontains-and-with-laravel-example

Upvotes: 5

Nazmul Haque
Nazmul Haque

Reputation: 868

<!-- If you have a collection of value like this: -->

$category_id = 1,2,3,...; $category_id = $_POST['category_id'];

$myArray = explode(',', $category_id); 

<!-- If you already have array data you can pass this to the following query -->
$data = DB::table('tablename')->select('*') ->whereIn('catcode', $myArray)->get();

Upvotes: 0

ZhaoRachi
ZhaoRachi

Reputation: 89

You could use something like this query.

$k = ["359045532","359079612","359079372","359081292","359081052","359086332","359086092","359111892","359111652"];

Modal::whereIn('myitems', $k)->get();

Upvotes: 0

Zubayer Hossain
Zubayer Hossain

Reputation: 608

Laravel includes whereJsonContains(): So your field jobs that you are casting as an array, that can query as :

->whereJsonContains('jobs', 3)

That way worked for me ...

Upvotes: 19

Callam
Callam

Reputation: 11539

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

Upvotes: 15

Related Questions