How do get the first record per day from a date range?

I have some records that I’ve queried, and now I want to do some additional filtering.

balance_snapshots = BalanceSnapshot.where([ ... ])
[ ... ]
balance_snapshots_per_day = []
(start_date.to_datetime..end_date.to_datetime).each do |day|
    balance_snapshots_per_day << balance_snapshots.where(created_at: day).first
end
balance_snapshots = balance_snapshots_per_day.compact

The problem with this code is that the result is just a plain array now, and now I can’t do any further filtering with activerecord methods.

Is there a way to achieve something similar, but where I end up with an active record result set rather than a plain array?

Thanks.

Hi Blaine,

I think that you can do a new query with the selected records, as explainded here by Matt Swanson → Prefer returning chainable ActiveRecord objects | Boring Rails: Skip the bullshit and ship fast

Something like this…

balance_snapshots = balance_snapshots_per_day.compact
BalanceSnapshot.where(id: balance_snapshots.map(&:id))

As an extra comment… I think that inside the loop you should avoid doing a query to the database, you can use ruby to select the record…

selected = (start_date..end_date).map do |day|
  balance_snapshots.detect { |s| s.created_at.to_date == day }
end.compact

And maybe to avoid the O(days * balance_snapshots) you can try to group the snapshots by day first…

grouped = balance_snapshots.group_by { |s| s.created_at.to_date }
selected = (start_date..end_date).map { |day| grouped[day]&.first }.compact
BalanceSnapshot.where(id: selected.map(&:id))

Also, maybe there is a way to select “the first record per day” within the database… but I am not sure what did you do on you previous query…

I hope this can help you :slight_smile:

2 Likes

That should work, thanks!

1 Like

There is a way to return the first record for each group (partition) if you are using SQL. You’ll need to write some manual SQL using window functions though MySQL Window Functions: An Essential Guide to Window Functions in MySQL.

You’ll need to use FIRST_VALUE, OVER, PARTITION and the ordering. There’s an example of the sql here MySQL FIRST_VALUE Window Function By Practical Examples