ActiveRecord syntax for multiple joins on same table?

Rails
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