CODEWITHSUNDEEP
javascriptsequelize.js
NickW
NickW

Reputation: 1343

Sequelize query returning the wrong number of rows when models are included (findAndCountAll)

The following code should return 12 rows (req.query.limit = 12). This works fine if I don't include any associated rows, but when I include the related models it returns 7 rows.

I think it's down to me using the property subQuery: false which allows me (if I remember correctly) to order my results by column correctly - but it's been a long time since I've looked at the ordering code, I'm not very proficient with sequelize, and don't want to break it unnecessarily!

Would really appreciate it if someone can spot where I'm going wrong, and maybe why?

exports.getApplications = async(req, res, next) => {
    const index = req.query.index || 0;
    const limit = req.query.limit || 10;
    const order = req.query.orderDirection || 'DESC';
    let orderFields;

    switch(req.query.orderField) {
        case 'lastName': 
            orderFields = [
                ['applicant', 'person', 'lastName'], 
                ['applicant', 'person', 'firstName'], 
 
                ['applicant', 'createdAt', order]

            ];
            break;
        
        default:
            orderFields = [ 'createdAt' ];
        
    }

    try {
        const applications = await Application.findAndCountAll({
            include: [
                {                               
                    model: Job,
                    include: [ 
                        { 
                            model: Company,
                            attributes: [ 'id', 'name' ],
                            include: [
                                {
                                    model: Person,
                                    attributes: [ 'firstName', 'lastName', 'phone', 'email' ]
                                }
                            ]
                        } 
                    ]
                },
                {
                    model: Applicant,
                    include: [ 
                        {
                            model: Person,
                            attributes: [ 'id', 'firstName', 'lastName', 'email', 'phone', 'createdAt' ]
                        } 
                    ],
                }
            ],

            subQuery: false,

            order: orderFields,      
            limit: parseInt(limit, 10), 
            offset: parseInt(index),
            distinct: true,
            attributes: [ 
                'id',
                'applicantId',
                'jobId',
                [Sequelize.fn('date_format', Sequelize.col('application.createdAt' ), '%d/%m/%y'), 'applicationDate']
 
            ]
        });
        console.log(applications.rows.length); // 7  -- wrong
        console.log(limit);                    // 12 -- correct
        res.status(200).json({msg: 'success', applications: applications});

    } catch (err) {
        console.log(err);
    }

Associations:

Company.hasMany(Job, { foreignKey: { name: 'companyId', allowNull: false} });
Job.belongsTo(Company, { foreignKey: { name: 'companyId', allowNull: false} });

Person.hasOne(Applicant, { foreignKey: { name: 'personId', allowNull: false, unique: true } });
Applicant.belongsTo(Person, { foreignKey: { name: 'personId', allowNull: false, unique: true } });

Applicant.belongsToMany(Job, { through: Application });
Job.belongsToMany(Applicant, { through: Application });
// Set associations so the Application table can be queried directly
Application.belongsTo(Job, { foreignKey: { name: 'jobId' }});
Application.belongsTo(Applicant, { foreignKey: { name: 'applicantId' } });

Person.belongsToMany(Company, { through: Contact });
Company.belongsToMany(Person, { through: Contact });

Upvotes: 0

Views: 2247

Answers (1)

Anatoly
Anatoly

Reputation: 22803

In order to properly use findAndCountAll you need to get rid of many-to-many in include option OR convert it to one-to-many and use separate: true option.

associations

Company.hasMany(Contact, { foreginKey: 'companyId' });
Contact.belongsTo(Person, { foreginKey: 'personId' });

possible include option

{ 
  model: Company,
  attributes: [ 'id', 'name' ],
  include: [
    {
      model: Contact,
      separate: true,
      attributes: [],
      include: [{
        model: Person,
        attributes: [ 'firstName', 'lastName', 'phone', 'email' ]
      }]
    }
  ]
}

And yes, you will get an extra level to get person attributes through Contact.

Upvotes: 1

Related Questions