How to do INNER JOIN on two tables without common columns using Sequelize in Nodejs?

user_14099755 0
Viewed 234

I have two tables.

Campaigns

id cause GoalType Target
1 5 0 5000
2 4 1 34500
3 5 1 50000
4 1 0 9000
5 8 1 35000
6 3 1 60000

Model defined as:

module.exports = (sequelize, Sequelize) => {
    const Campaigns = sequelize.define('Campaigns', {
        id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true },
        cause: Sequelize.INTEGER,
        goalType: Sequelize.INTEGER,
        target: Sequelize.FLOAT,
    }, {
        freezeTableName: true,
    });
    return Campaigns
}

Campaign_Tasks

id uuid action Status userId
1 001-0000004 2 0 50
2 001-0000004 1 4 60
3 001-0000003 2 7 50
4 001-0000007 0 10 40
5 001-0000010 1 3 30
6 001-0000003 0 5 50

Model defined as:

module.exports = (sequelize, Sequelize) => {
    const Campaign_Tasks = sequelize.define('Campaign_Tasks', {
        id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true },
        userId: Sequelize.INTEGER,
        uuid: Sequelize.STRING,
        action: Sequelize.INTEGER,
        status: Sequelize.INTEGER,
    }, {
        freezeTableName: true,
    });
    return Campaign_Tasks
}

Each UUID in the tasks table corresponds to the id in the campaign table(Each UUID is generated indirectly from id, and this process is reversible). But as can be seen, there are no common columns in both tables. I need to find Inner Join of these two tables based on id from campaign table and uuid from campaign_Tasks table (where uuid is not a PK of the campaign_Tasks table)

By following this tutorial: https://lorenstewart.me/2016/09/12/sequelize-table-associations-joins/ I created a db object. This was necessary because I couldn't define the associations in either of the model files as I kept getting the error mentioned here: hasMany called with something that's not an instance of Sequelize.Model

const db = require('./models/index')

const db_ = {};

db_.Sequelize = db.Sequelize;
db_.sequelize = db.sequelize;

//Models/tables
db_.campaigns = require('./models/campaigns')(db.sequelize, db.Sequelize);
db_.campaign_Tasks = require('./models/campaignTasks')(db.sequelize, db.Sequelize);

//Relations
db_.campaign_Tasks.belongsTo(db_.campaigns);
db_.campaigns.hasMany(db_.campaign_Tasks);

module.exports = db_;

How can I do Inner Join of both of these tables. I am looking for something like this:

const uuidToId = require('../../helpers/uuidToId')

let rows = await db_.campaign_Tasks.findAll({
        include: [{
            model: db_.campaigns,
            required: true,
            where: {
                id: uuidToId.uuidToId(/*<-uuid from campaign_Tasks rows->*/)
            }
        }]
    })

uuidToId converts UUID back to id. Thanks in advance.

sequelize.js node.js inner-join join javascript
1 Answers

There are three solutions that come to my mind:

  1. Run two separate queries where you first get all Campaign ID's, generate an appropriate uuid, use it in the second query, and merge the result in your code (my preferred way);
  2. Create a view in your database that will contain processed results and select against the view (note that if you're using computed columns, you won't be able to index them as MySQL doesn't support that as of right now);
  3. Use something called a cross-join. It's not a join per se, but it will compute a cartesian product of your two tables. https://www.mysqltutorial.org/mysql-cross-join/

I've never used a cross-join because I try to keep my queries as simple as possible since it allows me to cache intermediate results.

user_147935270
Related