带有任意created_at的Rails where方法

Question

我有一个这样的方法：

def transfers(material, capacity, category, created_at)
transfers_range = Transfer.first.created_at..Transfer.last.created_at if created_at.nil?
Transfer.where(
sender_dispensary_id: id,
status: %i[dispatched released returned],
capacity: capacity,
material: material,
created_at: created_at.nil? ? transfers_range : Time.given_month_range(created_at)
).or(
Transfer.where(
receiver_dispensary_id: id,
status: %i[dispatched released returned],
capacity: capacity,
material_id: material,
created_at: created_at.nil? ? transfers_range : Time.given_month_range(created_at)
)
)
end

它是有效的，但有没有办法避免查询transfer_range？我的意思是。。。如果是created_at == nil，那么这个函数应该跳过created_at列，就像它没有包含在查询中一样

Answer 1

当created_at是nil时，transfers_range基本上涵盖了所有传输，因此该条件毫无意义，在这种情况下，我只需通过created_at进行查询。

def transfers(material, capacity, category, created_at)
transfers = Transfer
.where(status: %i[dispatched released returned], capacity: capacity, material_id: material)
.where('sender_dispensary_id = :id OR receiver_dispensary_id = :id', id: id)
transfer = transfer.where(created_at: Time.given_month_range(created_at)) if created_at
transfer
end

Answer 2

是的，可以通过将查询分解为多行并单独添加created_at存在的条件来避免transfers_range查询。您还可以将两个OR查询合并为一个单独的查询，如下所示：

def transfers(material, capacity, category, created_at)
transfer_data = Transfer.where('sender_dispensary_id = ? OR receiver_dispensary_id = ?', id).where(status: %i[dispatched released returned], capacity: capacity, material_id: material)
transfer_data = transfer_data.where(created_at: Time.given_month_range(created_at)) if created_at.present?
transfer_data
end

Answer 3

您可以合并created_at查询逻辑，使其位于一个位置，而不必重复。

created_at = if created_at.nil?
Transfer.first.created_at..Transfer.last.created_at
else
Time.given_month_range(created_at)
end

你也不需要重复整个where条件两次。你想要相当于。。。

where status in ('dispatched', 'released', 'returned')
and capacity = ?
and material = ?
and created_at = ?
and (
sender_dispensary_id = ?
or
receiver_dispensary_id = ?
)

你这样做：

Transfer
.where(
status: %i[dispatched released returned],
capacity: capacity,
material: material,
created_at: created_at
)
.where(
Transfer
.where(receiver_dispensary_id: id)
.or(Transfer.where(sender_dispensary_id: id))
)
)

通过将逻辑放入范围中，可以使其更加简洁，并隐藏细节。

class Transfer < Application
scope :by_dispensary_id ->(id) {
where(receiver_dispensary_id: id)
.or(where(sender_dispensary_id: id))
}
scope :by_created_month ->(time) {
if time.nil?
where(created_at: first.created_at..last.created_at)
else
where(created_at: Time.given_month_range(time))
end
}
end

现在查询要简单得多。

Transfer
.by_dispensary_id(id)
.by_created_month(created_at)
.where(
status: %i[dispatched released returned],
capacity: capacity,
material: material
)
)