如何修改Mongoid查询以匹配Ticket的最后一条Transition嵌入文档？

类定义 #

class Ticket
  include Mongoid::Document

  embeds_many :transitions
end

class Transition
  include Mongoid::Document

  embedded_in :ticket

  field :to, type: String
  field :created_at, type: DateTime
end

示例数据 #

某Ticket的transitions集合如下：

[#<Transition _id: 663b468deebb9d417d981152, to: "created", created_at: 2024-05-08 09:31:57.855 UTC>,
 #<Transition _id: 663b47cceebb9d417d981153, to: "solved", created_at: 2024-05-08 09:37:16.424 UTC>,
 #<Transition _id: 663b47ceeebb9d417d981154, to: "working", created_at: 2024-05-08 09:37:18.717 UTC>,
 #<Transition _id: 663b47d0eebb9d417d981155, to: "pending", created_at: 2024-05-08 09:37:20.989 UTC>]

我希望查询出最后一条transition满足以下条件的Ticket：
原查询语句：

Ticket.where(:'transitions.to' => "solved", :"transitions.created_at".lte => Time.now - 24.hours)

但这个查询会匹配所有包含任意一条符合条件的transition的Ticket，请问如何修改使其仅匹配最后一条transition满足条件的Ticket？

解决方案 #

要匹配最后一条transition满足条件的Ticket，需要利用MongoDB的聚合表达式定位数组最后一个元素，结合Mongoid查询语法可以这样写：

Ticket.where(
  :$expr => {
    :$and => [
      { :$eq => [{ :$last => "$transitions.to" }, "solved"] },
      { :$lte => [{ :$last => "$transitions.created_at" }, Time.now - 24.hours] }
    ]
  }
)

说明 #

• $last 操作符提取transitions数组最后一个元素的对应字段值，确保只检查最后一条transition
• $expr 允许在查询条件中使用聚合表达式，实现对数组最后一个元素的条件判断
• 两个条件通过$and组合，同时满足最后一条transition的to为solved、created_at早于24小时前的要求

如果你的MongoDB版本较低（不支持$last在查询中使用），可以改用$arrayElemAt结合$size获取最后一个元素：

Ticket.where(
  :$expr => {
    :$and => [
      { :$eq => [{ :$arrayElemAt => ["$transitions.to", { :$subtract => [{ :$size => "$transitions" }, 1] }] }, "solved"] },
      { :$lte => [{ :$arrayElemAt => ["$transitions.created_at", { :$subtract => [{ :$size => "$transitions" }, 1] }] }, Time.now - 24.hours] }
    ]
  }
)

补充优化建议 #

如果这类查询频率较高，建议在Ticket文档中新增latest_transition字段，同步存储最后一条transition的信息（to和created_at），查询时直接针对该字段判断，性能更优：

# 更新Ticket时同步维护latest_transition字段
ticket.latest_transition = ticket.transitions.last
ticket.save

# 查询语句简化为
Ticket.where(
  :"latest_transition.to" => "solved",
  :"latest_transition.created_at".lte => Time.now - 24.hours
)

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