Node.js中Sequelize生成带COUNT的LEFT JOIN查询异常求助

Hey there, let's break down what's going wrong here and fix it step by step.

Your core issues are incorrect association configuration and using include the wrong way to implement subquery counting, which leads Sequelize to generate SQL that's totally off from your desired native query.

Step 1: Fix Model Associations #

Your belongsTo association was misconfigured, causing Sequelize to generate wrong JOIN conditions. Look at this problematic code:

expertisesEndorsementsModel.belongsTo(expertisesModel, { foreignKey: consts.uuidField, as: consts.modelAliases.EXPERTISE_ENDORSEMENTS });

The foreignKey should be the field in the expertise_endorsements table that links to expertises (which is consts.expertiseUuid), not the primary key of the main table. Also, the as alias should be distinct from the one used in hasMany (the main table uses EXPERTISE_ENDORSEMENTS for the collection of child records, while the child table uses EXPERTISE to reference the parent). Here's the corrected association code:

exports.setAssociations = (db, Sequelize, type) => {
  const expertisesModel = Expertises(db, Sequelize);
  const expertisesEndorseesModel = ExpertisesEndorsees(db, Sequelize);
  const expertisesEndorsementsModel = ExpertisesEndorsements(db, Sequelize);

  // Main table to child table: expertises has many expertise_endorsements
  expertisesModel.hasMany(expertisesEndorsementsModel, { 
    sourceKey: consts.uuidField, 
    foreignKey: consts.expertiseUuid, 
    as: consts.modelAliases.EXPERTISE_ENDORSEMENTS 
  });

  // Child table to main table: expertise_endorsements belongs to one expertise
  expertisesEndorsementsModel.belongsTo(expertisesModel, { 
    foreignKey: consts.expertiseUuid, // Foreign key in the child table
    targetKey: consts.uuidField, // Linked field in the main table
    as: consts.modelAliases.EXPERTISE // Alias for the parent in child table
  });

  return { expertisesModel, expertisesEndorseesModel, expertisesEndorsementsModel };
};

Step 2: Implement COUNT with Subquery (Instead of include) #

Your original approach tried to use include to join tables and count, but this causes data duplication (each linked endorsement creates a duplicate expertise record) and makes it hard to get a unique count per expertise. The right way is to add the subquery directly to the main model's attributes, matching your native SQL logic exactly:

Update your business logic code:

const models = dbUtils.setAssociations(db, consts.getExpertiseFlow);

// Define main model attributes, including the subquery count
const attributes = [
  [db.Sequelize.col('expertises.uuid'), 'id'],
  [db.Sequelize.col('expertises.display_name'), 'name'],
  [db.Sequelize.col('expertises.modified_by'), 'modifiedBy'],
  // Add the same subquery COUNT from your native SQL
  [
    db.Sequelize.literal(`(SELECT COUNT(expertise_uuid) FROM expertise_endorsements WHERE expertise_uuid = expertises.uuid)`),
    'users'
  ]
];

// No need for include anymore—subquery handles the counting
let {count: expertiseCount, rows: expertises} = JSON.parse(JSON.stringify( 
  await manageExpertise.getSortedExpertises(models, attributes, pageOffset, pageLimit, orderBy, [], false)
));

Step 3: Fix the getSortedExpertises Method #

You commented out the order configuration earlier, which breaks sorting. Restore it:

exports.getSortedExpertises = (models, attributes, offset, limit, orderBy, includes, raw=true) => {
  return models.expertisesModel
    .findAndCountAll({ 
      attributes, 
      include: includes, 
      offset: offset, 
      limit: limit, 
      order: orderBy, // Restore sorting
      raw 
    })
    .then(data => data)
    .catch(err => { throw err; });
};

Why Your Original Approach Failed #

1. Wrong Association: The belongsTo foreign key was set to the main table's primary key instead of the child table's foreign key, leading to invalid JOIN logic.
2. Misusing include: include is designed to fetch related records, not perform aggregate counts. Using it with COUNT requires grouping, which breaks findAndCountAll's total count calculation (it returns grouped counts instead of the total number of main table records).
3. Data Duplication: LEFT JOIN creates duplicate expertise records for each linked endorsement, making count results inaccurate and bloating the response.

Alternative: Using include + COUNT (Not Recommended) #

If you insist on using associations for counting, you'll need to add group and fetch the total count separately (since findAndCountAll's count gets distorted by grouping):

// Fetch total number of expertises first
const totalCount = await models.expertisesModel.count();

// Fetch the list with count
const expertises = await models.expertisesModel.findAll({
  attributes: [
    [db.Sequelize.col('expertises.uuid'), 'id'],
    [db.Sequelize.col('expertises.display_name'), 'name'],
    [db.Sequelize.col('expertises.modified_by'), 'modifiedBy'],
    [db.Sequelize.fn('COUNT', db.Sequelize.col(`${consts.modelAliases.EXPERTISE_ENDORSEMENTS}.${consts.expertiseUuid}`)), 'users']
  ],
  include: [{
    model: models.expertisesEndorsementsModel,
    attributes: [], // Don't return child table fields
    as: consts.modelAliases.EXPERTISE_ENDORSEMENTS,
    required: false
  }],
  group: ['expertises.uuid'], // Group by main table's unique ID
  offset: pageOffset,
  limit: pageLimit,
  order: orderBy,
  raw: true
});

// Manually combine total count and results
const result = { count: totalCount, rows: expertises };

This approach is less clean than the subquery method, especially if you need to add filters (you'll have to sync them to both the count and list queries). Stick with the subquery solution for simplicity.

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