CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
Lonecat
Lonecat

Reputation: 2707

Is there a way to convert this to rails arel queries? Or a better way to do this query?

I have 3 tables: groups, users and posts. Posts have a user_id and Users have a group_id. I'd like to find out which group has the top poster between a date range, preferably only using ActiveRecord. I think this query does the job:

select g_id, count(p_id) as posts_count 
from (
  select users.group_id as g_id, posts.id as p_id 
  from users inner join posts 
  on users.id = posts.user_id 
  where users.group_id is not null
  and posts.created_at > :since
  and posts.created_at <= :until
) as foo 
group by g_id 
order by posts_count desc limit 1;

Can someone help me translate it to ruby?

Upvotes: 1

Views: 180

Answers (1)

Mr. EnOD
Mr. EnOD

Reputation: 21

It can be done in few steps. First - get count of posts, grouped by group_id.

posts = Post.includes(
        :user
    ).where(
        'users.group_id is not null'
    ).where(
        :posts => {:created_at => ('12.09.2011'.to_date)..('12.09.2012'.to_date)}
    ).group(
        :group_id
    ).count

It will return a hash like {1=>4, 2=>5, 3=>2}, where key is a id of group, and value is count of posts.

Then you need to sort this hash, and get last element of array.

sorted_hash = posts.sort_by {|key,value| value}
top_posters_group = sorted_hash[-1]

After that, top_posters_group[0] will be a id of group, and top_posters_group[1] is count of posts.

But anyway this isn't ideal solution.

Tested in rails 3.1 with ruby 1.9.2.

Upvotes: 2

Related Questions