Hi, I wanted to ask if there is any plan or expec...
# community-help
g
Hi, I wanted to ask if there is any plan or expected timeline for supporting full-text search (
query_by
) on referenced/joined fields in Typesense (like
vendor.name
when searching products)? Currently, it seems that
query_by
works only on the main collection, and I’m wondering if native support for searching across related collections will be added soon. Thank you!
k
We had to pause working on that to tackle some higher priority items. We don't have an ETA for the feature at the moment since it covers a large surface area (which is one reason it took a long time for us to get to it).
g
Thanks for the clarification! I understand that it’s a large feature and currently lower priority. Could you share any recommended best practices or workarounds for searching across referenced/joined fields in the meantime? For example, is denormalizing certain fields or multi-step querying the suggested approach?
@Kishore Nallan Thanks for the clarification! I understand that it’s a large feature and currently lower priority. Could you share any recommended best practices or workarounds for searching across referenced/joined fields in the meantime? For example, is denormalizing certain fields or multi-step querying the suggested approach?
k
cc @Harpreet Sangar can help with this.
n
@Kishore Nallan @Harpreet Sangar Is there any workaround for this? I am trying to move to join based schema and it seems I will not be able to do it because I need to join guide_items with item_master and then search by description in item_master which I believe is not possible today
h
@Nikhil Agarwal If you only want to search one of those collection, you can query that collection and still join the related documents.
n
Thanks @Harpreet Sangar. I will try to "invert" my joins and then try again. Will start with item_master and join it with guide_items and other related collections
Only 1 functionality I will still lose is the item alias given by customer in a separate customer_items collection since that cannot be part of the item_master collection
h
Can you share your schema?
n
This is our current schema but our most common search is using item master fields and then by customer item fields
Copy code
const itemMasterCollectionSchemaFields: CollectionCreateSchema['fields'] = [
	{ name: 'id', type: 'string', index: true, optional: false, sort: false, infix: false },
	{ name: 'active', type: 'bool', index: true, optional: false, sort: false, infix: false },
	{ name: 'modifiedOn', type: 'string', index: true, optional: true, sort: true, infix: false },
	{ name: 'itemNumber', type: 'string', index: true, optional: false, sort: true, infix: true },
	{ name: 'description', type: 'string', index: true, optional: false, sort: true, infix: true, stem: true },
	{ name: 'commodityId', type: 'string', index: true, optional: false, sort: true, infix: false, facet: true },
	{ name: 'brand', type: 'string', index: true, optional: true, sort: true, infix: true, facet: true },
	{ name: 'caseDimension', type: 'float', index: true, optional: true, sort: true, infix: false },
	{ name: 'caseNetWeight', type: 'float', index: true, optional: false, sort: true, infix: false },
	{ name: 'hasChildNutrition', type: 'bool', index: true, optional: false, sort: false, infix: false, facet: true },
	{ name: 'isFood', type: 'bool', index: true, optional: false, sort: false, infix: false },
	{ name: 'isCatchWeight', type: 'bool', index: true, optional: false, sort: false, infix: false, facet: true },
	{ name: 'itemPack', type: 'int32', index: true, optional: true, sort: true, infix: false },
	{ name: 'gtin', type: 'string', index: true, optional: true, sort: true, infix: false },
	{ name: 'packSize', type: 'string', index: true, optional: true, sort: false, infix: false },
	{ name: 'packSizeDescription', type: 'string', index: true, optional: true, sort: false, infix: false },
	{ name: 'allergensContains', type: 'string[]', index: true, optional: true, sort: false, infix: false, facet: true },
	{ name: 'allergensFreeFrom', type: 'string[]', index: true, optional: true, sort: false, infix: false, facet: true },
	{ name: 'marketingDescription', type: 'string', index: true, optional: true, sort: true, infix: false },
	{ name: 'ingredients', type: 'string', index: true, optional: true, sort: true, infix: false },
	{ name: 'storageType', type: 'string', index: true, optional: true, sort: false, infix: false, facet: true },
];

const itemWarehouseCollectionSchemaFields: CollectionCreateSchema['fields'] = [
	{ name: 'id', type: 'string', index: true, optional: false, sort: false, infix: false },
	{ name: 'itemNumber', type: 'string', index: true, optional: false, sort: true, infix: true, reference: 'item_master.itemNumber', async_reference: true },
	{ name: 'active', type: 'bool', index: true, optional: false, sort: false, infix: false },
	{ name: 'modifiedOn', type: 'string', index: true, optional: true, sort: true, infix: false },
	{ name: 'division', type: 'int32', index: true, optional: true, sort: false, infix: false },
	{ name: 'isBrokenCase', type: 'bool', index: true, optional: false, sort: false, infix: false },
	{ name: 'reservedShipTos', type: 'string[]', index: true, optional: true, sort: false, infix: false },
	{ name: 'vendorNumber', type: 'string', index: true, optional: true, sort: true, infix: true },
	{ name: 'isSuspended', type: 'bool', index: true, optional: false, sort: false, infix: false },
	{ name: 'barcode', type: 'string', index: true, optional: true, sort: false, infix: false },
	{ name: 'itemZone', type: 'string', index: true, optional: true, sort: true, infix: false },
	{ name: 'isDropShip', type: 'bool', index: true, optional: false, sort: false, infix: false },
	{ name: 'isJustInTime', type: 'bool', index: true, optional: false, sort: false, infix: false },
];

const customerItemsCollectionSchemaFields: CollectionCreateSchema['fields'] = [
	{ name: 'id', type: 'string', index: true, optional: false, sort: false, infix: false },
	{ name: 'itemWhId', type: 'string', index: true, optional: false, sort: false, infix: false, reference: 'item_warehouse.id', async_reference: true },
	{ name: 'active', type: 'bool', index: true, optional: false, sort: false, infix: false },
	{ name: 'itemNumber', type: 'string', index: true, optional: false, sort: true, infix: true, reference: 'item_master.itemNumber', async_reference: true },
	{ name: 'shipTo', type: 'string', index: true, optional: true, sort: false, infix: false },
	{ name: 'division', type: 'int32', index: true, optional: true, sort: false, infix: false },
	{ name: 'type', type: 'int32', index: true, optional: true, sort: false, infix: false },
	{ name: 'customerDescription', type: 'string', index: true, optional: true, sort: true, infix: true },
	{ name: 'customerItemNumber', type: 'string', index: true, optional: true, sort: true, infix: true },
	{ name: 'modifiedOn', type: 'string', index: true, optional: true, sort: true, infix: false }
];

const contractItemsCollectionSchemaFields: CollectionCreateSchema['fields'] = [
	{ name: 'id', type: 'string', index: true, optional: false, sort: false, infix: false },
	{ name: 'itemWhId', type: 'string', index: true, optional: false, sort: false, infix: false, reference: 'item_warehouse.id', async_reference: true },
	{ name: 'contractId', type: 'string', index: true, optional: false, sort: false, infix: false },
	{ name: 'itemNumber', type: 'string', index: true, optional: false, sort: true, infix: true, reference: 'item_master.itemNumber', async_reference: true },
	{ name: 'division', type: 'int32', index: true, optional: true, sort: false, infix: false },
	{ name: 'active', type: 'bool', index: true, optional: false, sort: false, infix: false },
	{ name: 'modifiedOn', type: 'string', index: true, optional: true, sort: true, infix: false }
];

const guideItemsCollectionSchemaFields: CollectionCreateSchema['fields'] = [
	{ name: 'id', type: 'string', index: true, optional: false, sort: false, infix: false },
	{ name: 'itemWhId', type: 'string', index: true, optional: false, sort: false, infix: false, reference: 'item_warehouse.id', async_reference: true },
	{ name: 'active', type: 'bool', index: true, optional: false, sort: false, infix: false },
	{ name: 'guideType', type: 'int32', index: true, optional: false, sort: false, infix: false },
	{ name: 'guideId', type: 'string', index: true, optional: false, sort: false, infix: false },
	{ name: 'type', type: 'int32', index: true, optional: false, sort: false, infix: false },
	{ name: 'itemNumber', type: 'string', index: true, optional: true, sort: true, infix: true, reference: 'item_master.itemNumber', async_reference: true },
	{ name: 'customerItemId', type: 'string', index: true, optional: true, sort: true, infix: true, reference: 'customer_items.customerItemNumber', async_reference: true },
	{ name: 'shipTo', type: 'string', index: true, optional: true, sort: false, infix: false },
	{ name: 'division', type: 'int32', index: true, optional: true, sort: false, infix: false },
	{ name: 'guideItemId', type: 'string', index: true, optional: true, sort: false, infix: false },
	{ name: 'modifiedOn', type: 'string', index: true, optional: true, sort: true, infix: false },
	{ name: 'sortSequence', type: 'int32', index: true, optional: true, sort: true, infix: false },
	{ name: 'eachOnly', type: 'bool', index: true, optional: false, sort: false, infix: false },
	{ name: 'lastPurchaseUt', type: 'int64', index: true, optional: true, sort: true, infix: false }
];
@Harpreet Sangar Earlier we only had 1 collection where we would sync all the denormalized data and that takes way too long when some underlying data changes such as item master data. We were very optimistic about joins until we hit this road block of not being able to reference joined column in query_by
I believe facet_by is already being handled in v30 so not too worried about that
@Harpreet Sangar @Kishore Nallan Is there any update? We had to pause our move to new schema because we got stuck with
query_by
Flipping the join is not great for us
k
We have paused work on extending join to support query_by for the moment. I don't have an ETA on when we will resume that. We have to do some engine level refactoring first before we can complete that feature.
👍 1
n
Ok so the pattern here should be:- Use the main collection where we want search to work and do 1-many joins and then filter out the many joins happening here by using filters
guide_items <> item_master (simple 1:1 join but can't query by item_master data) item_master <> guide_items (1-many join, can query by item_master, need to filter guide_items using appropriate shipto) @Kishore Nallan Is this understanding correct?
k
Yes you can't query on the joined collection but can do so on the primary collection.