sequelize.jssequelize-typescript

Building Sequelize JOIN table SQL queries with WHERE table alias


Suppose we could use QueryInterface provided by sequelize, how can we define the Table Alias in the Where clause?

            const queryInterface = this.sequelize.getQueryInterface();
            const queryGenerator = queryInterface.queryGenerator as SequelizeQueryGenerator;
            const whereObject = [
                {
                    ['venueid']: {
                        [Op.lt]: 50
                    }
                },
                {
                    ['e.eventid']: { // Attempt to define Table Alias on column
                        [Op.eq]: 100
                    }
                }
            ];
            const sql = queryGenerator.selectQuery(
                [
                    ['venue', 'v'],
                    ['event', 'e']
                ],
                { where: whereObject },
                null
            );

Actual output:

SELECT * FROM "venue" AS "v", "event" AS "e" WHERE ("v"."venueid" < 50 AND "v"."e.eventid" = 100);

Expected output:

SELECT * FROM "venue" AS "v", "event" AS "e" WHERE ("v"."venueid" < 50 AND "e"."eventid" = 100);

My attempts to make this work is by providing the Alias directly in the whereObject

               {
                    ['e.eventid']: { // Attempt to define Table Alias on column
                        [Op.eq]: 100
                    }
                }

The reason for not using a Model for query is that I'm trying to make this a model-less query service.


Solution

  • If you use the where function, it seems to work.

    const whereObject = sequelize.and(
      sequelize.where(Sequelize.col('v.venue_id'), Op.lt, 50),
      sequelize.where(Sequelize.col('e.event_id'), 100)
    )