ActiveRecord syntax for multiple joins on same table?

rubyonrails-talk
1

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.)

2

[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