Aggregation Query in Symfony & MongoDB

Question

I'm working on a Symfony 6 project with MongoDB and facing an issue with an aggregation query.

My goal is to fetch Game documents filtered by season and a player name in the related GameLineUp collection.

Despite setting up the aggregation with $lookup, $match, $unwind, and $facet for pagination and count, the query returns 0 results.

The season is a field in Game, and the player is in GameLineUp. Here's the structure of my aggregation query:

• Match Game documents by season
• Lookup GameLineUp documents using _id and game
• Unwind the result
• Match by player in GameLineUp
• Apply pagination ($skip and $limit)
• Use $facet to combine results and count

I've double-checked field names and data types. Any insights or suggestions on what might be going wrong or how to debug this effectively would be greatly appreciated.

My code on GameRepository:

public function getPlayerGames(Player $player, Season $season, int $page = 1, int $limit = 30): Paginator
    {
        $aggregationBuilder = $this->createAggregationBuilder();
        $resultsPipeline    = clone $aggregationBuilder;
        $resultsPipeline
            ->match()
                ->field('season')->equals($season)
            ->lookup('lineUps')
            ->alias('lineUps')
            ->match()
            ->field('lineUps.player')->equals($player)
            ->sort(['dateTimeGame' => 'DESC'])
            ->skip(($page - 1) * $limit)
            ->limit($limit);

        // Count pipeline
        $countPipeline = clone $aggregationBuilder;
        $countPipeline
            ->match()
                ->field('season')->equals($season)
            ->lookup('lineUps')
            ->alias('lineUps')
            ->alias('lineUps')
            ->match()
                ->field('lineUps.player')->equals($player)
            ->group()
            ->field('_id')->expression(null)
            ->field('count')->sum(1);

        // Combine using facet
        $aggregationBuilder
            ->facet()
            ->field('results')
            ->pipeline($resultsPipeline)
            ->field('count')
            ->pipeline($countPipeline);

        return new Paginator(
            $aggregationBuilder->execute(),
            $this->dm->getUnitOfWork(),
            Game::class,
            $aggregationBuilder->getPipeline()
        );
    }