Can I generate a migration with 2 fields that will reference on the same table?

Question

I want to add to the table Orders two fields: seller_id and buyer_id that will reference to the Users Table.
I am not sure what is the best way to do this?

1. rails g migration AddUsersToOrder seller_id:integer buyer:integer
   I can run this command, but as I see it this is not a good solution, because this command doesn't 'connect' the two tables, it just creates two integer fields without a FOREIGN KEY constraint. In other words the reference wouldn't be created, but definitely will work.

There is a pretty good article (but quite old from 2008) with the same example but it doesn't generate migration and it focuses on Model. Also @Joe Kennedy wrote a very good solution for a similar question. But as I said, I think this will not create the reference.

2. rails g migration AddUserRefToOrder seller_id:references
   This is the best way to do it, but the problem is that we can't create another migration for buyer this way.

I am wondering what should I do am I missing something?

Answer 1

Depends on how you want to interact with the things you can choose from 2 versions.

For tasks it was important to see who was the assigner/executor so I was able to sort the different (incoming/outgoing) tasks. So I could call user.assigned_tasks or user.executed_tasks.

For conversations I was not interested in interacting with that model. It does not matter who created it. I don't need conversation.sender or conversation.recipient. I need the messages (nested under conversation) to be identified based on the sender/recipient.

Version A

class Task < ActiveRecord::Base
  belongs_to :assigner, class_name: "User"
  belongs_to :executor, class_name: "User"
end

class User < ActiveRecord::Base
  has_many :assigned_tasks, class_name: "Task", foreign_key: "assigner_id", dependent: :destroy
  has_many :executed_tasks, class_name: "Task", foreign_key: "executor_id", dependent: :destroy
end

create_table "tasks", force: :cascade do |t|
   t.integer  "assigner_id"
   t.integer  "executor_id"
end
add_index "tasks", ["assigner_id"], name: "index_tasks_on_assigner_id", using: :btree
add_index "tasks", ["executor_id"], name: "index_tasks_on_executor_id", using: :btree

Version B

class User < ActiveRecord::Base
  has_many :conversations, foreign_key: "sender_id", dependent: :destroy
end

class Conversation < ActiveRecord::Base
  belongs_to :sender, class_name: "User", foreign_key: "sender_id"
  belongs_to :recipient, class_name: "User", foreign_key: "recipient_id"
end

create_table "conversations", force: :cascade do |t|
  t.integer  "sender_id"
  t.integer  "recipient_id"
end
add_index "conversations", ["recipient_id"], name: "index_conversations_on_recipient_id", using: :btree
add_index "conversations", ["sender_id"], name: "index_conversations_on_sender_id", using: :btree

UPDATE:

If you run rails g model User or rails g model Order, migration will also be generated along test files, model file etc. This is the preferred way if the table does not exist yet. If table already exists and you wanna change it then you create only the migration which can be done 2 ways. The first is what you are talking about where u also pass the arguments, so when u open up the migration file the columns will already be there. But that is the hard way. You can just simply run rails g migration AddSomethingToThis. Here the migration name does not matter, you should just choose something descriptive so you can easily recognize the migration file later on. Then you open up the migration file and you put there the columns you want. In my code u see the necessary foreign keys. Besides that you can create price column etc., what you business logic needs. Then just run rake db:migrate, which will change your db schema. You can always check out your current schema in schema.rb. Never change the schema manually only by migrations.

Summing up:

1. Create migration (with model if there is no table yet)
2. Edit the migration file
3. Run rake db:migrate so schema will be updated.

Update 2:

You can generate indexes easily by adding t.integer "recipient_id", index :true in the migration file and then the the line that you highlighted will appear in schema.rb (where I copied that part of my code from) after running rake db:migrate . Check out some migration examples.

So indexes are about performance. With indexes the search in the db is faster in most cases. It's something like bookmark in a book. Usually you add it to foreign keys since those are the bridges between 2 models. If there are some attributes in a model which is queried heavily then you can add it to those as well. Don't add to too many lines. You can imagine what happens when you add let's say 20 bookmarks to a book.