mysql - 主动记录查询以从三个关联模型中获取数据

Question

我是编程和 Rails 的新手。在我的 Rails 应用程序中，我有名为用户、房源、预订的模型。一个用户有_许多房源和预订。一个房源有_许多预订和预订属于一个房源和用户。

    class User < ActiveRecord::Base
     has_many :listings
     has_many :reservations
    end

    class Listing < ActiveRecord::Base
    belongs_to :user
    has_many :reservations
    end


    class Reservation < ActiveRecord::Base
    belongs_to :user
    belongs_to :listing
    end

我想要一个查询来获取所有包含以下详细信息的预订

 RESERVATIONS # | LISTING OWNER NAME  | LISTING OWNER EMAIL | LISTING OWNER PHONE | GUEST NAME | GUEST EMAIL | CLASS OF LISTING | YEAR OF LISTING | NIGHTLY RENTAL RATE OF LISTING| TOTAL RENTAL AMOUNT

架构:

create_table "users", force: :cascade do |t|
    t.string   "email",
    t.string   "fullname",   
    t.string   "phone_number"
    end  

 create_table "listings", force: :cascade do |t|
    t.string   "vehicle_type",   
    t.string   "listing_name" ,            
    t.string   "make",                         
    t.string   "model",                        
    t.string   "year", 
    t.integer  "daily_rent",
    t.integer  "user_id"   
end

create_table "reservations", force: :cascade do |t|
    t.integer  "user_id",  
     t.integer  "total",
     t.integer  "listing_id"
 end

作为列表所有者和访客，两者都是用户我只能用一个简单的 SQL 查询来获取所有预订详细信息以及客人的电子邮件和电话以及相应预订的详细信息

  select r.id , u.email, u.fullname ,l.listing_name,l.year,l.daily_rent,r.total  from reservations r , users u ,listings l where u.id = r.user_id and l.id = r.listing_id;

1) 我无法提取列表所有者姓名 |所有者邮箱 | OWNER PHONE 但我能够得到客人姓名 | guest 电子邮件 | guest 电话。如何获取 LISTING OWNER 详细信息？？

2) 如何在 rails Active Record 中编写上述 SQL 查询，以便我可以在模型或 Controller 中使用它来获取结果？？

非常感谢任何帮助。提前致谢!

Answer 1

您可以定义自定义外键和名称，以便以更简洁的方式检索记录:

class User < ActiveRecord::Base
  has_many :listings, foreign_key: :owner_id
  has_many :reservations, foreign_key: :guest_id
end

class Listing < ActiveRecord::Base
  belongs_to :owner, class_name: 'User'
  has_many   :reservations
end

class Reservation < ActiveRecord::Base
  belongs_to :guest, class_name: 'User'
  belongs_to :listing
end

通过重新定义您的模型，现在您可以像这样轻松地查询它们:

reservation = Reservation.find_by(id: 1)
owner = reservation.listing.owner
guest = reservation.guest
total = reservation.total

# 'owner' and 'guest' are User instances. So you can invoke any fields on them that are defined in the model
owner.email
owner.fullname
owner.phone_number

顺便说一句，要支持上述定义，您必须运行以下迁移:

class AddReferencesInListingsAndReservations < ActiveRecord::Migration
  def change
    remove_column :listings,     :user_id,    :integer
    remove_column :reservations, :user_id,    :integer
    remove_column :reservations, :listing_id, :integer

    add_reference :listings,     :owner, foreign_key: { to_table: :users }
    add_reference :reservations, :guest, foreign_key: { to_table: :users }
    add_reference :reservations, :listing
  end
end

这将导致以下架构:

create_table "listings", force: :cascade do |t|
  t.string   "vehicle_type",   
  t.string   "listing_name" ,            
  t.string   "make",                         
  t.string   "model",                        
  t.string   "year", 
  t.integer  "daily_rent",
  t.integer  "owner_id"
  t.index    "owner_id",   name: "index_listings_on_owner_id"
end

create_table "reservations", force: :cascade do |t|
  t.integer  "guest_id",  
  t.integer  "total",
  t.integer  "listing_id"
  t.index    "guest_id",   name: "index_listings_on_guest_id"
  t.index    "listing_id", name: "index_listings_on_listing_id"
end

add_foreign_key "listings",     "users", column: "owner_id"
add_foreign_key "reservations", "users", column: "guest_id"
add_foreign_key "reservations", "listings"