CODEWITHSUNDEEP
ruby-on-railspostgresqlsubqueryrails-activerecord
Patrick Brinich-Langlois
Patrick Brinich-Langlois

Reputation: 1429

How do you get a count of a subquery in Active Record?

I'm migrating an SQL query to Active Record. Here is a simplified version:

SELECT count(*) FROM (
  SELECT type, date(created_at)
  FROM notification_messages
  GROUP BY type, date(created_at)
) x

I'm not sure how to implement this in Active Record. This works, but it's messy:

sql = NotificationMessage.
  select("type, date(created_at) AS period").
  group("type", "period").
  to_sql
NotificationMessage.connection.exec_query("SELECT count(*) FROM (#{sql}) x")

Another possibility is to do the count in Ruby, but that would be less efficient:

NotificationMessage.
  select("type, date(created_at) AS period").
  group("type", "period").
  length

Is there a better solution?

Upvotes: 4

Views: 3155

Answers (1)

Arup Rakshit
Arup Rakshit

Reputation: 118271

Rails has the from method. So I would write it:

NotificationMessage
  .from(
    NotificationMessage
      .select("type, date(created_at)")
      .group("type, date(created_at)"), :x
  ).count

Upvotes: 8

Related Questions