也许我误解了Arel的整个概念,如果有人能看一下,我将不胜感激.
此查询的预期结果如下所示：
[{：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]))
解决方法
首先,这些脚本将创建测试表并使用测试数据填充它们.我已经设置了9个expense_account_users,每个都有不同的费用/ paid_debts,如下所示：1次充电/ 1次付款,2次充电/ 2次付款,2次充电/ 1次付款,2次充电/ 0次付款,1次充电/ 2次付款,0次充电/ 2次付款,1次充电/ 0次付款,0次充电/ 1次付款,0次充电,0次付款.
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,(3,(4,(5,(6,(7,(8,(9,1); CREATE TABLE IF NOT EXISTS `account_charges` ( `id` int(11) NOT NULL AUTO_INCREMENT,`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,2,2),3,4,5,7,1); CREATE TABLE IF NOT EXISTS `paid_debts` ( `id` int(11) NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=10 ; INSERT INTO `paid_debts` (`id`,6,8,1);
最终,为了获得您所追求的数据,这是您使用的sql语句：
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加入到债务中,以避免将结果与同一构造中的两个连接相乘.
(注意使用COALESCE将NULL值从LEFT OUTER JOIN转换为零 – 也许是一个便利项)
这句话的结果如下：
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
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加入到债务中.这个arel代码产生以下sql语句：
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函数调用.
更重要的是,将LEFT OUTER JOIN组合成一个没有内部子选择的语句会导致sum_paid总数在示例2,3和4中成倍增加 – 任何时候不止一次收费或付款且至少有一次另一个.
根据一些在线阅读材料,我曾希望稍微更改一下这个问题可以解决问题：
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#[].这可能是一个错误,或者可能是对的 – 我实在说不出来.
我只是没有看到arel有一种方法可以利用第一个构造中的sql作为第二个构造中的可查询对象以及必要的子查询别名,这是在一个sql语句中实现这一点所需要的.
