I'm using Prisma with a MySQL database for a project, an online shop, where the same product can be sold by multiple suppliers.

At a certain part of my database I have a table structure such as this one:

• Product (id, name, ...)
• Supply (productId, supplier, price, stock, ...)

The Supply table represents the fact that a supplier sells a certain product. Obviously, a Product can have multiple Supply entries, as many as the number of suppliers that sell said product.

I'm looking to use Prisma's findMany operation to sort products by the lowest price out of all Supplies a product has. I can easily calculate this value after obtaining all products, and sort in that way, but that wouldn't be scalable, and I'm also using pagination with take and skip for efficiency purposes, rendering that alternative pointless.

As an example, say two Product products have two Supplies each:

• Product X, sold by supplier A at 63€ and by supplier B at 75€
• Product Y, sold by supplier B at 54€ and by supplier A at 60€

The natural result I'm looking for (when sorting in ascending order) would be:

1. Product Y
2. Product X

As product X's minimum Supply price is lower than product Y's minimum Supply value.

I've thoroughly researched Prisma's documentation regarding this situation and cannot find a solution. I would very much like to implement this using the orderBy property for the findMany operation instead of using raw SQL queries, as I have implemented many other types of sorting the Prisma way.

Any help regarding this issue is appreciated, thanks in advance.