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:
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:
The natural result I'm looking for (when sorting in ascending order) would be:
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.