Newest items and GROUP By with Eloquent

Question

I have the following prices-table:

shop_id (int)
product_id (int)
price (float)
created (DateTime)

Every hour a cronjob checks the shops and inserts new entries (current prices) into these price-table.

Now I want to display the newest price for a product. I have to GROUP BY the shop_id because I only want one price per shop but I only want the newest entry (created).

Can I solve this with Eloquent Query-Builder or do I have to use raw SQL? Is it possible to pass the result of a raw SQL-query into a model if the columns are the same?

Amit Gupta · Answer 1 · 2016-12-19T13:50:37.083

1

You can try it as:

Price::select('*', DB::raw('MAX(created_at) as max_created_at'))
      ->groupBy('shop_id')
      ->get()

Assuming model name is Price

edited Dec 19 '16 at 13:50

answered Dec 19 '16 at 06:03

Amit Gupta

17,072
4
41
53

SQLSTATE[42000]: Syntax error or access violation: 1055 'xyz.prices.id' isn't in GROUP BY (SQL: select `prices`.*, MAX(created_at) as max_created_at from `prices` group by `shop_id`) – rakete Dec 19 '16 at 12:06
This error is related to MySql. You can check out [this](http://stackoverflow.com/questions/41199800/group-by-laravel/41200112#41200112) answer for the solution – Amit Gupta Dec 19 '16 at 14:21
Hi Amit! I can execute it now without any errors but the DB::raw() doesn't affect the result in anyway. I can remove it, nothings changes. The result is the first row of the shop, independent of the created_at.. Is there missing a where() or something like that, where I check, that the created_at equals max_created_at? – rakete Dec 26 '16 at 13:47

score 0 · Answer 2 · answered Dec 19 '16 at 06:15

Eloquent (purist) approach:

Price::orderBy('created', 'desc')->groupBy('shop_id')
                                 ->get('shop_id', 'price');

References:

https://laravel.com/api/5.3/Illuminate/Database/Query/Builder.html#method_orderBy

https://laravel.com/api/5.3/Illuminate/Database/Query/Builder.html#method_groupBy

https://laravel.com/api/5.3/Illuminate/Database/Query/Builder.html#method_get

*untested though

Q: Is it possible to pass the result of a raw SQL-query into a model if the columns are the same?

A: you could pass it to Model's contructor - but it might need model's field to be fillable - or hydrate a model. Alternatively, just access it like an keyed-array, ie. $something[0]['price'] <-- assuming an array of prices with price column.

@rakete so, any clue on what error did you encounter, or the output just not as good as you expect? — Bagus Tesa, Dec 20 '16 at 00:57

score 0 · Accepted Answer · answered Dec 26 '16 at 13:59

I solved the problem without QueryBuilder. Instead I use a raw SQL-statement and generating the models with the hydrateRaw()-function of the Model-class.

$prices = Price::hydrateRaw( 'SELECT p.*
      FROM prices p
      INNER JOIN (
        SELECT shop_id, max(created_at) AS max_ca
        FROM prices p1
        GROUP BY shop_id
      ) m ON p.shop_id = m.shop_id AND p.created_at = m.max_ca');

Newest items and GROUP By with Eloquent

3 Answers3