mysql - 是否可以将此拒绝转换为rails事件记录查询？

Question

现在我正在通过 deals.all 获取交易并执行以下拒绝语句，但是有没有一种方法可以让我一次性查询此交易？我有一些 ruby​​ 代码，例如 pluck 和 max，我只是想知道是否可以在 sql 代码中混合 ruby​​ 代码。

dealings.reject {|n|

(n.item_rank == :import && n.dealings.pluck(:status).max == Contract::import) || (n.item_rank == :process && n.dealings.pluck(:status).max == Contract::process) || (n.item_rank == :export && n.dealings.pluck(:status).max == Contract::export) }

end

Answer 1

我相信使用子查询可以实现这一点。这是一个原始的 SQL 示例:

negotiations = Negotiation.from('(select negotiations.*, (select max(status) from deal_buildings where deal_buildings.deal_id = negotiations.id) max_status from negotiations) negotiations')
negotiations = negotiations.where(business_rank: [17, 19, 22])
negotiations = negotiations.where(main_member_id: 7)
negotiations = negotiations.where(
  '((business_rank = ? and max_status = ?) or (business_rank = ? and max_status = ?) or (business_rank = ? and max_status = ?)) is false',
  'application', Supplier::Building::STATUS_APPLY,
  'agreement', Supplier::Building::STATUS_CONTRACT,
  'settlement', Supplier::Building::STATUS_SETTLEMENT
)

和query object :

class NegotiationsQuery
  def all
    Negotiation.from(negotiations_and_max_status_query)
  end

  private

  def negotiations
    @negotiations ||= Arel::Table.new(:negotiations)
  end

  def deal_buildings
    @deal_buildings ||= Arel::Table.new(:deal_buildings)
  end

  def max_status_query
    deal_buildings.project(deal_buildings[:status].maximum).where(
      deal_buildings[:deal_id].eq(negotiations[:id])
    ).as('max_status')
  end

  def negotiations_and_max_status_query
    negotiations.project(negotiations[Arel.star], max_status_query).as('negotiations')
  end
end

  negotiations = NegotiationsQuery.new.all
  negotiations = negotiations.where(business_rank: [17, 19, 22])
  negotiations = negotiations.where(main_member_id: 7)
  negotiations = negotiations.where(
    '((business_rank = ? and max_status = ?) or (business_rank = ? and max_status = ?) or (business_rank = ? and max_status = ?)) is false',
    'application', Supplier::Building::STATUS_APPLY,
    'agreement', Supplier::Building::STATUS_CONTRACT,
    'settlement', Supplier::Building::STATUS_SETTLEMENT
  )