CODEWITHSUNDEEP
phpmysqllaravellaravel-7
Bobby Axe
Bobby Axe

Reputation: 1525

Laravel whereJsonContains returns No Result in A whereExists Sub Query

This is my current code, when executed will return no results. (Query1)

// Get all product categories that has a relation
$product_categories = ProductCategory::whereExists(function($query) {
        $query->select('id')
            ->from('restaurants')
            ->whereJsonContains('restaurants.restaurant_categories', 'product_categories.name');
})->get();

// Log
dd($product_categories->toSql());

Here an sql query dump

select *
from `product_categories`
where exists (
    select `id`
    from `restaurants`
    where json_contains(`restaurants`.`restaurant_categories`, ?)
)
and `product_categories`.`deleted_at` is null

While this when executed will return a result (Query2)

// Get all product categories that has a relation
$product_categories = ProductCategory::whereExists(function($query) {
        $query->select('id')
            ->from('restaurants')
            ->whereJsonContains('restaurants.restaurant_categories', 'Food');
})->get();

// Log
dd($product_categories->toSql());

Here is an sql query dump as well

select *
from `product_categories`
where exists (
    select `id`
    from `restaurants`
    where json_contains(`restaurants`.`restaurant_categories`, ?)
)
and `product_categories`.`deleted_at` is null"

Observation

  1. both sql dumps are the same
  2. difference between both queries is the second parameter of whereJsonContains
  3. in the first query i am passing the table column to the whereJsonContains method
  4. in the second query i am passing a row value directly

Question

  1. How do i get the query to filter using the row value on column name (getting Query1 to work).
  2. What did i miss?

For more context, These are what the tables looks like

table: restaurants

id name restaurant_categories
1 fancy ["Food"]

table: product_categories

id name type
1 Food fragile

This is my updated code, when executed will return no results. (Query3)

// Get all product categories that has a relation
$product_categories = ProductCategory::whereExists(function($query) {
        $query->select('id')
            ->from('restaurants')
            ->whereJsonContains('restaurants.restaurant_categories', \DB::raw('product_categories.name'));
})->get();

// Log
dd($product_categories->toSql());

Here is an sql query dump for Query3

select *
from `product_categories`
where exists (
    select `id`
    from `restaurants`
    where json_contains(`restaurants`.`restaurant_categories`, product_categories.name)
)
and `product_categories`.`deleted_at` is null"

Upvotes: 1

Views: 734

Answers (3)

Chinh Nguyen
Chinh Nguyen

Reputation: 1266

the expected query:

select *
from `product_categories`
where exists (
    select `id`
    from `restaurants`
    where JSON_CONTAINS(restaurants.restaurant_categories, CONCAT('"',`product_categories`.`name`,'"'))
);

laravel implementation:

$product_categories = ProductCategory::whereExists(function($query) {
        $query->select('id')
            ->from('restaurants')
            ->whereJsonContains('restaurants.restaurant_categories', DB::raw('CONCAT(\'"\',`product_categories`.`name`,\'"\')'));
})->get();

Upvotes: 0

Wilson
Wilson

Reputation: 825

You can use JSON_SEARCH instead:

select *
from `product_categories`
where exists (
    select `id`
    from `restaurants`
    where JSON_SEARCH(`restaurants`.`restaurant_categories`,  'all', `product_categories`.`name`) is not null
);

DB Fiddle

Eloquent should be like this:

$product_categories = ProductCategory::whereExists(function($query) {
        $query->select('id')
            ->from('restaurants')
            ->whereRaw("JSON_SEARCH(`restaurants`.`restaurant_categories`, 'all', `product_categories`.`name`) is not null");
})->get()

Upvotes: 1

IGP
IGP

Reputation: 15849

This works as expected. In both cases, whereJsonContains() is trying to match a string literal.

whereJsonContains('restaurants.restaurant_categories', 'product_categories.name')

Would give you a result if your restaurants table looked like this.

id name restaurant_categories
1 fancy ["product_categories.name"]

If you want to try and match with the column instead of a string, try

whereJsonContains('restaurants.restaurant_categories', DB::raw('product_categories.name'))

Upvotes: 0

Related Questions