This message was deleted.
# community-help
s
This message was deleted.
z
Under no disillusion that this will likely have a big performance impact at this depth - also likely not 1:1 of what production will end up looking like, but good to test the limits!
h
Hey @Zac Evans we didn't have a use case for nested joins till now. If you can share your schema and a few documents I'll let you know what's possible.
z
Hi @Harpreet Sangar, interesting, the feature seems to work really well already with the nested schema I've got, and on what I assume is quite an expensive query. But filter_by looks to be the only prop that supports the nesting that I can see.
Here's a snapshot of the schema so far
Copy code
export const PRODUCT_SCHEMA = {
	name: 'products',
	fields: [
		{ name: 'id', type: 'string' },
		{ name: 'title', type: 'string' },
		{ name: 'description', type: 'string' },
		{ name: 'image', type: 'string' },
		{ name: 'vendor', type: 'string', facet: true },
		{ name: 'group', type: 'string', facet: true, optional: true },

		//	Handles
		{ name: 'handle', type: 'string' },
		{ name: 'handle.vendor', type: 'string' },
		{ name: 'handle.category', type: 'string' },

		//	Categories
		{ name: 'categories', type: 'string[]', facet: true },
		{ name: 'categories.lvl0', type: 'string', facet: true, optional: true },
		{ name: 'categories.lvl1', type: 'string', facet: true, optional: true },
		{ name: 'categories.lvl2', type: 'string', facet: true, optional: true },
		{ name: 'categories.lvl3', type: 'string', facet: true, optional: true },
		{ name: 'categories.lvl4', type: 'string', facet: true, optional: true },

		//	Relationships
		{ name: 'skus', type: 'string[]' }, // Double up on variants until query_by available on joins
		{ name: 'riders', type: 'string[]', facet: true },
		{ name: 'ride_types', type: 'string[]', facet: true },
		{ name: 'availability', type: 'string[]', facet: true },

		//	Options
		{ name: 'option_keys', type: 'string[]' },

		//	Pricing
		{ name: 'price.from', type: 'float', facet: true },
		{ name: 'price.to', type: 'float' },
	],
	token_separators: [ '-' ],
};


export const PRODUCT_VARIANT_SCHEMA = {
	name: 'product_variants',
	enable_nested_fields: true,
	fields: [
		{ name: 'id', type: 'string' },
		{ name: 'title', type: 'string' },
		{ name: 'image', type: 'string' },
		{ name: 'skus', type: 'string[]' },

		//	Relationships
		{ name: 'product_id', type: 'string', reference: 'products.id', facet: true },
		{ name: 'vehicle_id', type: 'string[]', reference: 'vehicles.id', facet: true },

		//	Options
		{ name: 'options.*', type: 'string', facet: true },
		{ name: 'price', type: 'float', facet: true },
	],
};

export const VEHICLE_SCHEMA = {
	name: 'vehicles',
	fields: [
		{ name: 'id', type: 'string' },
		{ name: 'title', type: 'string' },
		{ name: 'handle', type: 'string' },
		{ name: 'handle.vendor', type: 'string' },
		{ name: 'vendor', type: 'string', facet: true },
		{ name: 'model', type: 'string', facet: true },
		{ name: 'year', type: 'int32', facet: true, sort: true },
		{ name: 'image', type: 'string', optional: true },
		{ name: 'popularity', type: 'int32' },
	],
	default_sorting_field: 'year',
};

export const RETAILER_SCHEMA = {
	name: 'retailers',
	fields: [
		{ name: 'id', type: 'string' },
		{ name: 'title', type: 'string' },
		{ name: 'handle', type: 'string' },
		{ name: 'image', type: 'string', index: false, optional: true },
		{ name: 'address', type: 'string', index: false, optional: true },
		{ name: 'location', type: 'geopoint' },
	],
};

export const INVENTORY_SCHEMA = {
	name: 'inventory',
	fields: [
		{ name: 'id', type: 'string' },
		{ name: 'retailer_id', type: 'string', reference: 'retailers.id', facet: true },
		{ name: 'product_variant_id', type: 'string', reference: 'product_variants.id', facet: true },
		{ name: 'qty', type: 'int32' },
		{ name: 'sku_src', type: 'string' },
	],
	default_sorting_field: 'qty',
};
And a document from each
Copy code
//	PRODUCT
{
	"id": "f68b253d-0b82-5c82-86dd-9a6754236c9c",
	"title": "Piston Kit",
	"handle": "skf-fork-seal-kit",
	"handle.category": "fork-seals",
	"handle.vendor": "skf",
	"image": "https://...",
	"description": "...",
	"categories": [
		"Parts & Accessories",
		"Bike Parts",
		"Suspension",
		"Fork & Components",
		"Fork Seals"
	],
	"categories.lvl0": "Parts & Accessories",
	"categories.lvl1": "Parts & Accessories > Bike Parts",
	"categories.lvl2": "Parts & Accessories > Bike Parts > Suspension",
	"categories.lvl3": "Parts & Accessories > Bike Parts > Suspension > Fork & Components",
	"categories.lvl4": "Parts & Accessories > Bike Parts > Suspension > Fork & Components > Fork Seals",
	"option_keys": [
		"Size",
		"Fitment"
	],
	"price.from": 46.95,
	"price.to": 181.95,
	"ride_types": [
		"Off-Road",
		"MX"
	],
	"riders": [],
	"vendor": "Namura Technologies",
	"availability": [
		"Delivery"
	]
}

//	PRODUCT_VARIANT
{
	"id": "c141f616-c4d1-5821-8178-db3574f7085a",
	"title": "WP 48mm / 5627816",
	"image": "https://...",
	"options": {
		"Fitment": "5627816",
		"Option": "WP 48mm"
	},
	"price": 181.95,
	"skus": [ "KITG48W", "1150485" ],
	"product_id": "f68b253d-0b82-5c82-86dd-9a6754236c9c",
	"vehicle_id": [ "23873", "25958", "22895" ]
}

//	INVENTORY
{
	"id": "78a5742d-f98c-592f-a2d0-0e74ccd08f7c",
	"product_variant_id": "c141f616-c4d1-5821-8178-db3574f7085a",
	"qty": 2,
	"retailer_id": "abdd3ccb-71e6-57cd-96fe-75a9c687a7f9",
	"sku_src": "115-0485"
}

//	RETAILER
{
	"address": "...",
	"handle": "beverly-hills-ducati",
	"id": "abdd3ccb-71e6-57cd-96fe-75a9c687a7f9",
	"location": [ 34.0788905, -118.3763705 ],
	"title": "Beverly Hills Ducati"
}
In a nutshell, the query is starting at the
products
collection, but filtering by what
product_variants
are in
inventory
at
retailers
within a location.
@Harpreet Sangar
h
Hey @Zac Evans I'm working on support for nested joins.
z
@Harpreet Sangar I could be seeing the results incorrectly, but it looks like the nested joins are already working? That’s a testament to the design if it’s unintentional! 💪 😄 but worth checking nonetheless.
h
Yeah, you're the first user to find the nested join "easter egg". It is working properly with
filter_by
because of how filtering is implemented. I'm working on adding support for the rest of the search parameters like
include_fields
.
💪 1
@Zac Evans You can try this in the next RC build.
Only
filter_by
,
include_fields
, and
exclude_fields
have support for nested join at the moment.
Copy code
{ ... "include_fields": "$product_variants(id,$inventory(qty,sku,$retailer(id,title)))" }
This will work now.
💪 1
z
Amazing! Thanks mate