sql - 让 ActiveRecord Query 与左外连接一起工作

Question

我不确定我在这里在语法上做错了什么，希望得到帮助。安InventoryItem有很多MaintenanceOrders。每个MaintenanceOrder都有一个布尔字段，即still_usable. 从逻辑上讲，我想对所有InventoryItems仍然可用的东西运行查询，这意味着它们要么根本没有，要么它们都MaintenanceOrders没有.MaintenanceOrdersstill_usable: false

快速Rspec

describe "test query" do
 before do
   InventoryItem.create(random_id:"a")
   InventoryItem.create(random_id:"b")
   InventoryItem.create(random_id:"c")

   InventoryItem.where(random_id:"a").last.maintenance_orders.create(still_usable:false)
   InventoryItem.where(random_id:"b").last.maintenance_orders.create(still_usable:nil)
   InventoryItem.where(random_id:"b").last.maintenance_orders.create(still_usable:true)


   @query = InventoryItem.left_outer_joins(:maintenance_orders).where.not(maintenance_orders:{still_rentable:false}).distinct
 end
 it "should return b & c" do
   expect(@query.map(&:random_id)).to match_array(["b","c"])
   # a has a maintenance order with flag still_usable:false so is excluded
   # b has maintenance orders, but none have flag still_usable:false, so is included
   # c has no maintenance orders, so is included
 end
end

标签： sqlruby-on-railsactiverecordrails-activerecord

Answer 1

您可以使用 SQL 来实现它NOT EXISTS

InventoryItem.where("NOT EXISTS (
   SELECT * FROM maintenance_orders 
   WHERE (maintenance_orders.inventory_item_id = inventory_items.id) 
   AND maintenance_orders.still_rentable = false)")