Sequelize: Querying a through table

Question

I'm trying to query a through table (Application) and paginate/order the results, but can't seem to get the logic quite right.

The Many-To-Many association:

// Applicants M:N Jobs (through Application)
Applicant.belongsToMany(Job, { through: Application });
Job.belongsToMany(Applicant, { through: Application });

I've queried Application, and then nested the queries for each side of the relation:

exports.getApplications = async (req, res, next) => {
    const index = req.query.index || 0;
    const limit = req.query.limit || 10;

    const applications = await Application.findAll({ limit: parseInt(limit, 10), index: parseInt(index)});
    let results = [];

    try {
        await Promise.all(applications.map(async (application) => {
            const job = await Job.findOne({ where: { id: application.jobId } });
            const applicant = await Applicant.findOne({ where: { id: application.applicantId } });

            results.push({application, job, applicant});
        }));

        res.status(200).json({msg: 'success', applications: results});

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

It seems to work, but feels a bit hacky. Is there a way of querying the through table and getting the associated data from both the Jobs and Applicant tables at the same time?

Thanks!

*EDIT: So I'm trying to return an array of application objects that look something like this:

[
    {
        applicationId: application.id,
        companyId: job.companyId,
        company: job.company.name,
        position: job.title,
        applicantId: applicant.id,
        firstName: applicant.firstName,
        lastName: applicant.lastName,
    }, 
    {...}, 
    {...}
]

...but I'd like to paginate the application results. So:

Application.findAll({ limit, index });

Ideally I'd also then like to be able to order by Job/Applicant properties too

---

More info:

So thanks to the help so far it looks like I need to also create a belongsTo association for the Application and Job/Applicant so that I can query the Association table and get Job/Applicant data:

// Applicants M:N Jobs (through Application)
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' }});

I currently create an application in one of my routes using applicant.addJob(currentJob);

// Applicant Model:

const Applicant = sequelize.define('applicant', {
    id: {
        type: Sequelize.INTEGER,
        autoIncrement: true,
        primaryKey: true,
        allowNull: false
    },
    cvUrl: {
        type: Sequelize.STRING,
        allowNull: true
    }
});

// Job Model:

const Job = sequelize.define('job', {
    id: {
        type: Sequelize.INTEGER,
        autoIncrement: true,
        allowNull: false,
        primaryKey: true
    },
    title: {
        type: Sequelize.STRING,
        allowNull: false
    },

    // **snip**

    createdAt: {
        type: Sequelize.DATE(3),
        allowNull: false,
    },
    updatedAt: {
        type: Sequelize.DATE(3),
        allowNull: false,
    }
});

// Application Model:

const Application = sequelize.define('application', {
    id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true,
        notNull: true
    }
});

Answer 1

With the combination of previous answers

Applicant.belongsToMany(Job, { through: Application ,as:'jobs'});
Job.belongsToMany(Applicant, { through: Application,as:'applicants' })

with these aliases, you can include both into the application

const applications = await Application.findAll({
include: [
    {model: Job, as:'jobs',  where: {id: application.jobId}},
    {model: Applicant, as:'applicants' where:{id: application.jobId}}
],
limit,
raw: true
});

also, you can set include as if applicant and job has an association

{model: Job, as:'jobs',  where: {id: application.jobId} 
   include:[{model:Applicant as:'applicants'}]
}

Answer 2

You can give aliases tou your associations and just get

Applicant.belongsToMany(Job, { through: Application ,as:'jobs'});
Job.belongsToMany(Applicant, { through: Application,as:'applicants' });

Then you'll be able to use a simple include for example Get the job and its applicants with one single query

 const job = await Job.findOne({
    where: { id: application.jobId },
    include: ['applicants'],
  });

in your job object you'll get an array of applicants .

More reference for that in here