R
R
Romua1d2020-07-13 00:41:42
symfony
Romua1d, 2020-07-13 00:41:42

How to get the item with the latest price?

Conditions: there is a Product and there is a ManyToOne Price (value, created_at, product_id)
How to put together a query in a query builder that will display products with a price (the last one according to created_at)? On bare SQL, it looks like this

SELECT *,
       (
           SELECT id FROM price
           WHERE price.product_id = product.id
           ORDER BY created
           DESC LIMIT 1
       )
FROM product

Answer the question

In order to leave comments, you need to log in

1 answer(s)
M
Maxim Fedorov, 2020-07-13
@Romua1d

Related answer:

  • or make a lastPrice field and store the last price there (well, or use a custom hydrator to insert it into the product field)
  • or select the required data in raw SQL/DQL in DTO objects,
  • or to the extreme one - separately choose the latest prices for your goods, but here, in fact, there is some semblance of the second option

Retrieving the last price in an array of prices is an incorrect and very bad idea, even if you manage to do it. You will get a "broken" business object and in general all this is bad and not convenient and not correct.
Query tip :
Better make it not a nested query, but through a join
SELECT product.*, last_prices.*
FROM product
LEFT JOIN (
     SELECT product_id AS id, max(id) as price_id
     FROM price
     GROUP BY product_id
) last_prices  ON last_prices.id=product.id

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question