#community-help

Issue with Join Operation in `CustomerProductPrices` Collection

TLDR Nikhil had concerns about adding product prices before the corresponding product and reported a crash when using a filter clause. Harpreet provided a workaround for fetching related products and also suggested a reason for the need of product presence enforcement. The crash could not be reproduced at Harpreet's end.

Powered by Struct AI

1

Aug 13, 2023 (1 month ago)
Nikhil
Photo of md5-07cb09a4ed26d997ee7f71d5f2a017e0
Nikhil
12:28 PM
With reference to joins discussed in https://gist.github.com/kishorenc/037314619c3104970ae703b25aff2f51, if I fetch from CustomerProductPrices collection, would it fetch the product automatically or would that require a filter clause as well? Currently the examples seem to be mainly about fetching from Products collection which doesn't have the reference field.
05:10
Nikhil
05:10 PM
One more concern is if we can allow a product price to be added before the corresponding product (allow reference to not be enforced during data entry). I got an error when I tried to add the price before the product.
06:46
Nikhil
06:46 PM
Got a crash when I used a filter clause:-
"filter_by":"$ItemCommon(itemNumber:!='')",
Image 1 for Got a crash when I used a filter clause:-
```"filter_by":"$ItemCommon(itemNumber:!='')",```
Aug 14, 2023 (1 month ago)
Harpreet
Photo of md5-745d880d794220d9f0fb9ade17c6b861
Harpreet
05:09 AM
We'll be adding support for fetching the related product from CustomerProductPrices soon. It won't require any filter clause but you'll have to specify include_fields: $Products(*) to get all the fields of related product or specific field names.
05:34
Harpreet
05:34 AM
> One more concern is if we can allow a product price to be added before the corresponding product (allow reference to not be enforced during data entry). I got an error when I tried to add the price before the product.
>
When a document containing a reference field is created, we lookup the referenced collection and store a reference in foo_sequence_id field. That's the reason for enforcing the presence of product.
05:36
Harpreet
05:36 AM
Can you post a minimal example that reproduces the crash?
Nikhil
Photo of md5-07cb09a4ed26d997ee7f71d5f2a017e0
Nikhil
06:24 AM
// Collection 1 - item_master
{
    "name": "item_master",
    "enable_nested_fields": true,
    "fields": [
        {
            "name": "id",
            "type": "string",
            "index": true,
            "optional": false,
            "sort": true,
            "infix": true,
            "facet": true
        },
        {
            "name": "attrId",
            "type": "string",
            "index": true,
            "optional": true,
            "infix": true,
            "facet": true
        },
        {
            "name": "itemNumber",
            "type": "string",
            "index": true,
            "optional": true,
            "sort": true,
            "infix": false,
            "reference": "items.itemNumber"
        },
        {
            "name": "name",
            "type": "string",
            "index": true,
            "optional": false
        }
    ]
}

// 2nd collection "items"
{
    "name": "items",
    "enable_nested_fields": true,
    "fields": [
        {
            "name": "itemNumber",
            "type": "string",
            "index": true,
            "optional": true,
            "sort": true,
            "infix": false
        },
        {"name": "notes", "type": "string"},
        { "name": "guideType", "type": "int32", "index": true, "optional": false, "sort": false , "infix": false}
    ]
}
06:24
Nikhil
06:24 AM
Create these 2 collections
06:26
Nikhil
06:26 AM
Add data to items
{
    "itemNumber": "123456",
    "notes": "TEst Item 1",
    "guideType": 1
}

And then to item_master
{
    "_id": "abc",
    "itemNumber": "123456",
    "name": "Test Item 1"
}

Harpreet On a side note, please also note this is an example of why I need item_master to have an item even though items collection doesn't have it (given how current join works which can change)
06:26
Nikhil
06:26 AM
Run this query and you will then see the crash
Image 1 for Run this query and you will then see the crash
08:17
Nikhil
08:17 AM
Harpreet Does the above info work for repro? This is from 0.26 rc12
Harpreet
Photo of md5-745d880d794220d9f0fb9ade17c6b861
Harpreet
08:21 AM
I'll update you.

1

Aug 16, 2023 (1 month ago)
Nikhil
Photo of md5-07cb09a4ed26d997ee7f71d5f2a017e0
Nikhil
01:19 PM
Harpreet Did you get a chance to look at this?
Aug 17, 2023 (1 month ago)
Harpreet
Photo of md5-745d880d794220d9f0fb9ade17c6b861
Harpreet
05:37 AM
Hi Nikhil I used the following commands and could not reproduce the crash
curl -k "" -X POST -H "Content-Type: application/json" -H "X-TYPESENSE-API-KEY: ${TYPESENSE_API_KEY}" -d '{
    "name": "item_master",                                                                                                    
    "enable_nested_fields": true,
    "fields": [
        {
            "name": "id",        
            "type": "string",
            "index": true,
            "optional": false,
            "sort": true,
            "infix": true,
            "facet": true
        },                                  
        {                                                                                                        
            "name": "attrId",
            "type": "string",
            "index": true,
            "optional": true,
            "infix": true,
            "facet": true
        },
        {
            "name": "itemNumber",
            "type": "string",
            "index": true,
            "optional": true,
            "sort": true,
            "infix": false,
            "reference": "items.itemNumber"
        },
        {
            "name": "name",
            "type": "string",
            "index": true,
            "optional": false
        }
    ]
}'

curl -k "" -X POST -H "Content-Type: application/json" -H "X-TYPESENSE-API-KEY: ${TYPESENSE_API_KEY}" -d '{
    "name": "items",                                                                                                    
    "enable_nested_fields": true,
    "fields": [
        {
            "name": "itemNumber",
            "type": "string",
            "index": true,
            "optional": true,
            "sort": true,
            "infix": false
        },               
        {"name": "notes", "type": "string"},
        { "name": "guideType", "type": "int32", "index": true, "optional": false, "sort": false , "infix": false}
    ]                        
}'

curl ""
-H "X-TYPESENSE-API-KEY: ${TYPESENSE_API_KEY}"
-H "Content-Type: text/plain"
-X POST
-d '{"itemNumber": "123456", "notes": "TEst Item 1", "guideType": 1}'

curl ""
-H "X-TYPESENSE-API-KEY: ${TYPESENSE_API_KEY}"
-H "Content-Type: text/plain"
-X POST
-d '{"_id": "abc", "itemNumber": "123456", "name": "Test Item 1"}'

curl -H "X-TYPESENSE-API-KEY: ${TYPESENSE_API_KEY}" "\
&filter_by=$item_master(itemNumber:!='')"
05:38
Harpreet
05:38 AM
This is the response from the API:
{
  "facet_counts": [],
  "found": 1,
  "hits": [
    {
      "document": {
        "guideType": 1,
        "id": "0",
        "itemNumber": "123456",
        "notes": "TEst Item 1"
      },
      "highlight": {},
      "highlights": []
    }
  ],
  "out_of": 1,
  "page": 1,
  "request_params": {
    "collection_name": "items",
    "per_page": 10,
    "q": "*"
  },
  "search_cutoff": false,
  "search_time_ms": 0
}
Nikhil
Photo of md5-07cb09a4ed26d997ee7f71d5f2a017e0
Nikhil
03:06 PM
That's surprising since it crashed for me twice. I will try again over the weekend and see if I can find a better repro
Harpreet
Photo of md5-745d880d794220d9f0fb9ade17c6b861
Harpreet
03:56 PM
Just check once by using the same curl commands I provided. It could've been an issue that's already fixed.