[2.0.10] PHQL Join With Subquery

Yes. My example above could be rewritten without the SELECT in the JOIN. However, I am working on something that requires gathering a separate SUM from two separate tables.

For exmple, I have an invoice table, payments table and invoice_item table. I would like to get the invoices along with the sum of the payments and the sum of the invoice_item's prices.

The accepted answer here helped me come up with the query.

Since it's not supported in PHQL, my solution is just executing this as Raw SQL.

Imho i think it can be done justi in select, not sure why you need it in join.

I thought so too at first, but was not able to come up with anything. Here is a more accurate example of what I am trying to accomplish. Any suggestions on rewriting the query are welcome.

<?php
$db = new PDO(
    sprintf('mysql:host=%s;dbname=%s', '127.0.0.1', 'test'),
    'root',
    ''
);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = '
DROP TABLE IF EXISTS `invoices`;
CREATE TABLE `invoices` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO invoices (name) VALUES ("Invoice #1"), ("Invoice #2");
';
$db->exec($sql);

$sql = '
DROP TABLE IF EXISTS `items`;
CREATE TABLE `items` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `invoiceId` INT NOT NULL,
  `price` DOUBLE NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
);
INSERT INTO items (invoiceId, price) VALUES (1, 10), (1, 15), (1, 15), (2, 5), (2, 10)
';
$db->exec($sql);

$sql = '
DROP TABLE IF EXISTS `payments`;
CREATE TABLE `payments` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `invoiceId` INT NOT NULL,
  `amount` DOUBLE NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
);
INSERT INTO payments (invoiceId, amount) VALUES (1, 5), (1, 10), (2, 5)
';
$db->exec($sql);

$sql = '
SELECT invoices.name, paymentsum.payments, itemsum.subtotal
FROM invoices
LEFT JOIN (
   SELECT items.invoiceId, SUM(items.price) AS subtotal FROM items GROUP BY items.invoiceId
) itemsum ON itemsum.invoiceId = invoices.id
LEFT JOIN (
   SELECT payments.invoiceId, SUM(payments.amount) AS payments FROM payments GROUP BY payments.invoiceId
) paymentsum ON paymentsum.invoiceId = invoices.id
';

$records = $db->query($sql)->fetchAll();

foreach ($records as $record) {
    echo $record['name'] . ' - Item Total: ' . $record['subtotal'] . ' | Payments: ' . $record['payments'] . "\r\n";
}

Output:

Invoice #1 - Item Total: 40 | Payments: 15

Invoice #2 - Item Total: 15 | Payments: 5

You can do raw sql in phalcon.