搜索具有联接的关联属性

Question

我想从记录模型关联模型、关联模型属性中找到一条记录。

我有一个VendorOrder模型，如果可能的话，我想通过搜索vendor_order.order.shipments.each {|shipment| shipment.shipping_label.tracking_number}来找到一个VendorOrder(或vendor_orders，如果符合条件)。 所以搜索跟踪号。

模型：

VendorOrder
belongs_to :order
has_many :shipments
Order
has_many :vendor_orders
has_many :shipments
has_many :shipping_labels
Shipment
belongs_to :order
belongs_to :vendor_order
belongs_to :shipping_label
ShippingLabel
belongs_to :order
has_one :shipment

我在控制器中，

@vendor_orders = VendorOrder.joins(:order).merge(Order.where(order_status: "paid")).order(created_at: :desc).paginate(page: params[:vendor_page], per_page: 25)
if params[:search]
@vendor_orders = VendorOrder.search(params[:search])
else
@vendor_orders = VendorOrder.joins(:order).merge(Order.where(order_status: "paid")).order(created_at: :desc).paginate(page: params[:vendor_page], per_page: 25)
end

在VendorOrder模型中，

def self.search(search)
if search
find(:all, :conditions => ['shipments.map {|shipment| shipment.shipping_label.tracking_number} LIKE ?', "%#{search}%"])
else
find(:all)
end
end

视图：

<%= form_tag vendor_orders_path, :method => 'get' do %>
<p>
<%= text_field_tag :search, params[:search] %>
<%= submit_tag "Search", :name => nil %>
</p>
<% end %>

错误：

Couldn't find all VendorOrders with 'id': (all, {:conditions=>["shipments.map {|shipment| shipment.shipping_label.tracking_number} LIKE ?", "%99999999999999999999%"]}) (found 0 results, but was looking for 2).):

显然，我并不完全了解这是如何工作的。 到目前为止，我只制作了一个搜索表单，在一个模型中搜索一个字符串。

如何搜索多层模型，但确保供应商订单是要搜索的前端模型？

更新：

使用 jvillian post：

行为：

在模型中：

class << self
def find_by_tracking_number(tracking_number)
joins(:shipments).
where(shipments: {
id: Shipment.find_by(
shipping_label: ShippingLabel.where(tracking_number: tracking_number)
)
})
end
end

控制器：

@vendor_orders = VendorOrder.joins(:order).merge(Order.where(order_status: "paid")).order(created_at: :desc).paginate(page: params[:vendor_page], per_page: 25)
if params[:search]
@vendor_orders = VendorOrder.find_by_tracking_number(params[:search])
else
@vendor_orders =  VendorOrder.
joins(:order).
merge(Order.where(order_status: "paid")).
order(created_at: :desc).
paginate(page: params[:vendor_page], per_page: 25)
end

努力使事情启动并运行，但我遇到了没有结果的问题。

输入数字"99999999999999999999"应该获得大约 10 个结果，但出现 0

。提交时：

Parameters: {"utf8"=>"✓", "search"=>"99999999999999999999"}

它的魔力：

SELECT  "shipments".* FROM "shipments" WHERE "shipments"."shipping_label_id" IN (SELECT "shipping_labels"."id" FROM "shipping_labels" WHERE "shipping_labels"."tracking_number" = $1) LIMIT $2[0m  [["tracking_number", "99999999999999999999"], ["LIMIT", 1]]

但在那之后什么都没有，它只是渲染页面。

目前，大约有 10 个供应商订单shipments.map { |shpiment| shipment.shipping_label.tracking_number }== "99999999999999999999">

有什么原因为什么什么都没有出现？我的代码中缺少某些内容来附加所有结果？

Answer 1

试一试：

VendorOrder.
joins(:shipments).
where(shipments: {
id: Shipment.find_by(shipping_label: ShippingLabel.find_by(tracking_number: @tracking_number))
})

为了分解它，假设您在一个名为@tracking_number的变量中有您的跟踪号。这：

ShippingLabel.find_by(tracking_number: @tracking_number)

。会找到你的ShippingLabel.而这个：

Shipment.find_by(shipping_label: ShippingLabel.find_by(tracking_number: @tracking_number))

。会找到belongs_toShippingLabel的Shipment。

然后，按照指南的"在联接表上指定条件"部分进行操作，即可获得上面的最终查询。

您可以在控制器中使用它，如下所示：

if params[:search]
@vendor_orders =  VendorOrder.
joins(:shipments).
where(shipments: {
id: Shipment.find_by(
shipping_label: ShippingLabel.find_by(
tracking_number: params[:search]
)
)
})
else
@vendor_orders =  VendorOrder.
joins(:order).
merge(Order.where(order_status: "paid")).
order(created_at: :desc).
paginate(page: params[:vendor_page], per_page: 25)
end

或者，您可以将(其中一些)放入VendorOrder模型中，如下所示：

class VendorOrder < ApplicationRecord 
class << self 
def find_by_tracking_number(tracking_number)
joins(:shipments).
where(shipments: {
id: Shipment.find_by(
shipping_label: ShippingLabel.find_by(tracking_number: tracking_number)
)
})
end
end
end

然后你可以在控制器中使用它，如下所示：

if params[:search]
@vendor_orders = VendorOrder.find_by_tracking_number(params[:search])
else
@vendor_orders =  VendorOrder.
joins(:order).
merge(Order.where(order_status: "paid")).
order(created_at: :desc).
paginate(page: params[:vendor_page], per_page: 25)
end

IMO似乎更好一点，并且对控制器隐藏了一些复杂性。