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"

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

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
    FROM edges
    JOIN vertices as a on = edges.vertex_a_id
    JOIN vertices as b on = 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.)

[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 =>
  belongs_to :vertex_b, :class_name => 'Vertex', :foreign_key =>

class Vertex < ActiveRecord::Base
  has_many :vertices

Now, a simple pair of joins gets us 90% of the way to our desired


=> "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

  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