join two tables to get data rails 4

Question

class Guardian < ActiveRecord::Base
  has_many :patients
  has_one :user, as: :profile
  accepts_nested_attributes_for :user
end


class User < ActiveRecord::Base
  belongs_to :profile, :polymorphic => true

  devise :database_authenticatable, :registerable,
         :recoverable, :rememberable, :trackable, :validatable
end

User migration

class DeviseCreateUsers < ActiveRecord::Migration
  def change
    create_table(:users) do |t|
      ## Database authenticatable
      t.string :email,              :null => false, :default => ""
      t.string :encrypted_password, :null => false, :default => ""
      t.string :username, :null => false
      t.string :address
      t.integer :age
      t.string :gender
      t.string :name
      t.integer :profile_id
      t.string :profile_type

      ## Recoverable
      t.string   :reset_password_token
      t.datetime :reset_password_sent_at

      ## Rememberable
      t.datetime :remember_created_at

      ## Trackable
      t.integer  :sign_in_count, :default => 0, :null => false
      t.datetime :current_sign_in_at
      t.datetime :last_sign_in_at
      t.string   :current_sign_in_ip
      t.string   :last_sign_in_ip


      t.timestamps
    end

    add_index :users, :email,                :unique => true
    add_index :users, :reset_password_token, :unique => true
  end
end

class DeviseCreateUsers < ActiveRecord::Migration
  def change
    create_table(:users) do |t|
      ## Database authenticatable
      t.string :email,              :null => false, :default => ""
      t.string :encrypted_password, :null => false, :default => ""
      t.string :username, :null => false
      t.string :address
      t.integer :age
      t.string :gender
      t.string :name
      t.integer :profile_id
      t.string :profile_type

      ## Recoverable
      t.string   :reset_password_token
      t.datetime :reset_password_sent_at

      ## Rememberable
      t.datetime :remember_created_at

      ## Trackable
      t.integer  :sign_in_count, :default => 0, :null => false
      t.datetime :current_sign_in_at
      t.datetime :last_sign_in_at
      t.string   :current_sign_in_ip
      t.string   :last_sign_in_ip
      t.timestamps
    end

    add_index :users, :email,                :unique => true
    add_index :users, :reset_password_token, :unique => true
    end
end

Guardian migration

class CreateGuardians < ActiveRecord::Migration
  def change
    create_table :guardians do |t|
      t.string :family_name

      t.timestamps
    end
  end
end

I want to get data from user table and guardian table in a single variable guardian has one user and user belongs_to guardian as profile(polymorphic). i want to get data from user table and from guardian table where guardian_id=users.profile_id

this is my first question sorry for the indentation. – Abdul Baig Oct 31 '13 at 06:42 — Abdul Baig, Oct 31 '13 at 06:42
Avoid adding commented code, it just adds noise. – Sully Oct 31 '13 at 06:45 — Sully, Oct 31 '13 at 06:45

Sully · Accepted Answer · 2013-11-03T06:32:33.637

16

Try

Guardian.select("*").joins(:user)

Edit:

if you have columns with the same name from the join you can do

Guardian.select("guardians.family_name, guardians.id as g_id, users.id as u_id,
    users.name, users.email, users.username, users.address, users.age,
    users.gender").joins(:user).where(:users => {:u_id => @user_session.id})

edited Nov 03 '13 at 06:32

answered Oct 31 '13 at 06:49

Sully

14,672
5
54
79

yeah that worked. but if i want some of the attributes of user and guardian.(only family_name from guardian and id,name from user)? – Abdul Baig Oct 31 '13 at 07:05
Guardian.select("guardians.*, users.id as user_id").joins(:user).having("user_id = (select id from users where id=guardians.id order by id desc limit 1)") this is returning null object. what is the mistake :( – Abdul Baig Oct 31 '13 at 07:08
`Guardian.select("guardians.family_name, users.id, users.name").joins(:user)` – Sully Oct 31 '13 at 07:11
But now i have two id's. user id and guardian id. how to name an id. this is my query Guardian.select("guardians.family_name, guardians.id, users.id, users.name, users.email, users.username, users.address, users.age, users.gender").joins(:user).where(:users => {:id => @user_session.id}) – Abdul Baig Oct 31 '13 at 10:44
@HithamS.AlQadheeb how do I specify an `OR` in the last `where` clause? Something like `where (:users => [{:u_id => @user_session.id}, {:name => "xyz"}])` ? – aandis Sep 02 '15 at 05:06
@zack, use ARel. Check http://stackoverflow.com/questions/3684311/rails-how-to-chain-scope-queries-with-or-instead-of-and – Sully Sep 02 '15 at 07:32

Sanjeev · Answer 2 · 2017-09-18T17:49:13.497

0

For some reason above answer didn't work for me, so I tried direct sql query

sqlQuery = "select tableA.column1, tableB.column2 from tableA inner join tableB on tableA.some_id = tableB.id"
ActiveRecord::Base.connection.execute(sqlQuery)

edited Sep 18 '17 at 17:49

answered Sep 18 '17 at 17:44

Sanjeev

4,255
3
28
37

join two tables to get data rails 4

2 Answers2

Linked