CODEWITHSUNDEEP
mysqlsequelize.js
Serdar D.
Serdar D.

Reputation: 3391

Sequelize Custom Field seperated from tables

I am trying to make an IN query but the field that I am querying is not an element of the table. It is hardcoded. 

await models.users.findOne({
  where: {
    id: 12,
    2: {$in: [1, 2, 3]}
  }
});

The problem is, sequelize puts the table name to begin of my field.

WHERE `users`.`2` IN (1, 2, 3)

As there is no field 2 in my table it throws "Unknown column 'users.2' in where clause"

Actually, this is what I need and works in native sql:

WHERE 2 IN (1, 2, 3)

UPDATE

To explain it better I am writing the whole SQL:

    SELECT count(`users`.`id`) AS `count`
    FROM `users` AS `users`
      INNER JOIN `payment_log` AS `succeed_payment`
        ON `users`.`id` = `succeed_payment`.`user_id` AND `succeed_payment`.`state` = 'success'
      LEFT OUTER JOIN `user_profile` AS `profile` ON `users`.`id` = `profile`.`user_id` AND `profile`.`state` = 'submitted'
    WHERE
      `users`.`country` = 'BLR'
      AND (`users`.`kyc_state` IN ('prepared', 'applicant_created', 'consider') OR `users`.`kyc_state` IS NULL)
    GROUP BY `users`.`id`
    HAVING
      1 IN ((SELECT tag_id
             FROM user_tags UT
             WHERE users.id = UT.user_id)) AND
      2 IN ((SELECT tag_id
             FROM user_tags UT
             WHERE users.id = UT.user_id));

1 and 2 come from the URL query. I am checking if the customer has those tags.

Unfortunately, sequelize adds backticks to my query and returns it to this:

  HAVING
    `1` IN ((SELECT tag_id
             FROM user_tags UT
             WHERE users.id = UT.user_id)) AND
    `2` IN ((SELECT tag_id
             FROM user_tags UT
             WHERE users.id = UT.user_id));

in that case I receive "unkown column 1" error

Upvotes: 1

Views: 260

Answers (1)

Vivek Doshi
Vivek Doshi

Reputation: 58543

You can achievie that by :

where : {
    '$2$' : {$in: [1, 2, 3]}
}

Upvotes: 1

Related Questions