Level 75
Like isn't usually for numbers. Use your less than greater than, greater than equal to symbols.
Dec 31, 2019
7
Level 6
How would you do where array contains value?
Hi all,
I have a (string) column which holds array data which the model casts. The array holds integers. I'm trying to get eloquent to see if the submitted value is in the stored array. I can get it to work using:
$q->where('weeks_worked', 'LIKE', '%'.$weeksWorked.'%');
However this only works if the array holds values under 10. I.e. a user sends 1 but the array is [1,5,6,12,13]; this breaks.
How would you therefore check if the array contains the value?
Level 122
I would avoid storing multiple values in one field for this very reason
1 like
Level 6
How would you best store the information?
For a bit more information this is part of a scheduling application. There are two tables; 'job_plans' and 'job_plan_sessions'.
The job plan hold a integer which is the job plan cycle length (e.g. 4 means it runs week 1,2,3,4 then back to week 1). This value is definable by the user. The sessions table holds the array of weeks worked, again definable by the user.
My only other thought is having a table of job_plan_session_weeks and in that table have job plan ID, and weeks. Then pull all weeks with that job plan ID, handling it that way.
Level 46
Why can't you just hold the weeks and sessions as integers in the job plan table?
Level 6
Only because there's a variety of other information which goes with it. I'm on my mobile at the minute but can provide a full schema later if you wish.
Level 46
I've never used the JSON column type but it might be your friend here. From my limited understanding it's possible to carry out queries on the data in a JSON column.
I could be wildly wrong about that though.
Level 6
So I've managed to get it working without using an extra table. I've converted the column to json in the migration, it still casts as an array field. To query I have the following (as part of a larger query):
->where(function ($q) {
foreach($this->weeks_worked as $week)
{
$q->whereJsonContains('weeks_worked', $week, 'or');
}
})
Please or to participate in this conversation.