https://www.prisma.io/ logo
Join Slack
Powered by
I have a table `Purchases` like this: ```id produc...
# orm-help
m
I have a table 
Purchases
like this:
Copy code
id product price date
1  1       10    2014-01-01
2  1       20    2014-02-17
3  1        5    2014-03-28
4  2       25    2014-01-05
5  2       12    2014-02-08
6  2       30    2014-03-12
And I want to get a result that contains only the record/row that has the max date, by 
product
. Thus I want to get a result that is like this:
Copy code
product price date
1        5    2014-03-28
2       30    2014-03-12
(because above for 
product
id 1 the most recent date is 2014-03-23 and for 
product
2 the most recent date is 2014-03-12. Could anyone help me with constructing a query in Prisma to do this (is it even possible?). I assume I'll be issuing a 
groupBy
? Thank you.
Sorry to bump but any help would be appreciated. Is this even possible without raw SQL (which I can do, I just can't see how to do this with Prisma alone).
For example the RAW SQL would look something like this:
Copy code
SELECT
  p.product,
  p.price,
  p.date
FROM
  products p INNER JOIN (
    SELECT
      product,
      MAX(date) AS max_date
    FROM
      products
    GROUP BY
      product) m
  ON p.product = m.product AND p.date = m.max_date
j
Hi @Michael for starters, you can first 
groupBy
product
i.e
Copy code
prisma.product.groudBy({
 by: ["product"]
})
I am too sure on an elegant way to do the max_date logic but you could try using 
orderBy
and 
distinc
something like;
Copy code
prisma.product.groudBy({
 by: ["product"],
 orderBy: {
    date: 'desc',
  },
 distinct: ['product']
})
m
Thank you for the reply Joey - I am not sure if what you wrote is legal - for one I tried it verbatim and it came back with "Type 'string' is not assignable to type 'never'" for distinct['product']. And when I read the logic I'm not sure if it'd give me the results I need - but thank you for the thoughts so much, I definitely cannot figure out how to do this in Prisma alone without RAW sql.
Open in Slack
PreviousNext
Powered by