Query nested values and return the doc and only the matching nested docs

default discord avatar
rmcdev
4 weeks ago
5

As an example: Say I have a collection of shops, and I have a multivalue relationship in the shop for products. Each product has a price value. I'd like to do a query for products in shops that have a price of less than 10. Its simple to do this query where it returns any shop that has a product with a price less than 10, but in the results of that query it has all the products even those worth more than 10 which is not what I want. Is it possible to do a query like this?

  • default discord avatar
    reepicheep05
    4 weeks ago

    I believe you can. Would have to see a bit more of the architecture to get you a better answer. But I would think you could just query the products with an AND query that filters on shops and price.



    Something like this:



    // custom product endpoint at /price/:price
    // query like /api/products/price/10?shops=id1&shops=id2&shops=id3
    // there is probably better ways to pass an array here, just a quick example.
    
    let price = Number(query.params.price)
    let shops = query.params.shops;
    
    const products = await payload.find({
      collection: "pages", // required
      depth: 10,
      where: {
          and: [
            {
              'path.to.price.field': {
                less_than_equal: price,
              },
            },
            {
              "path.to.shop.id": {
                in: shops,
              },
            },
          ],
        },
    });
  • default discord avatar
    rmcdev
    4 weeks ago

    The product is related to the shop not the other way around. So its collection:"shops". I think the query would then return all the shops that match the query and they would have the full list of products within them so I'd have to filter through them manually which is my back up. I've got it structured this way because the relationship field lets our marketer easily reorder items within each shop and they required strict custom ordering of the products in the shop.

  • discord user avatar
    jarrod_not_jared
    Payload Team
    4 weeks ago

    Yes that’s what would happen 👆



    You can easily open a custom endpoint though! That is what I would do for a more complex query like this. Then just filter out the shops you don’t want and return the shops

  • default discord avatar
    rmcdev
    4 weeks ago

    Cool, yep that's what I think we're going to go with. I thought it might be the solution but was just doing some wishful thinking there might be a one step solution.

  • discord user avatar
    jarrod_not_jared
    Payload Team
    4 weeks ago

    It would require aggregations which can come at a query response time cost. I think custom endpoint is the move

Open the post
Continue the discussion in Discord
Like what we're doing?
Star us on GitHub!

Star

Connect with the Payload Community on Discord

Discord

online

Can't find what you're looking for?

Get help straight from the Payload team with an Enterprise License.