Count of a relation using arel in active record

Question

I'm having a really rough time figuring out how to do this query and others like it in arel from active record.

  select users.id, 
         users.name, 
         maps.count as map_count, 
  from users
  left join (select user_id, count(map_id) as count from maps_users group by user_id) maps on users.id = maps.user_id

On the surface, it looks just like Nik's example here (http://magicscalingsprinkles.wordpress.com/2010/01/28/why-i-wrote-arel/):

photo_counts = photos.
group(photos[:user_id]).
project(photos[:user_id], photos[:id].count)

users.join(photo_counts).on(users[:id].eq(photo_counts[:user_id]))

But I can't get it to work in rails using active record. I think the equivalent should be something like this, but it errors out :(

  maps = Map.arel_table
  map_counts = Map.group(maps[:owner_id]).
                   select(maps[:owner_id]).
                   select(maps[:id].count.as("map_count"))
  users = User.joins(map_counts).on(User.arel_table[:id].eq(map_counts[:map_count]))

Any ideas on how to do it?

Answer 1

Well first replace the select with project. In relational algebra SELECT (restriction) is the WHERE clause.

Secondly you can do subselections.

sub_restriction = b.
                   where( b[:domain].eq(1) ).
                   project( b[:domain] )

restriction = a.
               where( a[:domain].in sub_restriction )

"sub selections" DONE! :-)