Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP

Loading…

Can't join a join in Arel #84

Closed
bradphelan opened this Issue · 4 comments

2 participants

@bradphelan

I have a simple model

class User
    has_many :logs


class Logs

related in the usual way through the foreign key logs.user_id. I'm trying to do the following using Arel and according to the Arel doc it should work.

      u_t = User.arel_table
      l_t = Log.arel_table

      counts = l_t.
        group(l_t[:user_id]).
        project(
          l_t[:user_id].as("user_id"), 
          l_t[:user_id].count.as("count_all")
        ).as "counts"

      users = u_t.
        join(counts).
        on(u_t[:id].
        eq(counts[:user_id])).
        project("*")

 puts users.to_sql

When I do that I get an error. The SQL I would expect to be generated would be something like this

select * from users 
join ( select logs.user_id as user_id, count(logs.user_id) as count_all group by logs.user_id ) counts
on users.id = counts.user_id

The original fork of Arel suggest queries like this are possible but perhaps Arel has been re-factored
and now this is not possible.

@bradphelan

The backtrace i get is

 1) TaggableModel querying tags#tagged_with Experiments with AREL should
 Failure/Error: puts users.to_sql
 undefined method `name' for #<Arel::Nodes::Grouping:0x00000102e2d3c8>
 # /Users/bradphelan/.rvm/gems/ruby-1.9.2-p180/gems/arel-2.2.1/lib/arel/nodes/table_alias.rb:13:in `table_name'
 # /Users/bradphelan/.rvm/gems/ruby-1.9.2-p180/gems/arel-2.2.1/lib/arel/visitors/to_sql.rb:105:in `column_for'
 # /Users/bradphelan/.rvm/gems/ruby-1.9.2-p180/gems/arel-2.2.1/lib/arel/visitors/to_sql.rb:373:in `visit_Arel_Attributes_Attribute'
 # /Users/bradphelan/.rvm/gems/ruby-1.9.2-p180/gems/arel-2.2.1/lib/arel/visitors/visitor.rb:19:in `visit'
 # /Users/bradphelan/.rvm/gems/ruby-1.9.2-p180/gems/arel-2.2.1/lib/arel/visitors/to_sql.rb:350:in `visit_Arel_Nodes_Equality'
 # /Users/bradphelan/.rvm/gems/ruby-1.9.2-p180/gems/arel-2.2.1/lib/arel/visitors/visitor.rb:19:in `visit'
 # /Users/bradphelan/.rvm/gems/ruby-1.9.2-p180/gems/arel-2.2.1/lib/arel/visitors/to_sql.rb:308:in `visit_Arel_Nodes_On'
 # /Users/bradphelan/.rvm/gems/ruby-1.9.2-p180/gems/arel-2.2.1/lib/arel/visitors/visitor.rb:19:in `visit'
 # /Users/bradphelan/.rvm/gems/ruby-1.9.2-p180/gems/arel-2.2.1/lib/arel/visitors/to_sql.rb:304:in `visit_Arel_Nodes_InnerJoin'
 # /Users/bradphelan/.rvm/gems/ruby-1.9.2-p180/gems/arel-2.2.1/lib/arel/visitors/visitor.rb:19:in `visit'
 # /Users/bradphelan/.rvm/gems/ruby-1.9.2-p180/gems/arel-2.2.1/lib/arel/visitors/to_sql.rb:291:in `block in visit_Arel_Nodes_JoinSource'
 # /Users/bradphelan/.rvm/gems/ruby-1.9.2-p180/gems/arel-2.2.1/lib/arel/visitors/to_sql.rb:291:in `map'
 # /Users/bradphelan/.rvm/gems/ruby-1.9.2-p180/gems/arel-2.2.1/lib/arel/visitors/to_sql.rb:291:in `visit_Arel_Nodes_JoinSource'
 # /Users/bradphelan/.rvm/gems/ruby-1.9.2-p180/gems/arel-2.2.1/lib/arel/visitors/visitor.rb:19:in `visit'
 # /Users/bradphelan/.rvm/gems/ruby-1.9.2-p180/gems/arel-2.2.1/lib/arel/visitors/to_sql.rb:143:in `visit_Arel_Nodes_SelectCore'
 # /Users/bradphelan/.rvm/gems/ruby-1.9.2-p180/gems/arel-2.2.1/lib/arel/visitors/to_sql.rb:129:in `block in visit_Arel_Nodes_SelectStatement'
 # /Users/bradphelan/.rvm/gems/ruby-1.9.2-p180/gems/arel-2.2.1/lib/arel/visitors/to_sql.rb:129:in `map'
 # /Users/bradphelan/.rvm/gems/ruby-1.9.2-p180/gems/arel-2.2.1/lib/arel/visitors/to_sql.rb:129:in `visit_Arel_Nodes_SelectStatement'
 # /Users/bradphelan/.rvm/gems/ruby-1.9.2-p180/gems/arel-2.2.1/lib/arel/visitors/sqlite.rb:7:in `visit_Arel_Nodes_SelectStatement'
 # /Users/bradphelan/.rvm/gems/ruby-1.9.2-p180/gems/arel-2.2.1/lib/arel/visitors/visitor.rb:19:in `visit'
 # /Users/bradphelan/.rvm/gems/ruby-1.9.2-p180/gems/arel-2.2.1/lib/arel/visitors/visitor.rb:5:in `accept'
 # /Users/bradphelan/.rvm/gems/ruby-1.9.2-p180/gems/arel-2.2.1/lib/arel/visitors/to_sql.rb:18:in `block in accept'
 # /Users/bradphelan/.rvm/gems/ruby-1.9.2-p180/gems/activerecord-3.1.0/lib/active_record/connection_adapters/abstract/connection_pool.rb:185:in `with_connection'
 # /Users/bradphelan/.rvm/gems/ruby-1.9.2-p180/gems/arel-2.2.1/lib/arel/visitors/to_sql.rb:16:in `accept'
 # /Users/bradphelan/.rvm/gems/ruby-1.9.2-p180/gems/arel-2.2.1/lib/arel/tree_manager.rb:23:in `to_sql'
@bradphelan

Is this related to

#59

??

@bradphelan

Added a spec at

bradphelan@872c00a

to show the failing

  it 'can have a non table as rhs' do
    users   = Table.new :users
    logs    = Table.new :logs

    counts = logs.from(logs).
      group(logs[:user_id]).
      project(
        logs[:user_id].as("user_id"), 
        logs[:user_id].count.as("count")
      ).as("counts")

    j = users.join(counts).on(counts[:user_id].eq(10))

    j.to_sql.must_be_like  %{
      SELECT FROM "users" INNER JOIN (SELECT "logs"."user_id" AS user_id, COUNT("logs"."user_id") AS count FROM "logs" GROUP BY "logs"."user_id") counts ON counts.user_id = 10"

    }
  end
@bradphelan

This works now :)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.