mysql - 如何使用当前日期的 order by 进行查询

Question

我想加入 2 个表，并希望首先根据当前日期显示最新帖子，例如今天的发布帖子将显示在顶部，然后是 future 日期，然后将显示过去日期的帖子。我需要在 sequelize 中编写此查询。我收到未知列错误'postModel.DATE(published_at)' 我的 Sequelize 查询是这样的 -

postModel.findAndCountAll({
               include:[
                { model:userModel, 
                  where: { user_id:  user_id},
                  required:false
                  },
                ],
                order: [
                   [ 'DATE(published_at) = DATE(NOW())', 'DESC']
                ],
                limit: limit,
                offset: offset,
                      
        });

以下原始查询对我来说效果很好

SELECT * FROM posts as P JOIN user as U ON U.id = P.user_id
where
ORDER BY
    DATE(P.published_at)=DATE(NOW()) DESC,
    DATE(P.published_at)<DATE(NOW()) DESC,
    DATE(P.published_at)>DATE(NOW()) ASC`

Answer 1

删除您的日期函数并像这样查询。

postModel.findAndCountAll({
  include: [{ model: userModel, where: { user_id: user_id },required:false }],
  where: {
    published_at: {
      [Op.eq]: Date(NOW()),
    },
  },
  order: [["published_at", "DESC"]],
  limit: limit,
  offset: offset,
});