Mongoid: Querying embedded (embeds_many) documents

Question

These are my classes:

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

For example, I have a Ticket with the following 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>]

I want to find tickets where the LAST transition has the following properties:

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

How can I modify the query to match only the last transition?

Answer 1

After repeatedly hitting head against the wall, I was able to use aggregation pipeline and filter documents the way I needed them:

      Ticket.collection.aggregate(
        [
          {
            "$project": {
              _id: 1,
              transitions: {
                "$slice": [
                  "$transitions",
                  -1
                ]
              }
            }
          },
          {
            "$match": {
              "transitions.created_at": {
                "$lte": Time.now.utc - 24.hours
              },
              "transitions.to": {
                "$in": states
              }
            }
          },
          {
            "$project": {
              _id: 1
            }
          }
        ]
      ).to_a

I added 3 stages:

• project docs with only the latest transition
• filter out docs where transition matches my conditions
• project docs with only id field

https://mongoplayground.net/p/UprsfaP0clE