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.