How to Have Two Columns in One Table Point to The Same Column in Another with Activerecord

How can I have two columns in one table point to the same column in another with ActiveRecord?

class Ticket < ActiveRecord::Base
  belongs_to :submitter, :class_name => "User"
  belongs_to :assignee, :class_name => "User"
end

Should work.

Edit: Without trying it out, I'm not sure whether you need the :foreign_key parameter or not. My instinct is not, but it couldn't hurt.

Edit again: Sorry, left off the User -> Ticket associations. You didn't mention using them, and I typically will only add associations in one direction if I don't plan on using them in the other direction.

Anyway, try:

class User < ActiveRecord::Base
  has_many :assigned_tickets, :class_name => "Ticket", :foreign_key => "assignee_id"
  has_many :submitted_tickets, :class_name => "Ticket", :foreign_key => "submitter_id"
end

Rails ActiveRecord two fields each referencing the same table

It looks you you are confusing SchemaStatement#add_reference and TableDefinition#references which have completely different signatures.

If you want to setup a foreign key column where the table can't be derived from the name of the column (the first argument) you just pass foreign_key: { to_table: :teams}.

class CreatePlayers < ActiveRecord::Migration[6.0]
  def change
    create_table :players do |t|
      t.references :high_school_team, foreign_key: { to_table: :teams}
      t.references :club_team, foreign_key: { to_table: :teams}
    end
  end
end

t.references :high_school_team, index: true as recommended by the other answers is NOT equivilent. That just adds a index to the column but no foreign key constraint.

You can then setup the assocations on Player as:

class Player < ApplicationRecord
  belongs_to :high_school_team, class_name: 'Team'
  belongs_to :club_team, class_name: 'Team'
end

You can't use a single has_many :players assocation on the other end though as the foreign key column can be either players.high_school_team_id or players.club_team_id.

class Team
  has_many :high_school_team_players, 
     foreign_key: :high_school_team_id,
     class_name: 'Player'
  has_many :club_team_players, 
     foreign_key: :club_team_id,
     class_name: 'Player'
end

But really a better alternative in the first place would have been to setup a join table:

class Player
  has_many :placements
  has_one :high_school_team, 
    through: :placements,
    source: :team
    class_name: 'Team'
  has_one :club_team, 
    through: :placements,
    source: :team
    class_name: 'Team'
end

class Placement
  belongs_to :player
  belongs_to :team
end

class Team
  has_many :placements
  has_many :players, through: :placements  
end

Defining two references to the same column in another table

A database table cannot have two columns with the same name. This is what you'll need in order to get this to work. (I'm going to use home_team and away_team to help distinguish them, but obviously you could use team1 and team2.)

rails g migration AddTeamsToMatch home_team_id:integer away_team_id:integer

This will generate a migration that looks like this:

class AddTeamsToMatch < ActiveRecord::Migration
  def change
    add_column :matches, :home_team_id, :integer
    add_column :matches, :away_team_id, :integer
  end
end

Then in your Team model, you can have the following:

class Team < ActiveRecord::Base
  has_many :home_matches, class_name: "Match", foreign_key: "home_team_id"
  has_many :away_matches, class_name: "Match", foreign_key: "away_team_id"

  # Get all matches
  def matches
    self.home_matches + self.away_matches
  end
end

In your Match model, you can have:

class Match < ActiveRecord::Base
  belongs_to :home_team, class_name: "Team"
  belongs_to :away_team, class_name: "Team"

  # List both teams as array
  def teams
    [self.home_team, self.away_team]
  end
end

Hopefully this helps.

Same Model with different columns Rails

You probably want to look at Single Table Inheritance

Try a Developer and Driver that both inherit from User and share one users database table. Each is effectively its own model, allowing you to define totally independent associations, callbacks, validations, instance methods, etc...

They share all of the same database columns, and anything defined in the User class will be inherited (and can be overwritten).

You will need to add a type column to users. All of the Developer and Driver columns fields should be defined for the users table as well.

class AddTypeColumnToUsers < ActiveRecord::Migration
  def change
    add_column :users, :type, :string
  end
end

And your models

class User < ApplicationRecord
end

class Driver < User
end

class Developer < User
end

Driver.new.type # => "Driver"
Developer.new.type # => "Developer"
User.new.type # => nil
User.new(type: 'Driver').class # => Driver
User.new(type: 'Developer').class # => Developer
User.new.class # => User

You can run separate queries for them just as you would any other model

Developer.all # queries all users WHERE type="Developer"
Driver.all # queries all users WHERE type="Driver"
User.all # queries all users no matter what type

Write your associations just as you would with any other model, and ActiveRecord will take care of everything else.

class Company < ApplicationRecord
  has_many :users
  has_many :developers
  has_many :drivers
end

class User < ApplicationRecord
  belongs_to :company
end

class Driver < User
  belongs_to :company
end

class Developer < User
  belongs_to :company
end

c = Company.first
c.developers # => All developers that belong to the Company
c.drivers # => All drivers that belong to the Company
c.users # => All users (including developers and drivers) that belong to the Company

You can also use enum for the type column, and override the default type column name if you wish

class AddTypeColumnToUsers < ActiveRecord::Migration
  def change
    add_column :users, :role, :integer
  end
end

class User < ApplicationRecord
  self.inheritance_column = :role # This overrides the the "type" column name default

  enum role: { Driver: 0, Developer: 1 }
end

class Driver < User
end

class Developer < User
end

The catch with using enum is you will have to use the capitalized name, and all of your enum helper methods and scopes will be capitalized as well.

User.Driver # => Scope that returns all drivers
Driver.all # => same as User.Driver
User.first.Driver? # => Is the user a Driver?

http://api.rubyonrails.org/classes/ActiveRecord/Inheritance.html

http://siawyoung.com/coding/ruby/rails/single-table-inheritance-in-rails-4.html

Creating new table - how to set up table with two columns linking to same column in another table?

In your ExchangeRate Model define association using foreign key

has_one :base_currency,   foreign_key: 'base_currency', class_name: 'Currency'
has_one :currency

now your can access both your currency and base currency like this

@exchange_rate.base_currency.name
@exchange_rate.currency.name

in Rails how to select additional columns from multiple tables

result = User.first.usertags.joins(:tag).select("tags.name as tag_name, tags.id, usertags.id, user.id")

result.collect{|r| r.tag_name}

How to get records from multiple condition from a same column through associated table

ca = Category.find_by_name('CA')
cb = Category.find_by_name('CB')
Book.where(:id => (ca.book_ids & cb.book_ids))  # & returns elements common to both arrays.

Otherwise you'd need to abuse the join table directly in SQL, group the results by book_id, count them, and only return rows where the count is at least equal to the number of categories... something like this (but I'm sure it's wrong so double check the syntax if you go this route. Also not sure it would be any faster than the above):

SELECT book_id, count(*) as c from books_categories where category_id IN (1,2) group by book_id having count(*) >= 2;

How to add two references column to create table migration?

You can change the Migration file as below, adding two integer columns player1 and player2

class CreateGames < ActiveRecord::Migration[5.0]
  def change
    create_table :games do |t|
      t.references :room, foreign_key: true
      t.boolean :p1turn
      t.string :secret
      t.string :wordsofar
      t.integer :wrongtries

      t.integer :player1
      t.integer :player2
    end
  end
end

The Game class can be like this,

this belongs_to two players referencing the User class.

class Game < ActiveRecord::Base
  belongs_to :player1, :class_name => "User"
  belongs_to :player2, :class_name => "User"
end

Now, the User class and the players relation.

class User < ActiveRecord::Base
  has_many :primary_players, :class_name => "Game", :foreign_key => "player1"
  has_many :secondary_players, :class_name => "Game", :foreign_key => "player2"
end

Here I referenced player1 as primary_players and player2 as secondary_players.

Now, you can call as,

User.first.primary_players

User.first.secondary_players

---

Related Topics