加入不工作正常

Question

我得到了下面的SQL，我试图将其转换成活动记录查询方法

我有以下表

Customers (id, name) 
CustomerPriceRelations (customer_id, sales_price_id) # jointable 
SalesPrices (id, margin) 
ProductSalesPrices (product_id, sales_price_id) # jointable 

，我试图执行下面的查询

SELECT sp.id, sp.margin_percentage 
        FROM sales_prices sp 
        LEFT OUTER JOIN carrier_product_Sales_Prices ps 
         ON sp.id = ps.sales_price_id 
        LEFT OUTER JOIN Customer_Price_Relations cr 
         ON ps.sales_price_id = cr.sales_price_id 
        LEFT OUTER JOIN Customers c 
         ON cr.customer_id = c.id 
        WHERE c.id = 2 AND ps.carrier_product_id = 3 

我试过使用连接方法，但结果从来没有加入由于某种原因，任何帮助吗？

SalesPrice.joins(:carrier_products, :customers).where(customer_id: 2, carrier_product_id: 3) 

Answer 1

连接（）的Rails标准行为是INNER JOIN，而不是LEFT OUTER JOIN。 INNER JOIN意思是，除非有匹配，否则连接的两边都不会出现。如果要使用LEFT OUTER JOIN，则需要明确定义它：

SalesPrice.joins("LEFT OUTER JOIN carrier_product_Sales_Prices ps ON sales_prices.id = ps.sales_price_id"). 
      joins("LEFT OUTER JOIN Customer_Price_Relations cr ON ps.sales_price_id = cr.sales_price_id"). 
      joins("LEFT OUTER JOIN Customers c ON cr.customer_id = c.id"). 
      where("cr.customer_id = ? and carrier_product_id = ?", 2, 3)