Aggregate multiple columns from multiple tables into single column with Sequelize-CodePudding

I'm working on an MTG database, I'm trying to get the total value of all cards that a user owns I'm using Sequelize with Postgres. I have 4 tables with associations:

Users
__
userId

Cards
__
cardId

CardPrices
___
cardId
usd

UserCard
__
userId
cardId
quantity

I'm able to do a query and return all of the user's cards with associated prices but I'm having trouble figuring out how I can add these together as a single value and return it in the query. I'm not sure if this is possible.

const userInventory = await db.User.findAll({
      attributes: ['id', 'username'],
      include: {
        model: db.UserCard,
        attributes: ['id'],
        include: {
          model: db.Card,
          attributes: ['name'],
          include: {
            model: db.CardPrice,
            attributes: ['usd', 'eur'],
            as: 'prices'
          }
        }
      },
    });

returns:

[
    {
        "id": 1,
        "username": "tyler",
        "UserCards": [
            {
                "id": 1,
                "quantity": 2,
                "Card": {
                    "name": "Fury Sliver",
                    "prices": {
                        "usd": 0.42
                    }
                }
            },
            {
                "id": 2,
                "quantity": 1,
                "Card": {
                    "name": "Mystic Skyfish",
                    "prices": {
                        "usd": 0.07
                    }
                }
            }
        ]
    }
]

In this example, I'm looking to achieve an inventory_value of 0.91

CodePudding user response：

Sequelize is not intended to execute complex aggregation queries. You either need to use a plain SQL query or at least a SQL subquery inside User.findAll attributes option like this:

const userInventory = await db.User.findAll({
  attributes: ['id', 'username',
   [Sequelize.literal('(SELECT sum(UserCards.quantity*CardPrices.usd) from UserCards join CardPrices on (UserCards.cardId=CardPrices.cardId) where UserCards.userId=Users.id)'), 'inventory_value']],
...