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 . '"%')?
Laravel includes
whereJsonContains():So your fieldjobsthat you are casting as an array, that can query as :
->whereJsonContains('jobs', 3)
That way worked for me ...
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);
});
generate:pivot function, very useful!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
You could use something like this query.
$k = ["359045532","359079612","359079372","359081292","359081052","359086332","359086092","359111892","359111652"];
Modal::whereIn('myitems', $k)->get();
<!-- 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();
$_POST in laravel?? Why do you use php core functions while Laravel already has Request to get Get / Post form values.