mysql - Rails 左连接并包含不存在的左表记录

Question

我试图通过rails AR查询接口(interface)实现“真正的”左连接，但不知道如何做到这一点，需要急切地加载...

我能够通过 AR 界面生成以下查询

scope :user_search, lambda {|user_id| includes(:trip_item).where("trip_items.user_id = ? OR trip_items.user_id IS NULL", user_id).order("trip_items.updated_at desc") }

apartments = Apartment.where(:location_id => near_by_locations.map(&:id).uniq)

    apartments = apartments.where.not(:id => exclude) unless exclude == nil

    apartments = apartments.where("apartments.updated_at >= :date", :date => 20.days.ago)
    apartments = apartments.where("apartments.price >= ?", search.min_price)
    apartments = apartments.where("apartments.price <= ?", search.max_price) unless search.max_price == nil
    apartments = apartments.where("apartments.rooms >= ?", search.min_rooms)
    apartments = apartments.where("apartments.rooms <= ?", search.max_rooms) unless search.max_rooms == nil


    apartments = apartments.order("apartments.updated_at DESC").user_search(user_id)

 SELECT * FROM "apartments" 
 LEFT OUTER JOIN "trip_items" ON "trip_items"."apartment_id" = "apartments"."id"
 WHERE "apartments"."location_id" IN (1, 8, 11, 13, 12, 7) AND ("apartments"."id" NOT IN (27, 8)) 
 AND (apartments.updated_at >= '2014-02-03 19:02:11.609227') AND (apartments.price >= 100) 
 AND (apartments.price <= 5000) AND (apartments.rooms >= 1.0) 
 AND (trip_items.user_id = 1 OR trip_items.user_id IS NULL) 
 ORDER BY apartments.updated_at DESC, trip_items.updated_at desc

但我需要以下内容来返回空的左表记录:

 SELECT * FROM "apartments" 
 LEFT OUTER JOIN "trip_items" ON "trip_items"."apartment_id" = "apartments"."id" AND trip_items.user_id = 1
  WHERE "apartments"."location_id" IN (1, 8, 11, 13, 12, 7) AND ("apartments"."id" NOT IN (27, 8)) 
 AND (apartments.updated_at >= '2014-02-03 19:02:11.609227') AND (apartments.price >= 100) 
 AND (apartments.price <= 5000) AND (apartments.rooms >= 1.0) 
 ORDER BY apartments.updated_at DESC, trip_items.updated_at desc

感谢帮助...

Answer 1

首先，为什么这一行出现两次？

apartments = apartments.where("apartments.rooms <= ?", search.max_rooms) unless search.max_rooms == nil

无论如何使用这个

 apartments = Apartment.where(:location_id => near_by_locations.map(&:id).uniq) 
 apartments = apartments.where.not(:id => exclude) unless exclude == nil
 apartments = apartments.where("apartments.updated_at >= :date", :date => 20.days.ago) 
 apartments = apartments.where("apartments.price >= ?", search.min_price) 
 apartments = apartments.where("apartments.price <= ?", search.max_price) unless search.max_price == nil
 apartments = apartments.where("apartments.rooms >= ?", search.min_rooms) 
 apartments = apartments.includes(:trip_items).where("trip_items.user_id = ?", user_id)
 apartments = apartments.order("apartments.updated_at DESC ,trip_items.updated_at desc")

这将生成以下内容

SELECT * FROM "apartments" 
WHERE "apartments"."location_id" IN (1, 8, 11, 13, 12, 7)
AND ("apartments"."id" NOT IN (27, 8)) 
AND (apartments.updated_at >= '2014-02-03 19:02:11.609227') 
AND (apartments.price >= 100) 
AND (apartments.price <= 5000) AND (apartments.rooms >= 1.0) 
LEFT OUTER JOIN "trip_items" ON "trip_items"."apartment_id" = "apartments"."id" AND trip_items.user_id = 1
ORDER BY apartments.updated_at DESC, trip_items.updated_at desc