What just happened to Arel and what do I do with an Arel::SelectManager?

Question

I'm desperately trying to make sense of Arel, mostly because I hate dealing with SQL; I was doing so well, but I've hit a wall.

I've been working in Rails 3.0.0, and I'm trying to make a complex query with some math in it. The real case is rather more complex, but I've simplified a bit. In my example, I have a table with a particular string field, and I want a count of all the records, as well as a count for each of two possible values of that field, grouped by a foreign id.

Under Rails 3.0.0, I can do this (console commands):

t = Arel::Table.new(:some_thingies)
e = t                                  .project(t[:foreign_id], t[:foreign_id].count.as('all_count'))  .group(t[:foreign_id])
c = t.where(t[:some_field].eq('type1')).project(t[:foreign_id], t[:foreign_id].count.as('type1_count')).group(t[:foreign_id])
x = e  
x = x.join(c).on(e[:foreign_id].eq(c[:foreign_id])) 

and at this point I can do do x.to_sql and... well, I'm not entirely sure it's right, but the results look right, aside from having the foreign_id column twice.

SELECT     
  `some_thingies_external`.`foreign_id`, 
  `some_thingies_external`.`all_count`, 
  `some_thingies_external_2`.`foreign_id`, 
  `some_thingies_external_2`.`type1_count` 
FROM       
  (SELECT     
    `some_thingies`.`foreign_id`, COUNT(`some_thingies`.`foreign_id`) 
   AS `type1+count` 
   FROM       `some_thingies`  
   GROUP BY  `some_thingies`.`foreign_id`) `some_thingies_external`  
INNER JOIN 
  (SELECT     `some_thingies`.`foreign_id`, COUNT(`some_thingies`.`foreign_id`) 
   AS `type1_count` 
   FROM       `some_thingies`  
   WHERE     `some_thingies`.`type` = 'type1' 
   GROUP BY  `some_thingies`.`foreign_id`) `some_thingies_external_2` 
ON `some_thingies_external`.`foreign_id` = `some_thingies_external_2`.`foreign_id`

So far so good. However, when I try to join in a second set of counts like this:

i = t.where(t[:some_field].eq('type2')).project(t[:foreign_id], t[:foreign_id].count.as('type2_count')).group(t[:foreign_id])
x = x.join(i).on(e[:foreign_id].eq(i[:foreign_id]))

it just hangs up, leading me to think I'm hitting this bug

(btw I've got more counts to add in, and ideally 'some_thingies' should itself be an arel object representing more filtering on which thingies we're counting... but I digress...)

So, I decided to try out the latest edge Arel and Rails, and bumped up my gems accordingly:

gem 'rails', :git => 'git://github.com/rails/rails.git'
gem 'rack', :git => 'git://github.com/rack/rack.git'
gem 'arel', :git => 'http://github.com/brynary/arel.git'

and now when I try to do do the first join, it fails miserably:

ruby-1.9.2-preview3 >     x = x.join(c).on(e[:foreign_id].eq(c[:foreign_id])) 
NoMethodError: undefined method `[]' for #<Arel::SelectManager:0x00000104311e38>
    from (irb):12
    from /Users/stephan/.rvm/gems/ruby-1.9.2-preview3/bundler/gems/rails-c42ea2172eb9/railties/lib/rails/commands/console.rb:44:in `start'
    from /Users/stephan/.rvm/gems/ruby-1.9.2-preview3/bundler/gems/rails-c42ea2172eb9/railties/lib/rails/commands/console.rb:8:in `start'
    from /Users/stephan/.rvm/gems/ruby-1.9.2-preview3/bundler/gems/rails-c42ea2172eb9/railties/lib/rails/commands.rb:33:in `<top (required)>'
    from script/rails:6:in `require'
    from script/rails:6:in `<main>'

This doesn't seem like a bug in Arel - if anything, it seems more like the fact that it worked before is the bug. I think I just don't know what an Arel::SelectManager is and what to do with it. It seemed like I was doing so well, but I really don't get what is going on.

Do I need to somehow make a new table based on the SelectManager I've got? Or am doing something wrong in my configuration which makes the [] syntax fail? Or do I just totally fail to understand what Arel does? I still don't quite get what I'm supposed to do with Arel::Rows, but I suppose I'll get the hang of that; and I suspect I can get rid of the extra foreign key in the results with a project()...

But I'm still pretty lost. Haaaaalp!

p.s. does 'railties' rhyme with 'frailties,' or with 'mail guys'?

Answer 1

I'm going to answer my own question, since no one seems interested, and now that I know what I'm doing wrong, I can see it would be obvious if I understood SQL.

Problem 1 with the way I was using Arel is you can only join a freshly-made table. That's beside the point.

The real problem is I'm trying to count two different things. I really should be grouping by the foreign ID AND the 'some_field'. I just didn't know you could do that, and the results from it are a little weird. If I didn't care about all the possible values of some_field, this might be annoying, but I do care about them all, and I can add them up easily enough to get the total, and it's easy to filter them out now.

t = Arel::Table.new(:some_thingies)    
e = t.group(:foreign_id, :some_field).project(t[:id], t[:foreign_id], t[:some_field])

Once I figured that out, I figured out how to do it with normal ActiveRecord and no ARel:

SomeThing.group('foreign_id, some_field').select('id, foreign_id, some_field, count(1)')

D'oh! Moral: SQL only knows about rows. Period.

Answer 2

Arel has been completely redone from the guts up. This initiative was started by tenderlove (Aaron)There were performance problems problems on largely composed queries.

I have even made contributions to this new initiative myself.

Arel now uses the Abstract Syntax Tree (within Select Manager) and the Visitor Pattern.

You might as well scrap the way that Arel 1.0.1 works in flavor for 2.0.0 (on the way to 3.0.x to align with rails)