Rails SQL -将一个表中数量的总和与连接表中的值进行比较

Question

我正在尝试使用收到的项目数量总和与订单总数进行比较，以确保在存在部分订单的情况下订单已关闭。

我希望能够创建的范围，我将能够返回所有打开的订单，然后所有关闭的订单。

class JobquoteOrder < ActiveRecord::Base
  belongs_to :jobquote
  belongs_to :order
  has_many :po_receipts, dependent: :restrict_with_exception
  has_many :receipts, through: :po_receipts

  scope :receipts, -> {PoReceipt.uniq.pluck(:jobquote_order_id)}

  scope :summed_receipts, ->(jq_id) {PoReceipt
    .where(jobquote_order_id: jq_id)
    .sum(:qty)}

  scope :open_orders, -> {find(receipts)
    .where("jobquote_orders.qty >= #{summed_receipts(:id)}")}
end

在控制台中，JobquoteOrder.open_orders返回

JobquoteOrder Load (0.1ms)  SELECT "jobquote_orders".* FROM "jobquote_orders"  WHERE "jobquote_orders"."id" IN (1, 2, 3)
(0.1ms)  SELECT SUM("po_receipts"."qty") AS sum_id FROM "po_receipts"  WHERE "po_receipts"."jobquote_order_id" = 'id'
NoMethodError: undefined method `where' for #<Array:0x007ff9b5958088>

Answer 1

我想出这个来让它工作，但我仍然想知道它是否可以用纯SQL完成。任何关于重构的建议都将受到欢迎。

class JobquoteOrder < ActiveRecord::Base
  belongs_to :jobquote
  belongs_to :order
  has_many :po_receipts, dependent: :restrict_with_exception
  has_many :receipts, through: :po_receipts
  validates_presence_of :jobquote_id, :order_id, :qty, :total_cost
  validates_uniqueness_of :jobquote_id, scope: [:order_id, :qty, :total_cost], message: 'That order already exists.'

  scope :receipts, -> {PoReceipt.uniq.pluck(:jobquote_order_id)}
  scope :summed_receipt, ->(jq_id) {PoReceipt.where(jobquote_order_id: jq_id)
.sum(:qty)}

  scope :open_orders, -> {find(o_c_orders("open"))}

  scope :closed_orders, -> {find(o_c_orders("closed"))}

  def self.o_c_orders(which)
    ret_arr = []
    case which
    when "open"
      receipts.each do |id|
        ret_arr << id unless is_closed?(id)
      end
      ret_arr << self.includes(:po_receipts).where(:po_receipts => {:id => nil}).pluck(:id)
    when "closed"
      receipts.each do |id|
        ret_arr << id if is_closed?(id)
      end
    end
    return ret_arr
  end

  def self.is_closed?(jq_id)
    jq = self.find(jq_id)
    if jq.qty <= summed_receipt(jq_id)
      true
    else
      false
    end
  end
end