Select from all customers the last record in Rails

Question

I'm trying to get from the statistics table the last record from the last day of each customer. I want to select the customer with the most sold items from the last day.

The stats are generated every hour so there are multiple statistics from the customer, but I need just the last one.

I tried several commands I just receive errors.

 Statistic.where("created_at < ?",1.day.ago).select(:customer_id).distinct.order(created_at: :desc)

or

Statistic.where("created_at < ?",1.day.ago).select(:customer_id).distinct.last

Is the best solution with pluck? Or should I create 2 selects? I have no idea where I should place 'sold_items'.

this could be also a solution, but I'm not sure.

Customer.all.each do |customer|
  stats = customer.statistics.where("created_at < ?",Date.today).last
  count = stats.sold_items if stats.sold_items > count
  best = customer if count = stats.sold_items
end

The Models: customer has_many :statistics - Statistic belongs_to :customer

could you please add model declarations? in order to make your first query to work, you should make a join. so the second. — marmeladze, Nov 28 '16 at 08:32
do you have a `has_many` `belongs_to` relation between your `Customer` and `Statistic` models ? — sa77, Nov 28 '16 at 08:34
Right. Customer has_many :statistics and Statistic belongs_to :customer — Archer, Nov 28 '16 at 08:35

score 2 · Accepted Answer · edited May 23 '17 at 12:30

Statistic.joins("inner join (select customer_id,max(created_at) as created_at from statistics group by customer_id) as stats2 on statistics.customer_id = stats2.customer_id and statistics.created_at = stats2.created_at").pluck("max(statistics.sold_items)")

This will give you the maximum sold items from the customer who sold most items on the last day so far,

Let me walk you through this,

Basically first of all you need the last statistics for each customer. then you need the maximum of sold_items from those statistics.

So the nested query (select customer_id,max(created_at) as created_at from statistics group by customer_id) is selecting the maximum created_at for each customer , then The outer query is joining the actual statistics with these results and selecting only those whose fall under the last date criteria. then at the end pluck("max(statistics.sold_items)") select the maximum sold_items from those results.

HERE is another link which finds the latest record from each group.

Wow, this is great! Thank you! – Archer Nov 28 '16 at 12:07 — Archer, Nov 28 '16 at 12:07

Select from all customers the last record in Rails

1 Answers1