How to sort with nest order with Prisma

Question

I have 2 table

model Product {
 id      
 name    
 skus[]   Sku
}

model Sku {
 id
 name
 price
 weight
 productId
 product  Product
}

Data example:

• Product table

id    |    name
--------------------
1     |    product 1
2     |    product 2
3     |    product 3 

• Sku table

id   |  name   | price  | weight | product_id  
--------------------------------------------------
1    | sku 1   |  10    |  2     |   1
2    | sku 2   |  12    |  1     |   1
3    | sku 3   |  11    |  3     |   1
4    | sku 4   |  12    |  3     |   2
5    | sku 5   |  15    |  1     |   2

=>> result expected:

• sort desc product by price of sku

id  | name  
---------
2   | product 2
1   | product 1

Now I want to get all products out as sorted by their price take lowest price of a SKU variation if a product contains more than 1 product variant.

Note: use prisma query

Answer 1

if i understand your question right (as your desired output lacks SKU ids) in the case of prisma you should basically orderBy the relation and the parent.

Also, if you start querying by some properties of the relation (skus in this case) you should remember to adopt the correct "some"/"all" etc.. in the relation filter condition otherwise the result might not be the expected, see: https://www.prisma.io/docs/concepts/components/prisma-client/relation-queries#relation-filters

prisma.Product.findMany({
      include: {
        skus: {
          orderBy: {
            price: 'asc'
          },
          // Remove to get all skus instead of the "cheapest"
          take: 1
        }
      },
      orderBy: {
        id: 'desc'
      }
})

Turning on debug log on prisma is a good idea: https://www.prisma.io/docs/concepts/components/prisma-client/debugging