I have three collections, shops
, shopProducts
, productPhotos
. shops
has many shopProducts
and shopProducts
has many productPhotos
.
shops collection:
- title
- description
shopProducts collection:
- title
- description
- shopRef <-- I use this to identify to what
shops
a particularshopProducts
record belongs to.
productPhotos collection:
- src
- productRef <— I use this to identify to what
shopProducts
a particularproductPhotos
record belongs to.
I have the following index that allows me to query all the products related to a shop:
CreateIndex({
name: 'shopProductsByShop',
source: Collection('shopProducts'),
terms: [
{ field: ['data', 'shopRef'] }
]
});
I query the products collection like so,
Map(
Paginate(
Match(
Index('shopProductsByShop'),
Ref(Collection('shops'), '270198156233277958')
)
),
Lambda(
'ref',
Get(Var('ref'))
)
)
The problem now that I am facing is, in the same query above, I want to “eagerload” the productPhotos
as well, that is, I want to query the shopProducts
and join
it with the productPhotos
, in MySQL terms, it would look something like (might be incorrect but I just to try to make what I’m trying to say clearer…):
select * from shopProducts as product where shopId = ?
inner join productPhotos as photos
on photos.productId = product.id
The end result that I want should look like this:
[
{
ref: 'Ref(Collection("shopProducts"), "12412435134")',
title: 'test',
description: 'test',
photos: [
{
ref: 'Ref(Collection("productPhotos"), "13541135")',
src: 'https://somewhere.com/photos/asdasf.png'
},
{
ref: 'Ref(Collection("productPhotos"), "14135235")',
src: 'https://somewhere.com/photos/bczxvcxv.png'
}
]
}
]