Using Sub Queries in Laravel

I recently had to endure the process of writing an SQL query that relied on the a specific SQL subquery. For those of you who are experienced with SQL, relational databases and perhaps even Laravel, this sounds straightforward, right? Well yes, but surprisingly, there’s very little guidance or documentation for inexperienced developers regarding this topic.

The problem

Let’s say we have a table called posts, where a single post can have more than one category and have a status of either active, inactive or removed.

We then have another table containing data that gets imported every 4 hours from an analytics service. Each row is a report for the last 4 hours with a date, foreign keys linking it to the post and category and a popularity rating. We’ll call this table post_analytics.

I want to check the performance of each category to find out what topics my audience prefer to read and which category is performing the worst, where 40% or more of the posts in that category have a popularity score of 20 or lower, for the last 7 days.

SQL First

Before we go straight into writing PHP, it’s good practice to write out the more complex queries you want to achieve. If you don’t know SQL, I’d highly recommend learning at least the basics of SQL so that you have an understanding of what we’re trying to do.

The initial query will be on the `post_analytics` table, getting the performance ratings for all active posts, from the last 3 days, for my user.

SELECT post_id, category_id, status, report_date 
    AVG(performance_rating) AS avg_performance 
FROM post_analytics 
WHERE status = 'active' 
AND report_date >= DATE_SUB(NOW(), INTERVAL 3 DAY)
AND user_id = 2 
GROUP BY post_id, category_id

The GROUP BY is necessary as without this, SQL would just return a single record. We want a record per post, for every category it’s in.

Now, out of the returned results, we want to know how many posts in any category, have an average performance rating lower than 20. We know the average performance rating for each post over the last 3 days from the above query, so we’ll use that.

SELECT COUNT(post_id) AS low_perfomance_posts, category_id FROM
    (SELECT post_id, category_id, status, report_date
    AVG(performance_rating) AS avg_performance FROM post_analytics 
    WHERE status = 'active' and report_date >= DATE_SUB(NOW(), 
    INTERVAL 3 DAY) and user_id = 2 GROUP BY post_id, category_id) AS sub
WHERE avg_performance < 20
GROUP BY category_id

That doesn’t look too scary, right? Well, it isn’t for what we need it to do, this query is rather eloquent (pun 100% intended). But how would we translate this over to our Laravel application?

Now, PHP

Let’s start with the subquery, first we want to define the time frame that we want to gather data from, as we’ll need in the initial query. To save time, let’s use Carbon a Laravel extension for handling PHP’s \DateTime and \DateInterval .

$timeFrame = now()->subDay(3));

Now for the query, we’ll be using Eloquent’s Query Builder for this.

$sub = DB::table('post_analytics')
    ->selectRaw('post_id, category_id, status, AVG(performance_rating 
              AS avg_performance, )')
    ->where('status', '=', 'enabled')
    ->where('report_date', '>=', $timeFrame)
    ->where('user_id', 2)
    ->groupBy('post_id', 'category_id');

But how do we run this as a subquery in Laravel? Well, since we’ve used Query Builder, we can call the method toSql() on the subquery.

It’s worth noting that, if we were to create the above query on an Eloquent Model, we would be using the wrong Builder class as Laravel ships with two; \Illuminate\Database\Eloquent\Builder.php and \Illuminate\Database\Query\Builder.php. Since we’ve used the latter, we can call the mergeBindings() method on the next query to include the previous subquery.

/** @var array $lowScoringPosts */
$lowScoringPosts = DB::table( DB::raw("({$sub->toSql()})") )
    ->mergeBindings($sub)
    ->selectRaw('COUNT(post_id) AS low_scoring_posts, category_id')
    ->where('avg_performance', '>=', 20)
    ->groupBy('category_id')
    ->get();

Conclusion

Using subqueries in Laravel means that we can reduce the number of queries we are performing for one specific task, much like eager loading a relationship saves you from calling a relational query on each row of data.

From here

So we’ve got our data, in a meaningful form, but what are we going to do with it? Well, this query will return data in the following format:

25 => {#2522
    +"low_scoring_posts": 10611
    +"category_id": 2
}

So we can loop over each returned row and create a key, value array for each category, like so:

$poorPosts = [];
foreach ($lowScoringPosts as $post) {
    $poorPosts[$post->category_id] = [
        'post_count' => $post->low_scoring_posts
    ];
}

We can then do a much broader, third query, to get all the posts with performance ratings, grouped by category:

$allPosts = \DB::table('posts')
    ->selectRaw('COUNT(posts.id) AS post_count, 
                 posts.category_id AS category_id, 
                 categories.name AS name')
    ->join('categories', posts.category_id', categories.id')
    ->where('posts.status', 'active')
    ->where('posts.user_id', 2)
    ->groupBy('posts.category_id')
    ->get();

Looping over this, and ignoring categories that perform well, we can then build an array of category ids, for those which have a 40% low-performing post rate:

foreach ($allPosts as $post) {
    // Category doesn't have poor performing posts, skip these
    if (!array_key_exists($post->category_id, $poorPosts)) {
        continue;
    }

    $percentage = round(($poorPosts[$post->category_id]
                         ['low_scoring_posts'] / 
                            $post->post_count) * 100, 2);

    if ($percentage < 40)
        continue;

    $posts[] = [
        'id' => $post->category_id,
        'name' => $post->name
    ];
}

Where you go from here is up to you. For example, you could stick this into a console command which is then triggered via a Cron job on your server, and then use this data to suggest to users which of their categories they should consider removing. Or alternatively, encourage them to write more in these low performing categories, to boost their performance ratings.

I hope this has been helpful, this is my first Medium post, so please be kind in the comments. Feedback is warmly welcome (just keep it clean :P).