0

I have two similar tables in rails named as messages and message_histories. Periodically we remove the old data from messages table and drop it in message_histories .

Now , I want to generate a report on the count of messages grouped by app_id which is present in message and message_history table .

Is there a way to Query [Message & MessageHistory ] Model and paginate on the records .

Currently I use the following Step , It looks weird but suggest a better way to do this :

@messages = Message.select("SUM(devices_count) as count ,CAST(run_at AS DATE) AS date").where(:app_id => @app.id).where("last_run_date is not ?", nil).group("CAST(run_at AS DATE)").order("date desc")
@messages << MessageHistory.select("SUM(devices_count) as count ,CAST(run_at AS DATE) AS date").where(:app_id => @app.id).where("last_run_date is not ?", nil).group("CAST(run_at AS DATE)").order("date desc")
@messages = @messages.flatten.paginate(:page => params[:page] || 1, :per_page => 100)
Santosh Mohanty
  • 468
  • 1
  • 6
  • 23
  • Not aware of a neat way to do this rails way but you can go for `ActiveRecord::Base.connection.execute()` and run the query over both tables – Deepak Mahakale Nov 04 '16 at 13:15
  • you can do something like `polymorphic association` for messages history as it is one form of messages. no need a second table for message histories – Rajarshi Das Nov 04 '16 at 13:17

1 Answers1

0

It sounds like what you want is to UNION the two tables, then paginate the results.

See ActiveRecord Query Union for some examples, and look at the active_record_union gem (which makes it easier to do UNIONs with ActiveRecord).

It will take some experimentation to figure out how to apply the where clause filters and sums/groups properly!

Community
  • 1
  • 1
gmcnaughton
  • 2,233
  • 1
  • 21
  • 28