Question 15952 in Laravel Question posted in
The official Laravel Documentation can be found here.

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"
Tags:

3

Answers


  1. 0

    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'))
    Login or Signup to reply.
  2. 0

    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()
    Login or Signup to reply.
  3. 0

    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();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search