Скажем, у меня есть 3 таблицы в приложении rails:
счета
id | customer_id | employee_id | notes
---------------------------------------------------------------
1 | 1 | 5 | An order with 2 items.
2 | 12 | 5 | An order with 1 item.
3 | 17 | 12 | An empty order.
4 | 17 | 12 | A brand new order.
invoice_items
id | invoice_id | price | name
---------------------------------------------------------
1 | 1 | 5.35 | widget
2 | 1 | 7.25 | thingy
3 | 2 | 1.25 | smaller thingy
4 | 2 | 1.25 | another smaller thingy
invoice_payments
id | invoice_id | amount | method | notes
---------------------------------------------------------
1 | 1 | 4.85 | credit card | Not enough
2 | 1 | 1.25 | credit card | Still not enough
3 | 2 | 1.25 | check | Paid in full
Это означает 4 порядка:
Первый имеет 2 элемента, в общей сложности 12,60. Он имеет два платежа за общую оплаченную сумму 6,10. Этот заказ частично оплачен.
Во втором есть только один элемент и один платеж, общий на 1,25. Этот заказ полностью оплачен.
В третьем порядке нет предметов или платежей. Это важно для нас, иногда мы используем этот случай. Это считается оплаченным в полном объеме.
В окончательном заказе есть еще один элемент, всего 1,25, но пока никаких платежей.
Теперь мне нужен запрос:
Покажите мне все заказы, которые еще не оплачены; то есть все заказы, такие, что общее количество предметов больше, чем общая сумма платежей.
Я могу сделать это в чистом sql:
SELECT invoices.*,
invoice_payment_amounts.amount_paid AS amount_paid,
invoice_item_amounts.total_amount AS total_amount
FROM invoices
LEFT JOIN (
SELECT invoices.id AS invoice_id,
COALESCE(SUM(invoice_payments.amount), 0) AS amount_paid
FROM invoices
LEFT JOIN invoice_payments
ON invoices.id = invoice_payments.invoice_id
GROUP BY invoices.id
) AS invoice_payment_amounts
ON invoices.id = invoice_payment_amounts.invoice_id
LEFT JOIN (
SELECT invoices.id AS invoice_id,
COALESCE(SUM(invoice_items.item_price), 0) AS total_amount
FROM invoices
LEFT JOIN invoice_items
ON invoices.id = invoice_items.invoice_id
GROUP BY invoices.id
) AS invoice_item_amounts
ON invoices.id = invoice_item_amounts.invoice_id
WHERE amount_paid < total_amount
Но... теперь мне нужно получить это в рельсы (возможно, как область). Я могу использовать find_by_sql, но затем возвращает массив, а не ActiveRecord:: Relation, который не то, что мне нужно, так как я хочу связать его с другими областями (есть, например, просроченная область, которая использует это ) и т.д.
Итак, необработанный SQL, вероятно, не правильный путь сюда... но что такое? Я не смог сделать это на языке запросов activerecord.
Ближайший я получил до сих пор:
Invoice.select('invoices.*, SUM(invoice_items.price) AS total, SUM(invoice_payments.amount) AS amount_paid').
joins(:invoice_payments, :invoice_items).
group('invoices.id').
where('amount_paid < total')
Но это терпит неудачу, так как при заказах, подобных # 1, с несколькими платежами, он неправильно удваивает цену заказа (из-за нескольких объединений), показывая, что он еще не оплачен. У меня была такая же проблема в SQL, поэтому я структурировал ее так, как я.
Любые мысли здесь?
