CODEWITHSUNDEEP
ruby-on-railsjoinassociations
Andrew Starostin
Andrew Starostin

Reputation: 544

Rails query join association table with alias

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?

Upvotes: 26

Views: 16769

Answers (2)

Малъ Скрылевъ
Малъ Скрылевъ

Reputation: 16507

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

Upvotes: 3

RPinel
RPinel

Reputation: 896

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

Upvotes: 13

Related Questions