Rails 3 : Specify inner join criteria "ON" in the rails way

Question

I want to access the table "Words" from the "linked_to" column, and not from "id" column.

The INNER JOIN query created by Rails always search for the id column in the Words table.

This is what I want to do :

From the Class Point,
Do an inner join to "linked_to" column (I already specify the :foreign_key)
"Linked_to" is not unique in the "Words" table, because there is one line per translation
Filter (Select) in the Words table, the value corresponding to my current language (let's assume dictionnary = 1)

Table explanation for INNER JOIN

My current Model

class Word < ActiveRecord::Base
  has_and_belongs_to_many :points, :foreign_key => "linked_to"    
  belongs_to :dictionnary 
  attr_accessible :lang, :value, :dictionnary_id, :language_id, :linked_to
end

class Point < ActiveRecord::Base
    has_and_belongs_to_many :words, :association_foreign_key => "linked_to"     
    belongs_to :priority        
    attr_accessible :edited_by
end

In the Ruby console : irb(main):002:0> p.words ?[1m?[36mWord Load (0.0ms)?[0m ?[1mSELECT "words".* FROM "words" INNER JOIN "points_words" ON "words"."id" = "points_words"."linked_to" WHERE "points_words". "point_id" = 1?[0m --> but I want "ON words.linked_to = ..." — Stéphane V, Oct 11 '12 at 09:27

score 1 · Accepted Answer · edited Dec 19 '13 at 23:01

1

It's called convention. :) You have created the wrong associations, beginning with your design.

The has_and_belongs_to_many creates a relation between two models, thus using their primary key. Rethink your models: the dictionary_id field does not belong to Words, but is a additional field for the intermediate table. When you 'qualify' the intermediate table, you cannot use HASB anymore. Use a normal intermediate table

WordPointsForDictionary:
belongs_to :word
belongs_to :point
belongs_to :dictionary

Word:
has_many :points, :through => :WordPointsForDictionary
has_many :wordpointsfordictionary

Point:
has_many :words, :through => :WordPointsForDictionary
has_many :wordpointsfordictionary

This way you have removed the repetition and ugly relationships from you models. Clean primary key relationships and that will help you on the long way very much.

edited Dec 19 '13 at 23:01

iconoclast

21,213
15
102
138

answered Oct 11 '12 at 09:42

Hugo Logmans

2,202
1
19
14

Thank you. Is "though" = ":through" ? – Stéphane V Oct 11 '12 at 13:12
This solution seems awesome great :-) I'll work on it from now ! – Stéphane V Oct 11 '12 at 13:24
How can I have multiple word with the same id ? If I do like you propose, all words will have different id, and I can't change from one language to another for on especified Point. How do you select the words in another language from the Point class ? @Point.words.where("dictionnary = ?", 2) – Stéphane V Oct 11 '12 at 15:22
The "value" field belongs to the WordPointsForDictionary class. Prevent duplicity in the Words class. The value field means: for a given word, in translation x, the translation is ... – Hugo Logmans Oct 15 '12 at 08:04

Rails 3 : Specify inner join criteria "ON" in the rails way

1 Answers1