CODEWITHSUNDEEP
mysqlsequelize.js
codeinprogress
codeinprogress

Reputation: 3501

Sequelize get sum of columns from association tables

I have two tables

CustomerAccount and CustomerAccountService

They are related in the following way:

CustomerAccount.hasMany(CustomerAccountService, {
    foreignKey: 'account_uuid'
});

CustomerAccountService.belongsTo(CustomerAccount, {
    as: 'account',
    foreignKey: 'account_uuid'
});

CustomerAccountService has two columns named invoice_amount and core_amount.

I want to get list of all CustomerAccount and within that list I want to show the sum of all invoice amounts and all core amounts for its children CustomerAccountService records.

This is the query I am trying:

CustomerAccount.findAll({
    attributes: [
        'uuid',
        'account_name',
        'account_number',
        'emergency_pay',
        [Sequelize.fn('SUM', Sequelize.col('CustomerAccountService.invoice_amount')), 'totalInvoiceAmount'],
        [Sequelize.fn('SUM', Sequelize.col('CustomerAccountService.core_amount')), 'totalCoreAmount']
    ],
    include: [
        {
            model: CustomerAccountService,
            attributes: []
        }
    ],
    group: ['CustomerAccount.uuid']
}).then(...);

However it throws an error saying

Unhandled rejection SequelizeDatabaseError: Unknown column 'CustomerAccountService.invoice_amount' in 'field list'

How do I get the sum of the two columns from the association table?

The model definition for CustomerAccountService is:

return sequelize.define(
        'customer_accounts_services', 
        {
            uuid: {
                type: type.STRING,
                primaryKey: true,
                autoIncrement: false
            },
            account_uuid: type.STRING,
            account_number: type.STRING,
            account_type: type.STRING,
            payment_type: type.STRING,
            service_type: type.STRING,
            date: type.DATEONLY,
            description: type.TEXT,
            invoice_amount: type.DECIMAL(10,2),
            core_amount: type.DECIMAL(10,2),
            paid: type.BOOLEAN
        },
        {
            timestamps: false,
            underscored: true,
            tableName: 'customer_accounts_services'
        }
    );

Upvotes: 2

Views: 8482

Answers (1)

doublesharp
doublesharp

Reputation: 27657

Your model definition set the table name to customer_accounts_services but you are passing in the Model name of CustomerAccountService to Sequelize.col(), which does not exist, so you are getting the error about the missing columns.

Update your query to use the correct table name in Sequelize.col().

[Sequelize.fn('SUM', Sequelize.col('customer_accounts_services.invoice_amount')), 'totalInvoiceAmount'],
[Sequelize.fn('SUM', Sequelize.col('customer_accounts_services.core_amount')), 'totalCoreAmount']

Upvotes: 2

Related Questions