CODEWITHSUNDEEP
phpjsonlaravelpostgresql
A4fam
A4fam

Reputation: 97

How to query whereIn inside json column in laravel 5.3?

I have been working with a laravel 5.3 version and in a table i have a json column where i have saved average rating of a category table. so, A product category table has a column "detail" as a json data type which saves avgRating. I want to run a query inside that json column. And i want to filter that category with a average rating. So, from the frontend side rating comes in a parameter with a comma seperated so that category can be filtered with multiple ratings.

$productCategory = ProductCategory::query();
$ratings = explode(',',$params['ratings']);
$productCategory = $productCategory->whereIn('detail->avgRating',$ratings)->get();

I want to achieve something like this. I am using Postgres

Upvotes: 1

Views: 1615

Answers (2)

Faesal
Faesal

Reputation: 823

You should use whereJsonContains or whereRaw:

$productCategory->whereJsonContains('detail->>avgRating',$ratings)->get();

OR

$productCategory->whereRaw('JSON_CONTAINS(detail->>avgRating, ?)', $ratings)->get();

Upvotes: 1

Lajos Arpad
Lajos Arpad

Reputation: 76436

It turns out that there was too much uncertainty at the time when the question was asked. Once the asker separated the problems and figured out that the raw query of

DB::select(DB::raw("SELECT * FROM product_categories where detail->>'avgRating' in ('2.0','4.0')"));

works in Postgres, the asker from that point onwards had a much easier time figuring out the actual solution in the where clause. I presuppose that it was

detail->>avgRating

instead of

detail->avgRating

but from the comment section that was not yet confirmed. But the moral of the story is that whenever one has an eloquent problem that might be related to the RDBMS, then it makes a lot of sense to first sort out the raw query and then, having solid knowledge about what should be generated, at that point one can switch to the Eloquent code and apply the solution there.

Upvotes: 1

Related Questions