I am new to isl, but after knocking on this subject for several days in a row and really digging, I don’t think it can be done. Here are the outlines of what I did, if someone has additional information, he will be happy.
First, these scripts will create test tables and populate them with test data. I set 9 counters_checks, each of which has a different set of fees / paid_detectors, namely: 1 payment / 1 payment, 2 payments / 2 payments, 2 payments / 1 payment, 2 payments / 0 payments, 1 payment / 2 payments, 0 payments / 2 payments, 1 payment / 0 payments, 0 fees / 1 payment, 0 fees, 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);
Ultimately, to get the data you use in one fell swoop, this is the SQL statement that you 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
First you have to make a LEFT INTERNAL CONNECTION between users and fees so that you get a row for each user, then you have to LEAVE EXTERNAL CONNECT the result to the debts in order to avoid multiplying your results with two unions inside the same construction.
(note the use of COALESCE to convert NULL values from LEFT OUTER JOINs to zero - perhaps a convenience item)
The result of this statement is the following:
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
After many attempts, I found that this isl code is closest to what we do 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'))
Essentially, I join users in the allegations of the first construct using the LEFT OUTER JOIN, and then try to get the result of this and the LEFT OUTER JOIN back to debt. This isl code creates 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`
What on startup produces this output:
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
Very close, but not quite. Firstly, the lack of COALESCE gives us NULLs instead of zeros - I'm not sure how to make a call to the COALESCE function from the inside.
More importantly, however, combining LEFT OUTER JOINs into a single statement without an internal subtitle results in the sum_paid sums that are multiplied in Examples 2, 3, and 4 — at any given time, more than one payment or payment and at least one other.
Based on some online readings, I was hoping that changing the language would solve the problem a bit:
charges = user_charges .join(d, Arel::Nodes::OuterJoin) .on(user_charges[:id].eq(d[:user_id])) .project(d[:cost].sum.as('sum_paid'))
But anytime when I used user_charges [] in the second isl construct, I got an undefined method error for SelectManager # [] . It may be a mistake, or it may be right - I really cannot say.
I just don’t see that isl has a way to use SQL from the first construct as a query object in the second construct with the necessary subquery alias, as it is necessary for this to happen in a single SQL statement.