#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.

3mo

Join the chat

Aug 13, 2023 (4 months ago)

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:!='')",

Aug 14, 2023 (3 months ago)

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

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

08:17

Nikhil

08:17 AM

Harpreet Does the above info work for repro? This is from 0.26 rc12

Harpreet

08:21 AM

I'll update you.

Aug 16, 2023 (3 months ago)

Nikhil

01:19 PM

Harpreet Did you get a chance to look at this?

Aug 17, 2023 (3 months ago)

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

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

03:56 PM

Just check once by using the same curl commands I provided. It could've been an issue that's already fixed.

Typesense

Lightning-fast, open source search engine for everyone | Knowledge Base powered by Struct.AI

Indexed 3015 threads (79% resolved)

Join Our Community

Similar Threads

Resolving Search Errors in RC Branch using Python Typesense

Aadarsh was encountering an error while testing a RC Branch using Python Typesense. With advice and debugging from Kishore Nallan and Harpreet, the issue was resolved by fixing malformed search parameters and ensuring reference fields match in referenced documents.

1mo

Issue with Embedding Error in Version 0.25.0.rc63

Bill reported a bug in version 0.25.0.rc63 regarding a problem with updating or emplacing a document and receiving an embedding error. This was resolved in version 0.25.0.rc65, but further discussion ensued regarding the function of 'index' in the update feature.

4mo

Resolving Issues with Reference Field in v0.26.0.rc11

Shaun reported problems applying the reference field in `v0.26.0.rc11`. The issue made the tested field disappear from UI, and an attempt to fix it resulted in an error. Harpreet suggested re-indexing data and promised to consider improving the feature in future updates. Harpreet is currently working on a related feature - sorting by reference field.

3mo

Fixing Multiple Document Retrieval in Typesense

Phil needed an efficient way to retrieve multiple documents by id. Kishore Nallan proposed a solution available in a pre-release build. After some bug fixing regarding id matching by Jason and Kishore Nallan, Phil successfully tested the solution.

26mo

Troubleshooting Typesense API Sorting Error in Item Collections

Nikhil encountered a sorting error when using Typesense API due to an optional reference field in the items collection schema. Harpreet and Jason helped debug and identified a bug with sorting in general for 2 fields with the first one being the join field, promising a fix in a forthcoming build.