How do I write a Rails finder method that will return the greatest date grouped by record?

Question

I'm using Rails 5 with PostGres 9.5. I have a table that tracks prices ...

                                         Table "public.crypto_prices"
       Column       |            Type             |                         Modifiers
--------------------+-----------------------------+------------------------------------------------------------
 id                 | integer                     | not null default nextval('crypto_prices_id_seq'::regclass)
 crypto_currency_id | integer                     |
 market_cap_usd     | bigint                      |
 total_supply       | bigint                      |
 last_updated       | timestamp without time zone |
 created_at         | timestamp without time zone | not null
 updated_at         | timestamp without time zone | not null

I would like to get the latest price per currency (where last_updated is greatest) for a select currencies. I can find all the prices related to certain currencies like so

current_prices = CryptoPrice.where(crypto_currency_id: CryptoIndexCurrency.all.pluck(:crypto_currency_id).uniq)

Then I can sort them by currency into arrays, looping through each until I find the one with the greatest last_updated value, but how can I write a finder that will return exactly one row per currency with the greatest last_updated date?

Edit: Tried Owl Max's suggestion like so

ids = CryptoIndexCurrency.all.pluck(:crypto_currency_id).uniq
crypto_price_ids = CryptoPrice.where(crypto_currency_id: ids).group(:crypto_currency_id).maximum(:last_updated).keys
puts "price ids: #{crypto_price_ids.length}"
@crypto_prices = CryptoPrice.where(crypto_currency_id: crypto_price_ids)
puts "ids: #{@crypto_prices.size}"

Although the first "puts" only reveals a size of "12" the second puts reveals over 38,000 results. It should only be returning 12 results, one for each currency.

Answer 1

We can write a finder that will return exactly one row per currency with the greatest last_updated date in such a way like

current_prices = CryptoPrice.where(crypto_currency_id: CryptoIndexCurrency.all.pluck(:crypto_currency_id).uniq).select("*, id as crypto_price_id, MAX(last_updated) as last_updated").group(:crypto_currency_id)

I hope that this will took you closer to your goal. Thank you.

Answer 2

Using this code you can fetch the latest updated row here from particular table.

  CryptoPrice.order(:updated_at).pluck(:updated_at).last

This Should be help for you.

Answer 3

Only works with Rails5 because of or query method

specific_ids = CryptoIndexCurrency.distinct.pluck(:crypto_currency_id)
hash = CryptoPrice.where(crypto_currency_id: specific_ids)
                  .group(:crypto_currency_id)
                  .maximum(:last_updated)
hash.each_with_index do |(k, v), i|
  if i.zero?
    res = CryptoPrice.where(crypto_currency_id: k, last_updated: v)
  else
    res.or(CryptoPrice.where(crypto_currency_id: k, last_updated: v))
  end
end

Explanation:

You can use group to regroup all your CryptoPrice object by each CryptoIndexCurrency presents in your table.

Then using maximum (thanks to @artgb) to take the biggest value last_updated. This will output a Hash with keys: crypto_currency_id and value last_updated.

Finally, you can use keys to only get an Array of crypto_currency_id.

CryptoPrice.group(:crypto_currency_id).maximum(:last_updated)
=> => {2285=>2017-06-06 09:06:35 UTC,
       2284=>2017-05-18 15:51:05 UTC,
       2267=>2016-03-22 08:02:53 UTC}

The problem with this solution is that you get the maximum date for each row without getting the whole records.

To get the the records, you can do a loop on the hash pairwise. with crypto_currency_id and last_updated. It's hacky but the only solution I found.

Answer 4

This is currently not easy to do in Rails in one statement/query. If you don't mind using multiple statements/queries than this is your solution:

cc_ids = CryptoIndexCurrency.distinct.pluck(:crypto_currency_id)

result = cc_ids.map do |cc_id|
  max_last_updated = CryptoPrice.where(crypto_currency_id: cc_id).maximum(:last_updated)
  CryptoPrice.find_by(crypto_currency_id: cc_id, last_updated: max_last_updated)
end

The result of the map method is what you are looking for. This produces 2 queries for every crypto_currency_id and 1 query to request the crypto_currency_ids.

If you want to do this with one query you'll need to use OVER (PARTITION BY ...). More info on this in the following links:

• Fetch the row which has the Max value for a column
• https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql
• https://blog.codeship.com/folding-postgres-window-functions-into-rails/

But in this scenario you'll have to write some SQL.

EDIT 1:

If you want a nice Hash result run:

cc_ids.zip(result).to_h

EDIT 2:

If you want to halve the amount of queries you can shove the max_last_updated query in the find_by as sub-query like so:

cc_ids = CryptoIndexCurrency.distinct.pluck(:crypto_currency_id)

result = cc_ids.map do |cc_id|
  CryptoPrice.find_by(<<~SQL.squish)
    crypto_currency_id = #{cc_id} AND last_updated = (
      SELECT MAX(last_updated) 
      FROM crypto_prices 
      WHERE crypto_currency_id = #{cc_id})
  SQL
end

This produces 1 queries for every crypto_currency_id and 1 query to request the crypto_currency_ids.