Relationship graph using isl in active record

Question

I have a very difficult time figuring out how to make this request, and others, for example, in isl from the 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 

At first glance, this looks like Nick's example (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 on rails using active recording. I think the equivalent should be something like this, but it is wrong :(

  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 this?

Answer 1

Well, first replace the selection with the project. In relational algebra, SELECT (constraint) is a WHERE clause.

Secondly, you can do subsamples.

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

"sub-election" DONE !:-)

Answer 2

Yes, this article really made me want to learn Arel's magic.

All the questions “do something smart with Arel” in Stackoverflow get an answer in SQL. From articles and research, I can say that Arel is not ActiveRecord. Despite the dynamic statement of queries, Active is not able to display the results of a fully formed Arel projection.

You get the ability to specify operators with

 https://github.com/activerecord-hackery/squeel 

but without subqueries.

Updated: OMG, I answered this question 5 years ago. Don’t joke, the link was dead :)