CODEWITHSUNDEEP
sqlruby-on-railsrubyactiverecord
mikker
mikker

Reputation: 831

Get series of counts with conditions using ActiveRecord

I'm displaying a graph of Subscription counts that may have been soft_destroyed_at at some point.

To do this I run a query for each month which is of course not as good as one big honking query but my SQL skills fail me once again.

Here's how I do it in Ruby:

months = (0..12).map { |i| i.months.ago.end_of_month }

stats = Hash[months.map do |eom|
  [
    eom.beginning_of_month.to_date,
    Subscription.where(
      'created_at < ?' \
      'AND (soft_destroyed_at IS NULL ' \
      '  OR soft_destroyed_at > ?) ' \
      'AND (suspended_at IS NULL ' \
      '  OR suspended_at > ?)',
      eom, eom, eom
    ).count
  ]
end]

# => { 2018-04-01 => 10, 2018-03-01 => 15, ... }

How could I write this as one query using ActiveRecord – or if necessary using raw SQL>

The database is Postgres 10.2, the app is Rails 5.2.

Thanks!

Upvotes: 3

Views: 343

Answers (1)

Pablo
Pablo

Reputation: 3005

You can use this query (I used 12 month in 2017; just change as you wish). This assumes a Postgresql DB, as you said in a comment:

query = 
  "select months.month, count(created_at) "\
  "from "\
    "(select DATE '2017-01-01'+(interval '1' month * generate_series(0,11)) as month, "\
            "DATE '2017-02-01'+(interval '1' month * generate_series(0,11)) as next) months "\
    "outer join subscriptions on "\
    "created_at < month and "\
    "(soft_destroyed_at IS NULL or soft_destroyed_at >= next) and "\
    "(suspended_at IS NULL OR suspended_at >= next) "\
  "group by month "\
  "order by month"

results = ActiveRecord::Base.connection.execute(query)

The first subquery (the select inside from) generates this:

month                 next
"2017-01-01 00:00:00";"2017-02-01 00:00:00"
"2017-02-01 00:00:00";"2017-03-01 00:00:00"
"2017-03-01 00:00:00";"2017-04-01 00:00:00"
"2017-04-01 00:00:00";"2017-05-01 00:00:00"
"2017-05-01 00:00:00";"2017-06-01 00:00:00"
"2017-06-01 00:00:00";"2017-07-01 00:00:00"
"2017-07-01 00:00:00";"2017-08-01 00:00:00"
"2017-08-01 00:00:00";"2017-09-01 00:00:00"
"2017-09-01 00:00:00";"2017-10-01 00:00:00"
"2017-10-01 00:00:00";"2017-11-01 00:00:00"
"2017-11-01 00:00:00";"2017-12-01 00:00:00"
"2017-12-01 00:00:00";"2018-01-01 00:00:00"

Next is only used to make it easier to check if the subscription was active at least until next month (destroyed or suspended are >= next (which guarantees the subscriber was active during current month).

Upvotes: 1

Related Questions