Arel 导致聚合无限循环 [英] Arel causing infinite loop on aggregation

问题描述

我在使用 Arel 聚合同一查询中的 2 列时遇到问题.当我运行它时，在 rails dev-server 崩溃之前，整个服务器会冻结一分钟.我怀疑是无限循环:).

I have trouble with using Arel to aggregate 2 columns in the same query. When I run this, the whole server freezes for a minute, before the rails dev-server crashes. I suspect an infinite loop :).

也许我误解了 Arel 的整个概念，如果有人能看一下，我将不胜感激.

Maybe I have misunderstood the whole concept of Arel, and I would be grateful if anybody could have a look at it.

这个查询的预期结果是这样的:[{:user_id => 1, :sum_account_charges => 300, :sum_paid_debts => 1000},...]

The expected result of this query is something like this: [{:user_id => 1, :sum_account_charges => 300, :sum_paid_debts => 1000},...]

a_account_charges = Table(:account_charges)
a_paid_debts = Table(:paid_debts)
a_participants = Table(:expense_accounts_users)

account_charge_sum = a_account_charges
  .where(a_account_charges[:expense_account_id].eq(id))
  .group(a_account_charges[:user_id])
  .project(a_account_charges[:user_id], a_account_charges[:cost].sum)

paid_debts_sum = a_paid_debts
 .where(a_paid_debts[:expense_account_id].eq(id))
 .group(a_paid_debts[:from_user_id])
 .project(a_paid_debts[:from_user_id], a_paid_debts[:cost].sum)

charges = a_participants
 .where(a_participants[:expense_account_id].eq(id))
 .join(account_charge_sum)
 .on(a_participants[:user_id].eq(account_charge_sum[:user_id]))
 .join(paid_debts_sum)
 .on(a_participants[:user_id].eq(paid_debts_sum[:from_user_id]))

推荐答案

我是 arel 的新手，但是在敲了几天并真正挖掘之后，我认为它无法完成.这是我所做工作的概述，如果有人有任何其他见解，我们将不胜感激.

I'm new to arel, but after banging on this for several days and really digging, I don't think it can be done. Here's an outline of what I've done, if anyone has any additional insight it would be welcome.

首先，这些脚本将创建测试表并用测试数据填充它们.我已经设置了 9 个费用账户用户，每个用户都有一组不同的费用/已付债务，如下:1 笔费用/1 笔付款，2 笔费用/2 笔付款，2 笔费用/1 笔付款，2 笔费用/0 笔付款，1 笔费用/2 笔付款，0 次费用/2 次付款，1 次费用/0 次付款，0 次费用/1 次付款，0 次费用，0 次付款.

First, these scripts will create the test tables and populate them with test data. I have set up 9 expense_account_users, each with a different set of charges/paid_debts, as follows: 1 charge/1 payment, 2 charges/2 payments, 2 charges/1 payment, 2 charges/0 payments, 1 charge/2 payments, 0 charges/2 payments, 1 charge/0 payments, 0 charges/1 payment, 0 charges, 0 payments.

CREATE TABLE IF NOT EXISTS `expense_accounts_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `expense_account_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=10 ;

INSERT INTO `expense_accounts_users` (`id`, `expense_account_id`) VALUES (1, 1), (2, 1), (3, 1), (4, 1), (5, 1), (6, 1), (7, 1), (8, 1), (9, 1);

CREATE TABLE IF NOT EXISTS `account_charges` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `expense_account_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `cost` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=10 ;

INSERT INTO `account_charges` (`id`, `expense_account_id`, `user_id`, `cost`) VALUES (1, 1, 1, 1), (2, 1, 2, 1), (3, 1, 2, 2), (4, 1, 3, 1), (5, 1, 3, 2), (6, 1, 4, 1), (7, 1, 5, 1), (8, 1, 5, 2), (9, 1, 7, 1);

CREATE TABLE IF NOT EXISTS `paid_debts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `expense_account_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `cost` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=10 ;

INSERT INTO `paid_debts` (`id`, `expense_account_id`, `user_id`, `cost`) VALUES (1, 1, 1, 1), (2, 1, 2, 1), (3, 1, 2, 2), (4, 1, 3, 1), (5, 1, 4, 1), (6, 1, 4, 2), (7, 1, 6, 1), (8, 1, 6, 2), (9, 1, 8, 1);

最终，为了一举获得您想要的数据，您可以使用以下 SQL 语句:

Ultimately, to get the data that you are after in one fell swoop, this is the SQL statement you'd use:

SELECT user_charges.user_id,
  user_charges.sum_cost,
  COALESCE(SUM(paid_debts.cost), 0) AS 'sum_paid'
FROM (
  SELECT expense_accounts_users.id AS 'user_id',
  COALESCE(sum(account_charges.cost), 0) AS 'sum_cost'
  FROM expense_accounts_users
  LEFT OUTER JOIN account_charges on expense_accounts_users.id = account_charges.user_id
  GROUP BY expense_accounts_users.id)
AS user_charges
LEFT OUTER JOIN paid_debts ON user_charges.user_id = paid_debts.user_id
GROUP BY user_charges.user_id

您必须先在用户之间进行 LEFT OUTER JOIN 并收费，以便为每个用户获得一行，然后您必须将 LEFT OUTER JOIN 结果 用于债务以避免成倍增加结果与同一构造内的两个连接.

You have to do a LEFT OUTER JOIN between users and charges first so that you get a row for every user, then you have to LEFT OUTER JOIN the result to debts in order to avoid multiplying your results with the two joins inside the same construct.

(注意使用 COALESCE 将 NULL 值从 LEFT OUTER JOIN 转换为零 - 也许是一个方便的项目)

(note the use of COALESCE to convert NULL values from the LEFT OUTER JOINs to zeroes - a convenience item, perhaps)

这个语句的结果是这样的:

The result of this statement is this:

user_id   sum_cost  sum_paid
1         1         1
2         3         3
3         3         1
4         1         3
5         3         0
6         0         3
7         1         0
8         0         1
9         0         0

经过多次尝试，我发现这个 arel 代码与我们所追求的最接近:

After many attempts, I found that this arel code came the closest to what we are after:

c = Arel::Table.new(:account_charges)
d = Arel::Table.new(:paid_debts)
p = Arel::Table.new(:expense_accounts_users)
user_charges = p
 .where(p[:expense_account_id].eq(1))
 .join(c, Arel::Nodes::OuterJoin)
 .on(p[:id].eq(c[:user_id]))
 .project(p[:id], c[:cost].sum.as('sum_cost'))
 .group(p[:id])
charges = user_charges
 .join(d, Arel::Nodes::OuterJoin)
 .on(p[:id].eq(d[:user_id]))
 .project(d[:cost].sum.as('sum_paid'))

从本质上讲，我加入用户在第一个构造中使用 LEFT OUTER JOIN 收费，然后尝试将结果和 LEFT OUTER JOIN 归还为债务.此 arel 代码生成以下 SQL 语句:

Essentially, I am joining users to charges in the first construct with a LEFT OUTER JOIN, then trying to take the result of that and LEFT OUTER JOIN it back to debts. This arel code produces the following SQL statement:

SELECT `expense_accounts_users`.`id`,
  SUM(`account_charges`.`cost`) AS sum_cost,
  SUM(`paid_debts`.`cost`) AS sum_paid
FROM `expense_accounts_users`
LEFT OUTER JOIN `account_charges` ON `expense_accounts_users`.`id` = `account_charges`.`user_id`
LEFT OUTER JOIN `paid_debts` ON `expense_accounts_users`.`id` = `paid_debts`.`user_id`
WHERE `expense_accounts_users`.`expense_account_id` = 1
GROUP BY `expense_accounts_users`.`id`

运行时产生以下输出:

id  sum_cost  sum_paid
1   1         1
2   6         6
3   3         2
4   2         3
5   3         NULL
6   NULL      3
7   1         NULL
8   NULL      1
9   NULL      NULL

非常接近，但不完全.首先，缺少 COALESCE 为我们提供了 NULL 值而不是零 - 我不确定如何从 arel 内部调用 COALESCE 函数.

Very close, but not quite. First, the lack of COALESCE gives us NULL values instead of zeroes - I'm not sure how to effect the COALESCE function call from within arel.

更重要的是，将 LEFT OUTER JOIN 合并到一个没有内部子选择的语句中会导致 sum_paid 总数在示例 2、3 和 4 中成倍增加 - 任何时候超过一个 收费或付款，至少其中之一.

More importantly, though, combining the LEFT OUTER JOINs into one statement without the internal subselect is resulting in the sum_paid totals getting multiplied up in examples 2, 3 and 4 - any time there is more than one of either charge or payment and at least one of the other.

基于一些在线阅读，我曾希望稍微改变 arel 可以解决问题:

Based on some online readings, I had hoped that changing the arel slightly would solve the problem:

charges = user_charges
 .join(d, Arel::Nodes::OuterJoin)
 .on(user_charges[:id].eq(d[:user_id]))
 .project(d[:cost].sum.as('sum_paid'))

但是每次我在第二个 arel 构造中使用 user_charges[] 时，都会遇到 SelectManager#[] 的未定义方法错误.这可能是一个错误，也可能是正确的 - 我真的不知道.

But any time I used user_charges[] in the second arel construct, I got an undefined method error for SelectManager#[]. This may be a bug, or may be right - I really can't tell.

我只是没有看到 arel 有办法使用第一个构造中的 SQL 作为第二个构造中的可查询对象，并带有必要的子查询别名，这是在一个 SQL 语句中实现这一点所需要的.

I just don't see that arel has a way to make use of the SQL from the first construct as a queryable object in the second construct with the necessary subquery alias, as is needed to make this happen in one SQL statement.

这篇关于Arel 导致聚合无限循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！