Rails - Dec 2010 - ActiveRecord syntax for multiple joins on same table?

In my ongoing quest to wean myself of find_by_sql(), I now need to know
how to do multiple joins into a single table using the spiffy new
ActiveRecord query syntax.

As an example, imagine that an Edge is defined by its two vertices:

  create_table "vertices", :force => true do |t|
    t.float    "x"
    t.float    "y"
    t.float    "z"
  end

  create_table "edges", :force => true do |t|
    t.integer  "vertex_a_id"
    t.integer  "vertex_b_id"
  end

An SQL query to enumerate all points connected by edges might look like:

  SELECT a.x as x0, a.y as y0, a.z as z0, b.x as x1, b.y as y1, b.z as
z1
    FROM edges
    JOIN vertices as a on a.id = edges.vertex_a_id
    JOIN vertices as b on b.id = edges.vertex_b_id

Any idea how to express this query in ActiveRecord query syntax?

- ff

(As an aside, I find that AREL is pretty well documented, ActiveRecord
is not.  And the relationship between the two, such as it is, is
entirely baffling.  Maybe I''m just looking in the wrong places.)

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

[I just *gotta* stop answering my own posts! :)]

Okay -- if you set up your associations properly, then the ActiveRecord
query syntax does the right thing.  With one big flaw.

In the above Edge / Vertex example, declare associations as follows:

class Edge < ActiveRecord::Base
  belongs_to :vertex_a, :class_name => ''Vertex'',
:foreign_key =>
''vertex_a_id''
  belongs_to :vertex_b, :class_name => ''Vertex'',
:foreign_key =>
''vertex_b_id''
end

class Vertex < ActiveRecord::Base
  has_many :vertices
end

Now, a simple pair of joins gets us 90% of the way to our desired
results:
>> Edge.joins(:vertex_a).joins(:vertex_b).to_sql
=> "SELECT `edges`.*
      FROM `edges`
INNER JOIN `vertices`
        ON `vertices`.`id` = `edges`.`vertex_a_id`
INNER JOIN `vertices` `vertex_bs_edges`
        ON `vertex_bs_edges`.`id` = `edges`.`vertex_b_id`"

Note that AR has cleverly invented an alias for the second join
(''vertex_bs_edges''), and therein lies my gripe: how are you
supposed to
know what alias AR has invented for you?  Unless I"m mistaken, you need
to know the alias in order to write the SELECT statement.

Completing the Edge / Vertex example of the OP, you''d write the
following:

Edge.
  joins(:vertex_a).
  joins(:vertex_b).
  select(''vertices.x as x0'',
         ''vertices.y as y0'',
         ''vertices.z as z0'',
         ''vertex_bs_edges.x as x1'',
         ''vertex_bs_edges.y as y1'',
         ''vertex_bs_edges.z as z1'')

Glarg.  Maybe i''ll stick to find_by_sql for the time being.

Summary: The new query interface is pretty great, but the inability to
name your own table aliases seems like a blemish.

- ff

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.