Associations and (multiple) foreign keys in rails (3.2) : how to describe them in the model, and write up migrations

Question

I have 3 models: Question, Option, Rule

Question has_many options; Option needs a foreign key for question_id

Rule table consists of 3 foreign_keys:

• 2 columns/references to question_ids -> foreign keys named as 'assumption_question_id' and 'consequent_question_id'
• 1 column/reference to option_id -> foreign key named as option_id or condition_id

Associations for Rule: Question has_many rules; and Option has_one rule

I want to understand how to write up migrations for this, and how that associates to the 'has_many'/'belongs_to' statements I write up in my model, and the ':foreign_key' option I can include in my model.

I had this for my Option migration, but I'm not sure how the "add_index" statement works in terms of foreign keys, and how I can use it for my Rule migration: (my Question and Options models have appropriate has_many and belongs_to statements - and work fine)

class CreateOptions < ActiveRecord::Migration
  def change
    create_table :options do |t|
      t.integer :question_id
      t.string :name
      t.integer :order

      t.timestamps
    end
    add_index :options, :question_id
  end
end

Thank you for the help!

Grettagreuze

Answer 1

add_index adds an index to column specified, nothing more.

Rails does not provide native support in migrations for managing foreign keys. Such functionality is included in gems like foreigner. Read the documentation that gem to learn how it's used.

As for the associations, just add the columns you mentioned in your Question to each table (the migration you provided looks fine; maybe it's missing a :rule_id?)

Then specify the associations in your models. To get you started

class Question < ActiveRecord::Base
  has_many :options
  has_many :assumption_rules, class_name: "Rule"
  has_many :consequent_rules, class_name: "Rule"
end

class Rule < ActiveRecord::Base
  belongs_to :option
  belongs_to :assumption_question, class_name: "Question", foreign_key: :assumption_question_id, inverse_of: :assumption_rules
  belongs_to :consequent_question, class_name: "Question", foreign_key: :consequent_question_id, inverse_of: :consequent_rules
end

class Option < ActiveRecord::Base
  belongs_to :question
  has_one    :rule
end

Note This is just a (untested) start; options may be missing.

I strongly recommend you read

• http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html
• http://guides.rubyonrails.org/association_basics.html

Edit: To answer the question in your comment

class Option < ActiveRecord::Base
  belongs_to :question
  # ...

The belongs_to tells rails that the question_id column in your options table stores an id value for a record in your questions table. Rails guesses the name of the column is question_id based on the :question symbol. You could instruct rails to look at a different column in the options table by specifying an option like foreign_key: :question_reference_identifier if that was the name of the column. (Note your Rule class in my code above uses the foreign_key option in this way).

Your migrations are nothing more than instructions which Rails will read and perform commands on your database based from. Your models' associations (has_many, belongs_to, etc...) inform Rails as to how you would like Active Record to work with your data, providing you with a clear and simple way to interact with your data. Models and migrations never interact with one another; they both independently interact with your database.

Kistner

Thank you for the help.. I had tried something similar to this, but was getting stuck trying to figure out what my rules table migration looks like... – Grettagreuze

(pressed enter too early) ... I was also reading up more about 'add_index', and read that it improves speed significantly. I'm trying to figure out how this fits into the Rules migration – Grettagreuze

Yes, my point is that the add_index method has nothing to do with the management of foreign keys. As you mentioned, there's plenty of resources for learning about the benefits of indexing columns, when (not) to do it, and other considerations. – Kistner

Ok. How do the statements I provide in the model relate to my migration? Do the column names have any significance in the context of the 'has_many' and 'belongs_to' statements? I already tried reading those two links, and am still missing a 'big-picture' understanding of what is going on – Grettagreuze

I tried answering your question, but you really should consider spending time on the articles I linked to - they explain it way better than I can (and even provide illustrations as to how the database columns relate to the associations) – Kistner

" Models and migrations never interact with one another; they both independently interact with your database." - How does rails know what column is referenced when the model specifies these relationships though? Can 'rules' have 3 random names? (by experimenting, I think the answer to that is no)... and if I name one of my Rules column 'assumption_question' or 'assumption_question_id', i get a fat SQL error thrown at me. – Grettagreuze

My Option table has a column called question_id. Since questions and options have a simple one-to-many relationship, just putting the has_many and belongs_to in my model is enough for rails to figure the rest out. Since Rules needs two question_ids, and I obviously can't name two columns with the same name, there is obviously some work around, which corresponds to the model-side solution you presented above – Grettagreuze

Right, in your migration you manually create 2 columns like :assumption_question_id and :consequent_question_id. You then reference these in your associations as I did above by specifying the non-standard column names for the foreign keys with the foreign_key option. – Kistner

yes.. "foreign_key: :assumption_question_id" needs to be included for both, the "has_many" and "belongs_to" statements, and the table columns should be named the same, as you said. thank you for your patience and help – Grettagreuze

Answer 2

Note: I have found this way to solve the problem.Kindness from China.

If you have RailsAdmin with you,you may notice that you can see all rules of one question as long as one field of both question fields(assumption_question_id,consequent_question_id) equals to id of the question.

I have done detailed test on this and found out that Rails always generates a condition "question_id = [current_id]" which make to_sql outputs

SELECT `rules`.* FROM `rules` WHERE `rules`.`question_id` = 170

And the reason that the following model

class Question < ActiveRecord::Base
  has_many :options
  # Notice ↓
  has_many :rules, ->(question) { where("assumption_question_id = ? OR consequent_question_id = ?", question.id, question.id) }, class_name: 'Rule'
  # Notice ↑
end

makes Question.take.rules.to_sql be like this

SELECT `rules`.* FROM `rules` WHERE `rules`.`question_id` = 170 AND (assumption_question_id = 170 OR consequent_question_id = 170)

Is that we have not yet get ride of the annoy question_id so no matter how we describe or condition properly, our condition follows that "AND".

Then,we need to get ride of it.How?

Click here and you will know how,Find sector 8.1,and you can see

Article.where(id: 10, trashed: false).unscope(where: :id)
# SELECT "articles".* FROM "articles" WHERE trashed = 0

Then lets do it:

class Question < ActiveRecord::Base
  has_many :options
  # Notice ↓
  has_many :rules, ->(question) { unscope(where: :question_id).where("assumption_question_id = ? OR consequent_question_id = ?", question.id, question.id) }, class_name: 'Rule'
  # Notice ↑
end

class Rule < ActiveRecord::Base
  belongs_to :option
  belongs_to :assumption_question, class_name: "Question", foreign_key: :assumption_question_id, inverse_of: :assumption_rules
  belongs_to :consequent_question, class_name: "Question", foreign_key: :consequent_question_id, inverse_of: :consequent_rules
end

class Option < ActiveRecord::Base
  belongs_to :question
  has_one    :rule
end

All done.

Finally

This is my first answer here at stackoverflow,and this method is never found anywhere else.

Thanks for reading.

Seoul

Answer 3

You can set a foreign key in your model like this:

class Leaf < ActiveRecord::Base
belongs_to :tree, :foreign_key => "leaf_code"
end

You do not need to specify this in a migration, rails will pull the foreign key from the model class definition.

Tarnopol