I am trying to do a filtered, grouped count and have the grouping
returned be an object, not an object id.
Consider a blogging application. I want to return a list of topics with
their posting counts, filtered by a user id.
This:
Topic.count :conditions => [“user_id = ?”, 1], :joins => “INNER JOIN
postings ON topics.id = postings.topic_id”, :group => “topics.id”,
:order => “count_all DESC”
Returns an array of arrays, each array containing a topic id and count,
like:
[[302, 12], [110, 7]]
That’s because the SQL generated by my command above looks like:
SELECT count(*) AS count_all, topics.id AS topics_id FROM topics INNER
JOIN postings ON topics.id = postings.topic_id WHERE (user_id = 1) GROUP
BY topics.id ORDER BY count_all DESC
This isn’t that useful, since I’ll need to go back and requery by topic
id to get the topic object. What I want is a result like this:
[[#<Topic:0x1234567 …>, 12], [#<Topic:0x7654321 …>, 7]]
What I imagine I need is something that generates SQL like this, which
AR then properly processes into the Topic object:
SELECT count() AS count_all, topics. AS topics_all FROM topics INNER
JOIN postings ON topics.id = postings.topic_id WHERE (user_id = 1) GROUP
BY topics.id ORDER BY count_all DESC
Is there a way to do this?
Thanks!
/afb