ActiveRecord `group` breaks counts

[bear454]

I wanted to be able to search against a calculated field, and so extended my get_raw_records defintion to perform a join+group, as described in Rails Guides:

Author.left_outer_joins(:posts).distinct.select('authors., COUNT(posts.) AS posts_count').group('authors.id')

This breaks counters though, as records_total_count returns a hash ( like authors.id => count... )

I was able to work around this by overriding records_total_count and records_filtered_count to use unscope(:group) to get back to a single countable value:

  def records_total_count
    fetch_records.unscope(:group).count(:all)
  end

  def records_filtered_count
    filter_records(fetch_records).unscope(:group).count(:all)
  end

I intended to submit that as a patch; but I realized the whole thing is ActiveRecord-specifc, and therefore probably needs a bigger refactor, to move the count functions down to the ORM adapter... but that's a decision best left to the maintainers.

Thoughts?

[keymastervn]

Well, I have to bump this up. In my case, I do group too because I have to aggregate to get the roles. So far I have to unscope :select too

User.joins(:roles)
    .select("users.id, users.avatar_url, users.first_name, users.last_name, email, array_agg(roles.name) as role_lists")
    .group(:"users.id", :"users.avatar_url", :"users.first_name", :"users.last_name", :email)
    .where("roles.resource_id": nil)
    .references(:roles).distinct

and fixes

  def records_total_count
    fetch_records.unscope(:group).unscope(:select).count(:all)
  end

  def records_filtered_count
    filter_records(fetch_records).unscope(:group).unscope(:select).count(:all)
  end

[clintbullock]

For a quick fix if you don't have a lot of records:

Use the length method here which will load the results and then count the records as an Array rather than issuing an extra COUNT SQL query.

  def records_total_count
    fetch_records.length
  end

  def records_filtered_count
    filter_records(fetch_records).length
  end

For lots of records, maybe use a subquery

Use the following format for a quick fix:

SELECT COUNT(*) FROM (subquery) AS some_alias;

  def records_total_count
    User.from("(#{fetch_records.to_sql}) AS a").pick("COUNT(*)")
  end

  def records_filtered_count
    User.from("(#{filter_records(fetch_records).to_sql}) AS a").pick("COUNT(*)")
  end