Sequelize区分大小写查询：将指定MySQL语句转为Sequelize查询

Alright, let's convert your MySQL query to Sequelize step by step, making sure we replicate every detail—including the case-sensitive BINARY check, custom FIELD ordering, and filters. Here's how to do it, assuming you already have a Users model mapped to your users table:

const { Users } = require('./path-to-your-models');
const { Op, fn, col, where } = require('sequelize');

const queryResults = await Users.findAll({
  // Match SELECT `uid`, `username`
  attributes: ['uid', 'username'],
  // Match AS `users` table alias
  as: 'users',
  where: {
    // Match `users`.`status` = 'ACTIVE'
    status: 'ACTIVE',
    // Handle BINARY `users`.`username` IN ('hammad', 'sAad')
    [where(fn('BINARY', col('username')), {
      [Op.in]: ['hammad', 'sAad']
    })]: true
  },
  // Match ORDER BY FIELD(`username`, 'hammad', 'saad.ahmed')
  order: [
    [fn('FIELD', col('username'), 'hammad', 'saad.ahmed')]
  ],
  // Match LIMIT 20
  limit: 20
});

Breakdown of key parts: #

• Case-sensitive BINARY check: We use sequelize.fn('BINARY', ...) to wrap the username column, paired with Op.in to replicate the IN clause. This ensures the username match is case-sensitive, just like your original query.
• Custom ordering with FIELD: The order array uses sequelize.fn('FIELD', ...) to call MySQL's native FIELD() function, preserving the exact custom sort order you specified.
• Table alias: The as: 'users' option sets the table alias to match your original AS users clause.
• Field selection & limit: attributes specifies the exact columns to fetch, and limit:20 caps the result count.

If you prefer a more direct approach using raw SQL snippets (though the above is more ORM-compliant), you could also write the where clause like this:

where: {
  status: 'ACTIVE',
  username: {
    [Op.in]: fn('BINARY', "('hammad', 'sAad')")
  }
}

Either approach will produce the exact SQL logic you need.

内容的提问来源于stack exchange，提问作者Saad Ahmed