CODEWITHSUNDEEP
node.jspostgresqlsequelize.js
wusauarus
wusauarus

Reputation: 315

Having trouble adding a where clause to a join table using Sequelizer for NodeJS

I am executing the following where. The key logic that is causing me problems is in line #7 where I'm trying to specify a where condition on a join table. 

models.Portfolio.findAll({
        include: [{
            model: models.PortfolioPermissions,
        }],
        where: models.sequelize.or(
            {'userId': userId},
            {'PortfolioPermissions.userId': userId}
        ),
        order: [['startDate', 'DESC']]
    })

You can see the resulting query below has a major problem in line #9. Sequelize is prepending my where clause with portfolios table which is messing everything up.

SELECT `portfolios`.*,
       `sharedUsers`.`id` AS `sharedUsers.id`,
       `sharedUsers`.`permissions` AS `sharedUsers.permissions`,
       `sharedUsers`.`userId` AS `sharedUsers.userId`,
       `sharedUsers`.`portfolioId` AS `sharedUsers.portfolioId`
FROM `portfolios`
LEFT OUTER JOIN `portfolioPermissions` AS `sharedUsers` ON `portfolios`.`id` = `sharedUsers`.`portfolioId`
WHERE (`portfolios`.`userId`=1
       OR `portfolios`.`PortfolioPermissions.userId`=1)
ORDER BY `startDate` DESC;

If someone could please point me in the right direction here, I'd really appreciate it. Thank you so much. I'm using Postgres FYI, probably not relevant though.

Upvotes: 2

Views: 731

Answers (1)

Marco Righele
Marco Righele

Reputation: 2852

Condition on a joined table should be put in the relevant include entry, not on the global where condition. In your case something like the following should work:

models.Portfolio.findAll({
    include: [{
        model: models.PortfolioPermissions,
        where: {'userId': userId}

    }],
    where: models.sequelize.or(
        {'userId': userId}
    ),
    order: [['startDate', 'DESC']]
})

Upvotes: 1

Related Questions