Eagerloading loading a one to many relationship

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 particular shopProducts record belongs to.

productPhotos collection:

  • src
  • productRef <— I use this to identify to what shopProducts a particular productPhotos 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'
      }
    ]
  }
]

I can do this query BUT it doesn’t produce the output that I am looking for, I might still need to make some computations on my end to produce that, but I’m looking to do it on the FQL side…

Let(
  {
    shopProducts: Map(
      Paginate(
        Match(
          Index('shopProductsByShop'),
          Ref(Collection('shops'), '270198156233277958')
        ),
        { size: 10 }
      ),
      Lambda(
        'ref',
        Get(Var('ref'))
      )
    ),
    productPhotos: Map(
      Var('shopProducts'),
      Lambda(
        'shopProduct',
        Paginate(
          Match(
            Index('productPhotosByProduct'),
            Select(['ref'], Var('shopProduct'))
          ),
          { size: 6 }
        )
      )
    )
  },
  {
    shopProducts: Var('shopProducts'),
    productPhotos: Var('productPhotos')
  }
)

Part 2 of the question is this: how do you go about doing the same thing, except its for an array of shopProducts? Imagine this:

Map(
  Paginate(
    Match(Index('shopProductsByShop'), Ref(Collection('shops'), shopId))
  ),
  Lambda('ref', Get(Var('ref')))
)

This query will return an array of shopProducts, each items in that array has many productPhotos, I’m imagining I have to loop through each of those items and grab the productPhotos individually but I cannot get a clear picture the query…

I thought of something like so:

Let(
  {
    products: Map(
      Paginate(
        Match(Index('shopProductsByShop'), Ref(Collection('shops'), '270198156233277958'))
      ),
      Lambda('ref', Get(Var('ref')))
    ),
    photos: Map(
      Var('products'),
      Lambda(
        'product',
        Paginate(
          Match(Index('productPhotosByProduct'), Select(['ref'], Var('product')))
        )
      )
    )
  },
  {
    products: Var('products'),
    photos: Map(
      Select(['data'], Var('photos')),
      Lambda(
        'photo',
        Map(
          Select(['data'], Var('photo')),
          Lambda(
            ['ref', 'productRef'],
            Get(Var('ref'))
          )
        )
      )
    )
  }
);

Which will grab shopProducts, loop through each shopProducts and get the productPhotos, then I can infer that the photos of products[0] is productPhotos[0]

But I’m pretty sure this is not a good way of doing it.

I think you’re getting close to the answer. You want to return an object from Let with the properties you want.

Check out this gist. https://gist.github.com/ptpaterson/82c01afc9b0ff624f96141a078b5ab54

(How familiar if at all are you with GraphQL. Imagine you are trying to create your own response to a GraphQL query. Doing just that is how I got familiar with building nested objects.)

You can try something like this:

q.Map(Paginate(Documents(Collection('shops')) , (ref) =>
  q.Let(
    {
      instance: q.Get(q.Var('ref'))
    },
    {
      _id: q.Select(['id'], q.Var('ref')),
      _ts: q.Select(['ts'], q.Var('instance')),

      title: q.Select(['data', 'title'], q.Var('instance'), null),
      description: q.Select(['data', 'description'], q.Var('instance'), []),

      products: q.Map(
        q.Paginate(Match(Index('shopProductsByShop'), q.Var('ref'))),
        (ref) =>
          q.Let(
            {
              instance: q.Get(q.Var('ref'))
            },
            {
              /* repeat above recursively */
              /* `ref` and `instance` variables are scoped! */
               _id: q.Select(['id'], q.Var('ref')),
               _ts: q.Select(['ts'], q.Var('instance')),

              title: q.Select(['data', 'title'], q.Var('instance'), null),
              description: q.Select(['data', 'description'], q.Var('instance'), []),

              photos: q.Map(
                q.Paginate(Match(Index('photosByShop'), q.Var('ref'))),
                (ref) =>
                  q.Let(
                    {
                      instance: q.Get(q.Var('ref'))
                    },
                    {
                      /* repeat above recursively */
                      /* `ref` and `instance` variables are scoped! */
                      _id: q.Select(['id'], q.Var('ref')),
                      _ts: q.Select(['ts'], q.Var('instance')),

                      src: q.Select(['data', 'src'], q.Var('instance'), null),
                    }
                  )
              ),
            }
          )
      ),
    }
  )
)
1 Like

Thank you very much for answering @ptpaterson. I was thinking that the way I can do this is by using spread operator, I learned about Merge function which is exactly what I needed.

Map(
  Paginate(
    Match(Index('shopProductsByShop'), Ref(Collection('shops'), '270198156233277958'))
  ),
  Lambda(
    'ref',
    Merge(
      Get(Var('ref')),
      {
        photos: Map(
          Paginate(
            Match(Index('productPhotosByProduct'), Var('ref'))
          ),
          Lambda(
            ['ref', 'productRef'],
            Get(Var('ref'))
          )
        )
      }
    )
  )
)