Rails query join association table with alias

Question

I have a model Edge that belongs to the other model Node twice through different foreign keys:

def Edge < ActiveRecord::Base
    belongs_to :first, class_name: 'Node'
    belongs_to :second, class_name: 'Node'
end

And I want to perform this query using ActiveRecord:

SELECT * FROM edges INNER JOIN nodes as first ON first.id = edges.first_id WHERE first.value = 5

I found the way to join association using .joins() method:

Edge.joins(:first)

But this produces query using a table name, not an association name, so in .where() method I have to explicitly use table name which breaks association abstraction.

Edge.joins(:first).where(nodes: {value: 5})

I can also explicitly use SQL query in .joins() method to define model alias:

Edge.joins('INNER JOIN nodes as first ON nodes.id = edges.first_id')

But this breaks even more abstraction.

I think there should be the way to automatically define table alias on join. Or maybe a way to write such function by myself. Something like:

def Edge < ActiveRecord::Base
    ...
    def self.joins_alias
        # Generate something like 
        # joins("INNER JOIN #{relation.table} as #{relation.alias} ON #{relation.alias}.#{relation.primary_key} = #{table}.#{relation.foreign_key}")
    end
end

But I couldn't find any information about accessing information about specific relation like it's name, foreign key, etc. So how can I do it?

Also it seems strange to me that such obvious feature is so complicated even through Rails is on its 4th major version already. Maybe I'm missing something?

RPinel · Accepted Answer · 2015-04-01T16:45:31.063

11

As for Rails 4.2.1, I believe you just cannot provide an alias when using joins from ActiveRecord.

If you want to query edges by the first node, you could do it just like you stated:

Edge.joins(:first).where(nodes: {value: 1})
SELECT "edges".* FROM "edges" INNER JOIN "nodes" ON "nodes"."id" = "edges"."first_id" WHERE "nodes"."value" = 1

But if you have to query using both nodes, you can still use joins like this:

Edge.joins(:first, :second).where(nodes: {value: 1}, seconds_edges: {value: 2})
SELECT "edges".* FROM "edges" INNER JOIN "nodes" ON "nodes"."id" = "edges"."first_id" INNER JOIN "nodes" "seconds_edges" ON "seconds_edges"."id" = "edges"."second_id" WHERE "nodes"."value" = 1 AND "seconds_edges"."value" = 2

edited Apr 01 '15 at 16:45

answered Apr 01 '15 at 13:46

RPinel

866
6
15

Do you probably know, how to establish ordering based on one of this columns? – code1n Feb 16 '20 at 10:42
It's sad that active_record does not always (consistently) use the association name as an alias when joining. That would make the names in the ruby code match the association names and it will automatically resolve name clashes in a clear and unambiguious way when having multiple associations to same table name. – Jarl Oct 03 '22 at 08:29

score 1 · Answer 2 · answered Dec 21 '21 at 12:06

Of course you are able to use the table aliases (may be since rails 5) for the relation, some like the following:

def Edge < ActiveRecord::Base
    ...
    def self.joins_alias
        # Generate something like 
       join_name = table.table_alias || table.name
       table_to_join = ... # table name to join
       alias_to_join = ... # table alias to join
       joins("INNER JOIN #{table_to_join} as #{alias_to_join} ON #{alias_to_join}.external_id = #{join_name}.id")
    end
end

Rails query join association table with alias

2 Answers2

Linked