sequelize subquery as field

Question

I'm trying to get such query to be generated by sequlized:

SELECT 
    "Customers"."id", 
    (SELECT SUM("Orders"."amount") FROM "Orders"
     WHERE "Orders"."CustomerId" = "Customers"."id") AS "totalAmount",
    "Customer"."lastName" AS "Customer.lastName",
    "Customer"."firstName" AS "Customer.firstName" 
FROM "Customers" AS "Customer";

I'm trying to avoid GROUP BY clause, as I have a lot of fields to select and I don't want to group by all them (I think it's not efficient, isn't it?)

I've tried several ways for making it happen with sequelize, include {include: ...} and {attributes: [[...]]}, but without any luck.

Any ideas? or maybe should I use one big GROUP BY clause and let all the "regular" fields to be grouped-by?

Answer 1

Your best option is:

    return Customer.findAll({
        attributes: Object.keys(Customer.attributes).concat([
            [sequelize.literal('(SELECT SUM("Orders"."amount") FROM "Orders" WHERE "Orders"."CustomerId" = "Customer"."id")'), 'totalAmount']
        ])
    });

---

This looks like an extension to issue #1869:

Querying on the through model/join table is not possible currently unfortuneatly.

Your question is also tangentially related to this one, where the question there was sort of a "for each user query associated table".

Looking at the test code for the include clause I don't see any group options, which is an absence of evidence for this feature.

Solutions:

Of course, you could just pass the query "raw":

    return sequelize.query(
        'SELECT *, (SELECT SUM("Orders"."amount") FROM "Orders" WHERE "Orders"."CustomerId" = "Customer"."id") AS "totalAmount" FROM "Customers" AS "Customer";',
        Customer,
        {raw: false}
    );

That will give you want you want, and wrapped into Customer instances.

Alternatively, you could add an instance method that returns another promise:

instanceMethods: {
    getOrderSummary: function () {
        return Order.findAll({
            where: {
                CustomerId: this.id
            },
            attributes: [
                [sequelize.fn('SUM', sequelize.col('amount')), 'sum'],
                'CustomerId'],
            group: ['CustomerId']
        });
    }
}

The instance method version isn't very clean, but it works ok and may be more appropriate depending on your situation.

The best solution that I've found is to use a SQL literal in the attribute field of a query. The only downside is that it seems to wipe the slate clean on selecting the other attributes, and a '*' doesn't cut it. So, you need to do the workaround with Object.keys().

    return Customer.findAll({
        attributes: Object.keys(Customer.attributes).concat([
            [sequelize.literal('(SELECT SUM("Orders"."amount") FROM "Orders" WHERE "Orders"."CustomerId" = "Customer"."id")'), 'totalAmount']
        ])
    });

Still, it works a charm and you could use that for some more interesting nested SELECTs. And that findAll gives us the correct:

Executing (default): SELECT "id", "firstName", "lastName", "createdAt", "updatedAt", (SELECT SUM("Orders"."amount") FROM "Orders" WHERE "Orders"."CustomerId" = "Customer"."id") AS "totalAmount" FROM "Customers" AS "Customer";
{ id: 1,
  firstName: 'Test',
  lastName: 'Testerson',
  createdAt: Wed Feb 04 2015 08:05:42 GMT-0500 (EST),
  updatedAt: Wed Feb 04 2015 08:05:42 GMT-0500 (EST),
  totalAmount: 15 }
{ id: 2,
  firstName: 'Invisible',
  lastName: 'Hand',
  createdAt: Wed Feb 04 2015 08:05:42 GMT-0500 (EST),
  updatedAt: Wed Feb 04 2015 08:05:42 GMT-0500 (EST),
  totalAmount: 99 }

BTW, I also tried doing it backwards and using the GROUP BY on the Order model to select into the Customer model, but that didn't work:

    // Doesn't work
    return Order.findAll({
        attributes: [
            [Sequelize.fn('COUNT', '*'), 'orderCount'],
            'CustomerId'
        ],
        include: [
            {model: Customer, attributes: ['id']}
        ],
        group: ['CustomerId']
    });