Query with JSON Path Fails Due to Type Mismatch in JSON IDs Values [Int , String]

Question

Query with JSON Path Fails Due to Type Mismatch in JSON IDs Values [Int , String]

Closed this issue 4 months ago · 6 comments

fh32000 commented 4 months ago

I’m experiencing an issue when querying JSON columns using the eloquent-json-relations package. Specifically, the hasManyJson method does not handle the JSON values' types correctly, leading to SQL queries that do not match expected results.

Steps to Reproduce:

I have a products table with a category_ids JSON column. The category_ids column stores an array of objects, where each object contains an id field as a string.

Example data in category_ids:
```
[
  {"id": "2", "position": 1},
  {"id": "4", "position": 2}
]
```

I have a Category model with a JsonProducts relationship defined as:

public function JsonProducts()
{
    return $this->hasManyJson(Product::class, 'category_ids[]->id');
}

Running the following query:

$home_categories = Category::limit(2)
    ->select('id', 'name')
    ->with('JsonProducts:name,id,category_ids')
    ->get();

Results in the following SQL query:

SELECT
  `name`,
  `id`,
  `category_ids`
FROM
  `products`
WHERE
  (
    2 MEMBER OF (
      JSON_EXTRACT(`products`.`category_ids`, '$[*]."id"')
    )
    OR 4 MEMBER OF (
      JSON_EXTRACT(`products`.`category_ids`, '$[*]."id"')
    )
  )

The Issue:

The query fails because the 2 and 4 values are not matched correctly. When I modify the SQL manually to:

SELECT
  `name`,
  `id`,
  `category_ids`
FROM
  `products`
WHERE
  (
    "2" MEMBER OF (
      JSON_EXTRACT(`products`.`category_ids`, '$[*]."id"')
    )
    OR "4" MEMBER OF (
      JSON_EXTRACT(`products`.`category_ids`, '$[*]."id"')
    )
  )

It works as expected. The issue seems to be that the values are not quoted correctly in the generated SQL query.

Expected Behavior:

The eloquent-json-relations package should quote JSON values correctly in the generated SQL query, ensuring that the member of operator works as expected.

Environment:

Laravel version: v10.48.18
eloquent-json-relations package version: v1.10.2
Database: 8.0.31

Answer 1 · 2024-08-05T21:48:11.000Z

Hi @fh32000,
What's the type of the id column in the categories table? Is it an integer?

Answer 2 · 2024-08-05T21:55:08.000Z

@staudenmeir yes its an integer

Answer 3 · 2024-08-05T22:27:42.000Z

The queries can only work when the data types match.

I have a products table with a category_ids JSON column. The category_ids column stores an array of objects, where each object contains an id field as a string.

You need to convert these strings to integers before storing them in the database. The package can't support you here, unfortunately.

Answer 4 · 2024-08-05T23:28:45.000Z

Hi,

Thank you for your prompt response and guidance. I have followed your suggestion and ensured that the IDs are stored as integers in the database. However, I am encountering a new issue after making these changes.

Updated Issue Details:

Context:
After converting the id fields to integers and updating the database, I ran the following code:

$category = Category::with('JsonProducts')->first();

    public function JsonProducts()
    {
        return $this->hasManyJson(Product::class, 'category_ids[]->id','id');
        
    }

class Product extends Model
{
    protected $casts = [

        'category_ids' => 'json',
        ];
}

New Error:

I encountered the following error:

foreach() argument must be of type array|object, null given in vendor/staudenmeir/eloquent-json-relations/src/Relations/HasManyJson.php on line 204

SQL Query Generated:

The generated SQL query is:

SELECT *
FROM `products`
WHERE (
    2 MEMBER OF (
        JSON_EXTRACT(`products`.`category_ids`, '$[*]."id"')
    )
)

Observations:

Database Behavior:
The query works correctly when run directly on the database server and returns the expected results.
Error Context:
Despite the correct SQL execution, the application throws an error due to a null value being passed to the foreach loop in the HasManyJson class.

Additional Information:

debug

    /**
     * Build model dictionary keyed by the relation's foreign key.
     *
     * @param \Illuminate\Database\Eloquent\Collection $results
     * @return array
     */
    protected function buildDictionary(Collection $results)
    {
        Log::debug('buildDictionary');
        Log::debug( $results);
        $foreign = $this->getForeignKeyName();
        Log::debug( "foreign : $foreign");

        $dictionary = [];

        foreach ($results as $result) {
            Log::debug( $result->{$foreign} );

            foreach ($result->{$foreign} as $value) {
                $dictionary[$value][] = $result;
            }
        }

        return $dictionary;
    }

logs

[2024-08-06 01:16:04] local.DEBUG: foreign : category_ids->id  
[2024-08-06 01:18:33] local.DEBUG: buildDictionary  
[2024-08-06 01:18:33] local.DEBUG: [{"id":2012,"added_by":"admin","user_id":1,"name":"Tosca Semoule - Semolina Medium 1Kg*10pcs","slug":"tosca-semoule-semolina-medium-1kg10pcs-BXiGzQ","product_type":"physical","category_ids":[{"id":2,"position":1}],"category_id":2,"sub_category_id":null,"sub_sub_category_id":null,"brand_id":5,"unit":"kg","min_qty":1,"refundable":1,"digital_product_type":null,"digital_file_ready":"","images":"[\"2024-03-29-6606f580abf9a.webp\"]","color_image":"[]","thumbnail":"2024-03-29-6606f580e1619.png","featured":1,"flash_deal":null,"video_provider":"youtube","video_url":null,"colors":"[]","variant_product":0,"attributes":"null","choice_options":"[]","variation":"[]","published":0,"unit_price":5,"purchase_price":0,"tax":0,"tax_type":"percent","tax_model":"include","discount":0,"discount_type":"flat","current_stock":50,"minimum_order_qty":1,"details":"<p element-id=\"935\">Tosca semolina is one of the finest types of wheat... Ideal choices for a healthy life... Semolina is used in the manufacture of many sweets and is also used in pastries and baked goods.<br element-id=\"934\"><\/p>","free_shipping":0,"attachment":null,"created_at":"2024-03-29T16:08:16.000000Z","updated_at":"2024-08-05T22:44:16.000000Z","status":0,"featured_status":1,"meta_title":null,"meta_description":null,"meta_image":"def.png","request_status":1,"denied_note":null,"shipping_cost":0,"multiply_qty":0,"temp_shipping_cost":null,"is_shipping_cost_updated":null,"code":"185849","weight":200,"article_no":null,"supplier_no":null,"translations":[],"reviews":[]},{"id":2635,"added_by":"admin","user_id":2,"name":"Hamol Baby powder sensitive150g*12 st","slug":"hamol-baby-powder-sensitive150g12-st-h2Gklq","product_type":"physical","category_ids":[{"id":2,"position":1}],"category_id":2,"sub_category_id":null,"sub_sub_category_id":null,"brand_id":16,"unit":"kg","min_qty":1,"refundable":1,"digital_product_type":null,"digital_file_ready":"","images":"[\"2024-05-20-664b9d2819a5f.webp\"]","color_image":"[]","thumbnail":"2024-05-20-664b9d28247b9.png","featured":null,"flash_deal":null,"video_provider":"youtube","video_url":null,"colors":"[]","variant_product":0,"attributes":"null","choice_options":"[]","variation":"[]","published":0,"unit_price":22,"purchase_price":0,"tax":21,"tax_type":"percent","tax_model":"include","discount":0,"discount_type":"flat","current_stock":10,"minimum_order_qty":1,"details":null,"free_shipping":0,"attachment":null,"created_at":"2024-05-20T16:57:44.000000Z","updated_at":"2024-08-05T22:44:31.000000Z","status":1,"featured_status":1,"meta_title":null,"meta_description":null,"meta_image":"def.png","request_status":1,"denied_note":null,"shipping_cost":0,"multiply_qty":0,"temp_shipping_cost":null,"is_shipping_cost_updated":null,"code":"00003358","weight":200,"article_no":null,"supplier_no":null,"translations":[],"reviews":[]},{"id":2636,"added_by":"admin","user_id":2,"name":"Hamol Baby powder green 150g*12 st","slug":"hamol-baby-powder-green-150g12-st-as8ePt","product_type":"physical","category_ids":[{"id":2,"position":1}],"category_id":2,"sub_category_id":null,"sub_sub_category_id":null,"brand_id":16,"unit":"kg","min_qty":1,"refundable":1,"digital_product_type":null,"digital_file_ready":"","images":"[\"2024-05-20-664b9dc6abf1c.webp\"]","color_image":"[]","thumbnail":"2024-05-20-664b9dc6b3642.png","featured":null,"flash_deal":null,"video_provider":"youtube","video_url":null,"colors":"[]","variant_product":0,"attributes":"null","choice_options":"[]","variation":"[]","published":0,"unit_price":22,"purchase_price":0,"tax":21,"tax_type":"percent","tax_model":"include","discount":0,"discount_type":"flat","current_stock":10,"minimum_order_qty":1,"details":null,"free_shipping":0,"attachment":null,"created_at":"2024-05-20T17:00:22.000000Z","updated_at":"2024-08-05T22:44:31.000000Z","status":1,"featured_status":1,"meta_title":null,"meta_description":null,"meta_image":"def.png","request_status":1,"denied_note":null,"shipping_cost":0,"multiply_qty":0,"temp_shipping_cost":null,"is_shipping_cost_updated":null,"code":"00003357","weight":200,"article_no":null,"supplier_no":null,"translations":[],"reviews":[]},{"id":2637,"added_by":"admin","user_id":2,"name":"Hamol Baby powder pink 150g*12 st","slug":"hamol-baby-powder-pink-150g12-st-IF8yBF","product_type":"physical","category_ids":[{"id":2,"position":1}],"category_id":2,"sub_category_id":null,"sub_sub_category_id":null,"brand_id":16,"unit":"kg","min_qty":1,"refundable":1,"digital_product_type":null,"digital_file_ready":"","images":"[\"2024-05-20-664b9e6a69dde.webp\"]","color_image":"[]","thumbnail":"2024-05-20-664b9e6aed29b.png","featured":null,"flash_deal":null,"video_provider":"youtube","video_url":null,"colors":"[]","variant_product":0,"attributes":"null","choice_options":"[]","variation":"[]","published":0,"unit_price":22,"purchase_price":0,"tax":21,"tax_type":"percent","tax_model":"include","discount":0,"discount_type":"flat","current_stock":10,"minimum_order_qty":1,"details":null,"free_shipping":0,"attachment":null,"created_at":"2024-05-20T17:03:06.000000Z","updated_at":"2024-08-05T22:44:31.000000Z","status":1,"featured_status":1,"meta_title":null,"meta_description":null,"meta_image":"def.png","request_status":1,"denied_note":null,"shipping_cost":0,"multiply_qty":0,"temp_shipping_cost":null,"is_shipping_cost_updated":null,"code":"00003356","weight":200,"article_no":null,"supplier_no":null,"translations":[],"reviews":[]},{"id":2638,"added_by":"admin","user_id":2,"name":"Hamol Cologne pink 200ml*12st","slug":"hamol-cologne-pink-200ml12st-ALtG3u","product_type":"physical","category_ids":[{"id":2,"position":1}],"category_id":2,"sub_category_id":null,"sub_sub_category_id":null,"brand_id":16,"unit":"kg","min_qty":1,"refundable":1,"digital_product_type":null,"digital_file_ready":"","images":"[\"2024-05-20-664b9f091a7b6.webp\"]","color_image":"[]","thumbnail":"2024-05-20-664b9f0994283.png","featured":null,"flash_deal":null,"video_provider":"youtube","video_url":null,"colors":"[]","variant_product":0,"attributes":"null","choice_options":"[]","variation":"[]","published":0,"unit_price":22,"purchase_price":0,"tax":21,"tax_type":"percent","tax_model":"include","discount":0,"discount_type":"flat","current_stock":10,"minimum_order_qty":1,"details":null,"free_shipping":0,"attachment":null,"created_at":"2024-05-20T17:05:45.000000Z","updated_at":"2024-08-05T22:44:31.000000Z","status":1,"featured_status":1,"meta_title":null,"meta_description":null,"meta_image":"def.png","request_status":1,"denied_note":null,"shipping_cost":0,"multiply_qty":0,"temp_shipping_cost":null,"is_shipping_cost_updated":null,"code":"00003355","weight":200,"article_no":null,"supplier_no":null,"translations":[],"reviews":[]},{"id":2639,"added_by":"admin","user_id":2,"name":"Hamol Baby Shampoo blue 200ml*12st","slug":"hamol-baby-shampoo-blue-200ml12st-pHJ75F","product_type":"physical","category_ids":[{"id":2,"position":1}],"category_id":2,"sub_category_id":null,"sub_sub_category_id":null,"brand_id":16,"unit":"kg","min_qty":1,"refundable":1,"digital_product_type":null,"digital_file_ready":"","images":"[\"2024-05-20-664b9f8adc574.webp\"]","color_image":"[]","thumbnail":"2024-05-20-664b9f8b73f0d.png","featured":null,"flash_deal":null,"video_provider":"youtube","video_url":null,"colors":"[]","variant_product":0,"attributes":"null","choice_options":"[]","variation":"[]","published":0,"unit_price":18,"purchase_price":0,"tax":21,"tax_type":"percent","tax_model":"include","discount":0,"discount_type":"flat","current_stock":10,"minimum_order_qty":1,"details":null,"free_shipping":0,"attachment":null,"created_at":"2024-05-20T17:07:55.000000Z","updated_at":"2024-08-05T22:44:31.000000Z","status":1,"featured_status":1,"meta_title":null,"meta_description":null,"meta_image":"def.png","request_status":1,"denied_note":null,"shipping_cost":0,"multiply_qty":0,"temp_shipping_cost":null,"is_shipping_cost_updated":null,"code":"00003354","weight":200,"article_no":null,"supplier_no":null,"translations":[],"reviews":[]},{"id":2640,"added_by":"admin","user_id":2,"name":"Hamol Cologne blue 200ml*12st","slug":"hamol-cologne-blue-200ml12st-6WxJlo","product_type":"physical","category_ids":[{"id":2,"position":1}],"category_id":2,"sub_category_id":null,"sub_sub_category_id":null,"brand_id":16,"unit":"kg","min_qty":1,"refundable":1,"digital_product_type":null,"digital_file_ready":"","images":"[\"2024-05-20-664ba02a4a29e.webp\"]","color_image":"[]","thumbnail":"2024-05-20-664ba02ac1a14.png","featured":null,"flash_deal":null,"video_provider":"youtube","video_url":null,"colors":"[]","variant_product":0,"attributes":"null","choice_options":"[]","variation":"[]","published":0,"unit_price":22,"purchase_price":0,"tax":21,"tax_type":"percent","tax_model":"include","discount":0,"discount_type":"flat","current_stock":10,"minimum_order_qty":1,"details":null,"free_shipping":0,"attachment":null,"created_at":"2024-05-20T17:10:34.000000Z","updated_at":"2024-08-05T22:44:31.000000Z","status":1,"featured_status":1,"meta_title":null,"meta_description":null,"meta_image":"def.png","request_status":1,"denied_note":null,"shipping_cost":0,"multiply_qty":0,"temp_shipping_cost":null,"is_shipping_cost_updated":null,"code":"00003353","weight":200,"article_no":null,"supplier_no":null,"translations":[],"reviews":[]},{"id":2641,"added_by":"admin","user_id":2,"name":"Hamol Cologne yellow 200ml*12st","slug":"hamol-cologne-yellow-200ml12st-9oDnpz","product_type":"physical","category_ids":[{"id":2,"position":1}],"category_id":2,"sub_category_id":null,"sub_sub_category_id":null,"brand_id":16,"unit":"kg","min_qty":1,"refundable":1,"digital_product_type":null,"digital_file_ready":"","images":"[\"2024-05-20-664ba0a86f015.webp\"]","color_image":"[]","thumbnail":"2024-05-20-664ba0a8e8911.png","featured":null,"flash_deal":null,"video_provider":"youtube","video_url":null,"colors":"[]","variant_product":0,"attributes":"null","choice_options":"[]","variation":"[]","published":0,"unit_price":22,"purchase_price":0,"tax":21,"tax_type":"percent","tax_model":"include","discount":0,"discount_type":"flat","current_stock":10,"minimum_order_qty":1,"details":null,"free_shipping":0,"attachment":null,"created_at":"2024-05-20T17:12:40.000000Z","updated_at":"2024-08-05T22:44:31.000000Z","status":1,"featured_status":1,"meta_title":null,"meta_description":null,"meta_image":"def.png","request_status":1,"denied_note":null,"shipping_cost":0,"multiply_qty":0,"temp_shipping_cost":null,"is_shipping_cost_updated":null,"code":"00003352","weight":200,"article_no":null,"supplier_no":null,"translations":[],"reviews":[]},{"id":2642,"added_by":"admin","user_id":2,"name":"Hamol Baby Shampoo roze 200ml*12st","slug":"hamol-baby-shampoo-roze-200ml12st-hiUUvt","product_type":"physical","category_ids":[{"id":2,"position":1}],"category_id":2,"sub_category_id":null,"sub_sub_category_id":null,"brand_id":16,"unit":"kg","min_qty":1,"refundable":1,"digital_product_type":null,"digital_file_ready":"","images":"[\"2024-05-20-664ba118d805e.webp\"]","color_image":"[]","thumbnail":"2024-05-20-664ba1196f77a.png","featured":null,"flash_deal":null,"video_provider":"youtube","video_url":null,"colors":"[]","variant_product":0,"attributes":"null","choice_options":"[]","variation":"[]","published":0,"unit_price":18,"purchase_price":0,"tax":21,"tax_type":"percent","tax_model":"include","discount":0,"discount_type":"flat","current_stock":10,"minimum_order_qty":1,"details":null,"free_shipping":0,"attachment":null,"created_at":"2024-05-20T17:14:33.000000Z","updated_at":"2024-08-05T22:44:31.000000Z","status":1,"featured_status":1,"meta_title":null,"meta_description":null,"meta_image":"def.png","request_status":1,"denied_note":null,"shipping_cost":0,"multiply_qty":0,"temp_shipping_cost":null,"is_shipping_cost_updated":null,"code":"00003351","weight":200,"article_no":null,"supplier_no":null,"translations":[],"reviews":[]},{"id":2644,"added_by":"admin","user_id":2,"name":"Hamol Baby Shampoo roze 400ml*12st","slug":"hamol-baby-shampoo-roze-400ml12st-TvFpFX","product_type":"physical","category_ids":[{"id":2,"position":1}],"category_id":2,"sub_category_id":null,"sub_sub_category_id":null,"brand_id":16,"unit":"kg","min_qty":1,"refundable":1,"digital_product_type":null,"digital_file_ready":"","images":"[\"2024-05-20-664ba1a7b339d.webp\"]","color_image":"[]","thumbnail":"2024-05-20-664ba1a8396e1.png","featured":null,"flash_deal":null,"video_provider":"youtube","video_url":null,"colors":"[]","variant_product":0,"attributes":"null","choice_options":"[]","variation":"[]","published":0,"unit_price":22,"purchase_price":0,"tax":21,"tax_type":"percent","tax_model":"include","discount":0,"discount_type":"flat","current_stock":10,"minimum_order_qty":1,"details":null,"free_shipping":0,"attachment":null,"created_at":"2024-05-20T17:16:56.000000Z","updated_at":"2024-08-05T22:44:31.000000Z","status":1,"featured_status":1,"meta_title":null,"meta_description":null,"meta_image":"def.png","request_status":1,"denied_note":null,"shipping_cost":0,"multiply_qty":0,"temp_shipping_cost":null,"is_shipping_cost_updated":null,"code":"00003350","weight":200,"article_no":null,"supplier_no":null,"translations":[],"reviews":[]}]  
[2024-08-06 01:18:33] local.DEBUG: foreign : category_ids[]->id  
[2024-08-06 01:18:33] local.DEBUG:   
[2024-08-06 01:18:33] local.DEBUG:   
[2024-08-06 01:18:33] local.DEBUG:   
[2024-08-06 01:18:33] local.DEBUG:   
[2024-08-06 01:18:33] local.DEBUG:   
[2024-08-06 01:18:33] local.DEBUG:   
[2024-08-06 01:18:33] local.DEBUG:   
[2024-08-06 01:18:33] local.DEBUG:   
[2024-08-06 01:18:33] local.DEBUG:   
[2024-08-06 01:18:33] local.DEBUG:

Thank you for your support!

Best regards,

@fh32000

Answer 5 · 2024-08-06T09:06:44.000Z

Did you also add the HasJsonRelationships trait to the Product model?

Answer 6 · 2024-08-06T14:12:29.000Z

it work for me after add the HasJsonRelationships trait to the Product model thanks 👏