如何在Sequelize中将聚合函数结果单独返回而非每条结果包含

Got it, let's fix this for you. The issue with your current Sequelize query is that your GROUP BY clause is grouping by individual review IDs (and user IDs), so the AVG calculation is just returning the rating of each single review instead of the overall average for all matching reviews. That's why every review object has its own rating as the average_rating.

First, let's clarify what you actually need:

• A single average_rating value that's the average of all active reviews matching your where condition
• An array of those matching reviews, without the redundant average field on each entry

Here's the cleanest way to achieve this directly with Sequelize, using parallel queries to keep things efficient:

Solution Code #

const { sequelize } = require('../models'); // Adjust path to your sequelize instance
const STATUS_TYPE_ACTIVE = 'active'; // Replace with your actual value

// Define your shared where clause to avoid repetition
const reviewFilters = {
  id: req.params.id,
  status: STATUS_TYPE_ACTIVE
};

// Run both queries in parallel for better performance
const [reviews, averageRatingData] = await Promise.all([
  // Query 1: Get all matching reviews (without average)
  Review.findAll({
    where: reviewFilters,
    attributes: ['id', 'rating', 'body', 'created_at', 'updated_at'],
    include: [{
      model: User,
      attributes: ['id', 'photo', 'username']
    }]
  }),
  // Query 2: Calculate the overall average rating for matching reviews
  Review.findOne({
    where: reviewFilters,
    attributes: [
      [sequelize.fn('AVG', sequelize.col('rating')), 'average_rating']
    ],
    raw: true, // Returns plain object instead of Sequelize model instance
    plain: true // Ensures we get a single object instead of an array
  })
]);

// Combine results into the structure you want
const finalResult = {
  average_rating: averageRatingData?.average_rating || 0, // Fallback to 0 if no reviews exist
  reviews: reviews
};

Why This Works #

1. Parallel Queries: Using Promise.all runs both the review fetch and average calculation at the same time, which is more efficient than running them sequentially.
2. Correct Average Calculation: The second query removes the GROUP BY clause (since we want the average across all matching reviews, not grouped by individual entries). The raw: true and plain: true options simplify the result to a plain object with just the average_rating value.
3. Clean Result Structure: The final output has the average as a top-level field, plus your full reviews array—exactly what you requested.

Fixing Your Original PostgreSQL Query Note #

Your original SQL query:

SELECT avg(rating) as average_rating FROM reviews GROUP BY rating;

This doesn't give you the overall average—it groups reviews by their rating value, so you'll get a row for each distinct rating (with the average being the rating itself). To get the overall average, you should remove the GROUP BY clause entirely:

SELECT avg(rating) as average_rating FROM reviews WHERE id = ? AND status = 'active';

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