如何在Objection.js中统计并分组答案的SO式上下投票

模型关系前提 #

先确认你的Objection模型已正确定义关联：

• Answer模型：hasMany(Vote, { foreignKey: 'post_id' })
• Vote模型：belongsTo(VoteType)

方案1：使用查询构建器实现 #

直接用Objection的查询构建器复刻原生SQL逻辑，再转换为你要的嵌套格式：

const knex = Answer.knex(); // 获取实例

// 执行分组查询
const rawGroups = await Answer.query()
  .select(
    'answer_details',
    'vt.vote_type',
    knex.raw('COUNT(*) as vote_count')
  )
  .joinRelated('votes.vote_type') // 关联投票和投票类型
  .alias({
    votes: 'v',
    'votes.vote_type': 'vt'
  })
  .groupBy('answer_details', 'vt.vote_type');

// 转换为预期的嵌套格式
const formattedAnswers = rawGroups.reduce((acc, row) => {
  const targetAnswer = acc.find(item => item.answer_details === row.answer_details);
  if (targetAnswer) {
    targetAnswer.votes.push({ [row.vote_type]: row.vote_count });
  } else {
    acc.push({
      answer_details: row.answer_details,
      votes: [{ [row.vote_type]: row.vote_count }]
    });
  }
  return acc;
}, []);

// 最终结果
const output = { answers: formattedAnswers };

方案2：直接执行原生SQL #

如果更习惯原生SQL写法，可以用Objection的原生查询功能，再处理结果格式：

const rawResults = await Answer.knex().raw(`
  SELECT vt.vote_type, a.answer_details, COUNT(*) as vote_count
  FROM answers a
  JOIN votes v ON v.post_id = a.id
  JOIN vote_types vt ON vt.id = v.vote_type_id
  GROUP BY vt.vote_type, a.answer_details
`);

// 格式转换逻辑和方案1一致
const formattedAnswers = rawResults.rows.reduce((acc, row) => {
  const targetAnswer = acc.find(item => item.answer_details === row.answer_details);
  if (targetAnswer) {
    targetAnswer.votes.push({ [row.vote_type]: row.vote_count });
  } else {
    acc.push({
      answer_details: row.answer_details,
      votes: [{ [row.vote_type]: row.vote_count }]
    });
  }
  return acc;
}, []);

const output = { answers: formattedAnswers };

内容的提问来源于stack exchange，提问作者Roger Satsi Dankudi