I have an Eloquent Book model with a readings (JSON) field. It is an array of 12 positions, each corresponding to the number of readings of each book in each month of the current year, e.g.:

[1, 0, 3, 2, 5, 5, 2, 1, 3, 0, 0, 2]

In one of my controllers, I want to select all the books with one or more readings, by month. So I have a for loop which iterates over the months. The following is my best try, but the problem is that no books are selected with that query in any iteration, although the book from the example should be returned in iterations 2, 10 and 11:

for ($i = 1; $i <= 12; $i  ) {
    $books = Book::whereJsonDoesntContain('readings->'. $i - 1, 0)->get();
    ...
};

I have also tried with Book::whereRaw('not json_contains(readings->'.$i-1.', 0)'); inside the for loop, with the same luck.

Any ideas? Thanks in advance.

CodePudding user response：

Your query should be:

$books = Book::whereJsonDoesntContain('readings', 0)->get();

Remove the for or any loop... readings is the field, in this case 1, 0, 3, 2, 5, 5, 2, 1, 3, 0, 0, 2...

CodePudding user response：

It seems like whereJsonDoesntContain should work – it uses JSON_CONTAINS internally – but I wasn't able to get it working in testing. (I'm not super familiar with JSON columns though; I prefer normalizing into separate tables.)

Using Laravel's where() with a numeric key doesn't seem to work; it ignores readings[3] and converts readings->3 into $."3".

So what we're left with is a raw query using JSON_EXTRACT:

for ($i = 0, $i <= 11; $i  ) {
    $books = Book::whereRaw("JSON_EXTRACT(`readings`, '\$[$i]') != 0");
}

Test fiddle here