Complex Aggregation Pipelines with Vanilla PHP and MongoDB

Viraj Thakrar10 min read • Published Sep 05, 2024 • Updated Sep 05, 2024

PHP MongoDB

SNIPPET

Rate this quickstart

In the world of data, processing large volumes is both a challenge and a necessity. Whether it's analysing sales data to identify trends, summarising user activities to provide personalised experience, or transforming raw data into meaningful insights, the ability to process and aggregate data is crucial. Let's understand this with an example.

Imagine you've got a movie dataset, a collection that includes extensive information about movies, user reviews, and ratings. To improve user experience and engagements, you might want to identify the most popular genres, understand the average ratings of the movies, determine reviews to comment ratio, and so on.

To get these insights, writing queries for individual metrics would be inefficient and complex. Also, processing large datasets on-the-go would be a compute-intensive task. This is where aggregation becomes crucial.

In the next section, let's understand what aggregation is, how it is achieved in MongoDB, and what pipelines and stages are from a theoretical standpoint, and then we will dive into some hands-on exercises with PHP drivers.

What is aggregation?

Aggregation is a process of transforming and summarising large sets of data to extract meaningful insights. It involves performing operations like counting, grouping, and averaging to consolidate data into an insightful format. It makes data easier to analyse and understand.

Aggregation in MongoDB is made up of pipelines, which are sequences of stages that process data step-by-step. Think of how an item would move through an assembly line.

Pipeline: Imagine water flowing through a series of pipes. Similarly, data flows through a series of stages. Each stage performs an operation on the data and passes the transformed data to the next stage. This sequential processing allows for efficient and modular data transformations.
Stages: These are individual steps within the pipeline, each designed to perform a specific task. Common examples are $match, $group, $project, $sort, and $limit.

(Illustration of how data would flow through the pipeline consisting of different stages)

In the next steps, we will set up a free Atlas cluster and load a sample Mflix movie dataset provided by MongoDB. Skip the next section if you already know how to or already have a free tier cluster ready with Mflix database loaded.

Prepare a free tier cluster with sample dataset

Sign up for MongoDB Atlas if you haven't already, and create a free tier M0 cluster. If you're not sure how to do it, follow the step-by-step tutorial to deploy a free cluster.
Load the sample dataset provided by MongoDB called Mflix, which we will use throughout this tutorial. If you need guidance, learn how to load data into Atlas.
Check that you're able to connect to your cluster. You should be able to see the sample dataset in Compass. Having trouble? Learn how to connect via Compass.

Prepare your own development environment

Before we start writing aggregation pipelines, let's prepare our own development environment and make sure we have everything ready to go for writing our own pipelines.

Make sure your machine is running with at least PHP 8.1, and you have MongoDB PHP library 1.17 or plus and MongoDB PHP extension 1.16 or plus installed. Follow our guide to install the MongoDB PHP library, if needed.

Connect to your Atlas cluster using PHP

At this point, you should have your drivers, database cluster with sample dataset loaded, and everything required for executing a PHP script.

Code Snippet

This code uses the ping admin command to check whether your machine can connect to the cluster using your PHP code and the MongoDB PHP driver. We will use PHP CLI to check the output of the above and all the following scripts we will execute for this tutorial.

Code Snippet

(Running code block using PHP CLI for checking connectivity)

Let's dive into complex aggregation pipelines using PHP

In this section, we will use aggregation pipelines to get useful insights from the sample Mflix dataset that we imported in the beginning of this tutorial. These examples will give you an idea of the most commonly used aggregation stages and how data passes through one stage to the another. You will see some of the complex examples, as well, which can offer some perspective on how you could handle such scenarios in your projects.

Find users who have commented the most on movies

To solve this problem, we will break it down into the following stages:

Group users by a unique identifier.
Sum the total comments left by each user.
Sort the results in descending order by the total comments.
Display the top five users.

Let's convert the above steps into an aggregation query in PHP.

Code Snippet

Results

Code Snippet

Explanation

Group users by email
- The $group stage groups documents by the email field.
- It creates a new field, totalComments, which is the sum of comments of each user.
- $sum:1 will add 1 for every record present with that email id.
Sort results
- The $sort stage sorts the grouped documents from the first step by the totalComments field in descending order.
Display top 10 results
- The $limit stage restricts the output to the top 10 users.

Get monthly average ratings over time

To solve this problem, we will break it down into the following stages:

Ensure that the documents have an imdb.rating field and a released date.
Extract the year and month from the released date.
Group and calculate the average rating for each month.
Sort the results by year and month.

Let's convert the above steps into an aggregation query in PHP.

Code Snippet

Results

Code Snippet

Explanation

Match stage
- The $match stage filters documents as per conditions passed.
- It filters out documents with an imdb.rating field.
Addfields stage
- The $addfields stage adds year and month fields to the $released field.
Group stage
- The $group stage groups documents by the year and month fields.
- $avg calculates the average ratings of the imdb.rating field.
Sort stage
- The $sort stage sorts the results in ascending order by the year and month fields.

Find user engagement metrics: Most commented movies, Most commented genres

We are going to use $facet here to demonstrate how two different metrics that require different operations/calculations can be performed in the single query with the same set of input documents.

The idea here is to get the top 5 most commented movies, and use the same movies collection to get the top 5 most commented genres.

To solve this problem, we will break it down into the following stages:

We can fetch both metrics in a single query.
Calculate the top 5 most commented movies.
Calculate the top 5 most commented genres.
Return the top 5 most commented movies and genres.

Let's convert the above steps into an aggregation query in PHP.

Code Snippet

<?php
$moviesCollection = $client->sample_mflix->movies;
$pipeline = [
    [
        '$lookup' => [
            'from' => 'comments',
            'localField' => '_id',
            'foreignField' => 'movie_id',
            'as' => 'comments'
        ]
    ],
    [
        '$facet' => [
            'mostCommentedMovies' => [
                [
                    '$project' => [
                        '_id' => 0,
                        'title' => 1,
                        'comment_count' => ['$size' => '$comments']
                    ]
                ],
                [
                    '$sort' => ['comment_count' => -1]
                ],
                [
                    '$limit' => 5
                ]
            ],
            'mostCommentedGenre' => [
                [
                    '$unwind' => '$genres'
                ],
                [
                    '$group' => [
                        '_id' => '$genres',
                        'totalComments' => ['$sum' => ['$size' => '$comments']]
                    ]
                ],
                [
                    '$sort' => ['totalComments' => -1]
                ],
                [
                    '$limit' => 5
                ]
            ],
        ]
    ]
];
$result = $moviesCollection->aggregate($pipeline);
// Convert the result to an array and pretty print
$resultArray = $result->toArray();
echo "<pre>" . print_r($resultArray, true) . "</pre>";

Results

Code Snippet

Pinged your deployment. You successfully connected to MongoDB!
[{
    "mostCommentedMovies": [
        {
            "title": "The Taking of Pelham 1 2 3",
            "comment_count": 161
        },
        {
            "title": "Ocean's Eleven",
            "comment_count": 158
        },
        {
            "title": "About a Boy",
            "comment_count": 158
        },
        {
            "title": "Terminator Salvation",
            "comment_count": 158
        },
        {
            "title": "50 First Dates",
            "comment_count": 158
        }
    ],
    "mostCommentedGenre": [
        {
            "_id": "Comedy",
            "totalComments": 15435
        },
        {
            "_id": "Adventure",
            "totalComments": 12363
        },
        {
            "_id": "Drama",
            "totalComments": 12339
        },
        {
            "_id": "Action",
            "totalComments": 11419
        },
        {
            "_id": "Fantasy",
            "totalComments": 6955
        }
    ]
}]

Explanation

Lookup stage
- The $lookup stage joins the movies collection with the comments collection on the _id field to fetch comments.
Facet stage
- The $facet stage allows running multiple aggregation pipelines in a single stage and can be used for deriving different metrics and views from the same dataset simultaneously.
- The result of the join gets stored in the movie field.
mostCommentedMovies metric pipeline
- The $project stage allows adding new fields to the pipeline and also offers flexibility of showing or hiding fields. We have hidden the _id field and kept the title field and added the comment_count field.
- The $sort stage sorts the users by the number of comment counts in ascending order.
- The $limit stage limits the output to five documents.
mostCommentedGenres metric pipeline
- The $unwind stage flattens the genres array to individual records.
- The $group stage groups the documents by genre and calculates the total comments for that specific genre.
- The $sort stage sorts the genres output by total number of comments.
- The $limit stage limits the output to five documents.

Content preferences analysis: Find out users' preferences in terms of movie genres

To solve this problem, we will break it down into the following stages:

Content preference can be identified with user engagements.
More comments on movie genres can help to understand use preferences of movie genres.

Let's convert the above steps into an aggregation query in PHP.

Code Snippet

<?php
$commentsCollection = $client->sample_mflix->comments;
$pipeline = [
    ['$lookup' => [
        'from' => 'movies',
        'localField' => 'movie_id',
        'foreignField' => '_id',
        'as' => 'movie'
    ]],
    ['$unwind' => '$movie'],
    ['$replaceRoot' => ['newRoot' => [
        'comment_id' => '$_id',
        'user' => '$name',
        'movie_id' => '$movie_id',
        'movie_title' => '$movie.title',
        'genres' => '$movie.genres',
        'rating' => '$movie.imdb.rating',
        'comment' => '$text'
    ]]],
    ['$unwind' => '$genres'],
    ['$group' => [
        '_id' => [
            'user' => '$user',
            'genre' => '$genres'
        ],
        'totalComments' => ['$sum' => 1],
        'averageRating' => ['$avg' => '$rating']
    ]],
    ['$group' => [
        '_id' => '$_id.user',
        'preferences' => [
            '$push' => [
                'genre' => '$_id.genre',
                'totalComments' => '$totalComments',
                'averageRating' => ['$round' => ['$avg' => '$averageRating', 2]]
            ]
        ],
        'totalComments' => ['$sum' => '$totalComments']
    ]],
    ['$sort' => ['totalComments' => -1]]
];

$result = $commentsCollection->aggregate($pipeline);
// Convert the result to an array and pretty print
$resultArray = $result->toArray();
echo "<pre>" . print_r($resultArray, true) . "</pre>";

Explanation

Lookup stage
- The $lookup stage performs a left join with the comments collection and movies collection on the movie_id to fetch movie details.
Unwind stage
- The $unwind stage flattens the joined movie array and the genres array to treat each genre separately.
Replaceroot stage
- The $replaceRoot stage replaces the root document with a new structure that includes user, movie details, genre, rating, and comment information.
First Group stage
- Groups the documents by user and genre, calculating: i. totalComments, the total number of comments made by each user on movies of each genre. ii. averageRating, the average rating given by the user on movies of each genre.
Second Group stage
- This groups the documents again by user, aggregating the preferences for each genre into an array. It also calculates the total number of comments made by the user across all genres.
Sort stage
- The $sort stage sorts the results by total comments in descending order.

Real-time new comment notifications and live feed update

To solve this problem, we will break it down into the following stages:

When a user makes a comment, send a notification.
When a user makes a comment, update a live feed, as well.

Let's convert the above steps into an aggregation query in PHP.

Code Snippet

<?php
$commentsCollection = $client->sample_mflix->comments;
$liveFeedCollection = $client->sample_mflix->live_feed;
$changeStream = $commentsCollection->watch();

function sendNotification($comment) {
    printf("Notification sent for new comment by %s on movie ID %s\n", $comment['name'], $comment['movie_id']);
}

function updateLiveFeed($comment, $liveFeedCollection) {
    $liveFeedDocument = [
        'user' => $comment['name'],
        'movie_id' => $comment['movie_id'],
        'comment' => $comment['text'],
        'timestamp' => new MongoDB\BSON\UTCDateTime()
    ];
    $liveFeedCollection->insertOne($liveFeedDocument);
    printf("Live feed updated with new comment by %s\n", $comment['name']);
}

for ($changeStream->rewind(); true; $changeStream->next()) {
    if (!$changeStream->valid()) {
        continue;
    }

$event = $changeStream->current();

if ($event['operationType'] === 'invalidate') {
        break;
    }

if ($event['operationType'] === 'insert') {
        $newComment = $event['fullDocument'];
        sendNotification($newComment);
        updateLiveFeed($newComment, $liveFeedCollection);
    }
}

Results

Notification: When a new comment is added, the sendNotification function is called, which sends a notification
Live feed update: the updateLiveFeed function inserts the new comment into the live_feed collection, which could be used to display recent activities on a website or application dashboard.

Explanation

Change stream stage
- The $changestream stage opens a change stream on the comments collection, which listens for any changes in real time.
sendNotification function
- This placeholder function simulates sending a notification whenever a new comment is added.
updateLiveFeed function
- This updates the live_feed collection with the new comment to keep a record of the latest activities.
For loop
- This iterates over the change stream, processing each change. If the change type is an insert, it triggers the notification function and updates the live feed.

Summary

The examples we have seen thus far cover the aggregation pipeline stages and operators used in ideal cases. From analysing user engagement metrics to performing real-time updates upon a user's action, MongoDB's aggregation pipeline allows for complex data transformation and insightful analytics.

While we've covered a few stages essential for aggregating and analysing data, MongoDB offers a wide array of aggregation pipeline stages beyond what is covered. To delve deeper into more advanced stages and analysis, refer to the official MongoDB documentation and the official PHP driver documentation.

Expand your understanding and leverage MongoDB's aggregation framework efficiently in PHP to address diverse data processing requirements and enhance your application's capabilities.

If you've any doubts or questions or want to discuss this or any new use cases further, you can reach out to me on LinkedIn.

Top Comments in Forums

There are no comments on this article yet.

Start the Conversation

Rate this quickstart

Article

The Top 4 Reasons Why You Should Use MongoDB

Sep 23, 2022 | 9 min read

Tutorial

Client-Side Field Level Encryption (CSFLE) in MongoDB with Golang

Feb 03, 2023 | 15 min read

Industry Event

TORONTO, ONTARIO, CANADA | IN-PERSON

Rails World

Sep 26, 2024 - Sep 27, 2024

Article

How to Secure MongoDB Data Access with Views

Apr 07, 2023 | 4 min read

What is aggregation?
Prepare a free tier cluster with sample dataset
Prepare your own development environment
Connect to your Atlas cluster using PHP
Let's dive into complex aggregation pipelines using PHP
Find user engagement metrics: Most commented movies, Most commented genres
Content preferences analysis: Find out users' preferences in terms of movie genres
Real-time new comment notifications and live feed update
Summary

MongoDB

Complex Aggregation Pipelines with Vanilla PHP and MongoDB

What is aggregation?

Prepare a free tier cluster with sample dataset

Prepare your own development environment

Connect to your Atlas cluster using PHP

Let's dive into complex aggregation pipelines using PHP

Find users who have commented the most on movies

Results

Explanation

Get monthly average ratings over time

Results

Explanation

Find user engagement metrics: Most commented movies, Most commented genres

Results

Explanation

Content preferences analysis: Find out users' preferences in terms of movie genres

Explanation

Real-time new comment notifications and live feed update

Results

Explanation

Summary

Top Comments in Forums

Related

The Top 4 Reasons Why You Should Use MongoDB

Client-Side Field Level Encryption (CSFLE) in MongoDB with Golang

Rails World

How to Secure MongoDB Data Access with Views

Table of Contents