如何通过Sequelize在WHERE子句中使用数据库函数实现周温度统计

Got it, let's fix this query step by step. Your raw SQL is correct, and we just need to translate it properly into Sequelize syntax— the key issue here is how to compare two SQL function results in the WHERE clause, which requires using sequelize.where() instead of the standard object syntax.

Correct Sequelize Query #

Assuming you have imported your Temperature model and the sequelize instance, here's the working code:

const { Op } = require('sequelize');

Temperature.findAll({
  attributes: [
    // Get dayofweek from datetime field
    [sequelize.fn('dayofweek', sequelize.col('datetime')), 'day_of_week'],
    // Calculate average value
    [sequelize.fn('avg', sequelize.col('value')), 'average_temperature']
  ],
  where: sequelize.where(
    sequelize.fn('week', sequelize.col('datetime')),
    Op.eq,
    sequelize.fn('week', sequelize.fn('now'))
  ),
  group: [sequelize.fn('dayofweek', sequelize.col('datetime'))]
});

Breakdown of the Fix #

• Attributes Configuration:
  We use sequelize.fn() to wrap SQL functions, paired with sequelize.col() to reference table columns (this avoids hardcoding column names and keeps it model-safe). Alias the results with strings like 'day_of_week' so you can easily access them in the returned data.

• WHERE Clause Fix:
  Your original attempt tried to use the object syntax for where, but that only works for comparing columns to values, not function-to-function comparisons. sequelize.where() is designed for this exact scenario: it takes three arguments: the left-hand expression (your week(datetime)), the operator (Op.eq), and the right-hand expression (week(now())).

• Group By:
  Match the group clause to the dayofweek function used in attributes to ensure proper grouping.

Why Your Original Code Failed #

• You had the operator ([Sequelize.Op.eq]) as the key in the where object, which isn't the right syntax for function comparisons.
• The order of the function calls was reversed (you had week(now()) on the left and week(datetime) on the right, though that's a logic issue, not just syntax).
• Using Temperature.sequelize.fn() works, but it's cleaner to use the imported sequelize instance directly if you have it available.

This query will generate exactly the raw SQL you provided, so it should return the grouped average temperatures you need.

内容的提问来源于stack exchange，提问作者鸿则_