How do I write a Rails search method that returns the largest date grouped by record?

Question

I am 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 for a currency (where last_updated greater) for the selected currencies. I can find all prices associated with certain currencies, such as

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

Then I can sort them by currency into arrays, last_updated over them each, until I find the value with the highest last_updated value, but how can I write a search engine that returns exactly one row for each currency with the longest last_updated date?

Edit: Tried an owl max. offer 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 shows the size of "12", the second puts shows more than 38,000 results. It should return only 12 results, one for each currency.

Answer 1

Only works with Rails5 due to or request 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 rearrange your entire CryptoPrice object CryptoPrice each CryptoIndexCurrency in the table.

Then, using maximum (thanks @artgb), you will get the highest last_updated value. This will lead to the output of the Hash using the keys: crypto_currency_id and the value of last_updated .

Finally, you can use keys to get Array 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 all records.

To get entries, you can loop the hash in pairs. with crypto_currency_id and last_updated . This is hacking, but the only solution I found.

Answer 2

We can write a search engine that returns exactly one row for each currency with the longest last date of the date in such a way

 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 this brings you closer to your goal. Thanks.

Answer 3

Using this code, you can get the latest updated row from this table.

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

This should help you.

Answer 4

In Rails, this is not so easy to do in a single statement / request. If you do not mind using multiple operators / queries, then 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 gives 2 requests for each crypto_currency_id request and 1 for crypto_currency_id s request.

If you want to do this with a single query, you need to use OVER (PARTITION BY ...) . More on this in the following links:

• Extract row with maximum value for column
• https://docs.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 case you have to write some SQL.

EDIT 1:

If you need a good Hash result:

 cc_ids.zip(result).to_h 

EDIT 2:

If you want to double the number of queries, you can drag the max_last_updated query to find_by as a subquery, for example:

 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 creates 1 request for each crypto_currency_id request and 1 for crypto_currency_id s request.