How to get the item with the latest price?

Question

symfony

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 1

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