complicated query using sequelize

Question

i have 3 tables .. profile, player, garage

profile has winCount , loseCount

player has username

garage has carNumber

they are all connected with each other using the id from player table (playerId in table garage and profile)

i want to get the top 20 winCount , username , carnumber

i am trying this code

let racers = await Profile.findAll({
            where: {
                carRaceWinCount: {
                    [Op.gt]: 0
                }
            },
            limit: 20,
            order: [
                ['carRaceWinCount', 'DESC']
            ]
        })
            .then((races: any) => {
                Garage.findAll({
                    where: {
                        playerId: races.id
                    },
                    attributes : ['carNum']
                })
                Player.findAll({
                    where :{
                        id : races.id
                    },
                    attributes : ['username']
                })
            })

and it is not working what is the best way to get this query

Does this answer your question? [How to make join queries using Sequelize on Node.js](https://stackoverflow.com/questions/20460270/how-to-make-join-queries-using-sequelize-on-node-js) — Tobias S., Apr 04 '22 at 13:13

score 0 · Answer 1 · answered Apr 04 '22 at 18:38

0

If you have these associations:

Player.hasMany(Profile, { foreignKey: 'playerId' })
Player.hasMany(Garage, { foreignKey: 'playerId' })
Profile.belongsTo(Player, { foreignKey: 'playerId' })

then the query might look like this

await Profile.findAll({
            where: {
                winCount: {
                    [Op.gt]: 0
                }
            },
            limit: 20,
            include: [{
              model: Player,
              include: [{
                model: Garage,
                // this is important option to query all garames as separate queries, Otherwise you will get wrong count of profiles.
                separate: true
              }]
            }]
            order: [
                ['winCount', 'DESC']
            ]
        })

answered Apr 04 '22 at 18:38

Anatoly

20,799
3
28
42

ok this is almost working but the garage data in not fetched .... this is the response "garages": [] – user10596155 Apr 05 '22 at 13:33
Then check data in DB and asociations – Anatoly Apr 05 '22 at 16:27
no i am sure DB has rows and the associations are correct .. i tried the same query but without the " separate " and it fetched data but the order of players are different ... so why this is happening ? – user10596155 Apr 06 '22 at 04:11
`separate` is needed for correct fetching players because you limiting them by a certain count. Otherwise if you have 1 profile with 1 player with 10 garages and other profile with 1 player and 10 garages and all other profiles with 1 player and 1 garage you might get only 2 profiles instead of 20 because in SQL these two tables are multiplied. – Anatoly Apr 06 '22 at 18:15

complicated query using sequelize

1 Answers1