Limit MySQL subquery results inside a WHERE IN clause, using Laravel's Eloquent ORM

This post was originally published here.

MySQL sub querying capability is a great tool that can be used to create very powerful and readable queries. In some cases, it can also provide big performance boosts if used correctly. By default, MySQL doesn’t allow us to add a limit clause in our subqueries, when using specific clauses like WHERE IN. Nevertheless, we can easily override this limitation by wrapping our subquery inside another query. In this tutorial, we are going to explain how to limit subquery results using Laravel’s Eloquent ORM.

Our database setup

Our database consists of two tables. The first one is the users table, having an id and an email column. The second one is the posts table, having an id, a title, and a user_id column. The user_id column acts as a foreign key for the id column of the users table. Let’s see how our data looks by joining the two tables with a simple query:

SELECT usres.id, users.email, posts.id AS post_id, posts.title
FROM users 
JOIN posts ON posts.user_id = users.id;

As you can see in the table below, the first user has published three articles, and the second user has published 2 articles.

id	email	post_id	title
1	[e-mail]	1	Post 1
1	[e-mail]	2	Post 2
1	[e-mail]	3	Post 3
2	[e-mail]	4	Post 4
2	[e-mail]	5	Post 5

Limit subquery results using plain MySQL

After reviewing our database structure, it’s time to start playing around. Let’s try to write a query that returns each user along with their latest post. We assume that the post order is determined through the post_id column, so the latest post is the one with the highest id. Therefore, a query like the following should do the job for us:

SELECT users.id, users.email, posts.id AS post_id, posts.title
FROM users 
JOIN posts ON posts.user_id = users.id 
WHERE posts.id IN (
    SELECT id FROM posts p2 
    WHERE p2.id = posts.id
    ORDER BY id DESC
    LIMIT 1
);

Unfortunately, executing the above query produces the following error:

ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

What just happened? Why can’t we use a LIMIT clause in conjunction with a WHERE IN clause in our subquery? Sad to say, but it’s one of MySQL’s restrictions. Although this restriction doesn’t make much sense, we have to live with it. It’s also very easy to override these restrictions.

The fastest way to fix this problem is by wrapping the original subquery inside another subquery. By creating an external query with a wildcard selector, we can then perform the original query inside it, as the external query doesn’t use the LIMIT clause. Let’s see what the following query returns when it gets executed:

SELECT users.id, users.email, posts.id AS post_id, posts.title
FROM users 
JOIN posts ON posts.user_id = users.id
WHERE posts.id IN (
  SELECT * FROM (
    SELECT id FROM posts sq_posts
    WHERE sq_posts.user_id = posts.user_id
    ORDER BY sq_posts.id DESC
    LIMIT 1
  ) as sq
);

id	email	post_id	title
1	[e-mail]	3	Post 3
2	[e-mail]	5	Post 5

So, we found a way to override the MySQL limitation. Now, let’s find out how we can implement this in Laravel’s Eloquent ORM.

Limit subquery results using Eloquent ORM

Let’s assume we have the following models in our Laravel application:

• User
• Post

Our User model has a function defining a relationship to the Post model:

function posts() {
  return $this->belongsToMany(App\Post::class);
}

So, if we run the following code we will get a list of users along with their posts:

User::with('posts')
      ->get();

Simple as that. If you are just starting with Laravel and want to learn more about how to define a relationship in Eloquent, follow this link.

The next step is to make our query to return only the latest post for each user. To do so, we will use a feature that Eloquent provides called query scopes. Query scopes allow us to inject filters in our queries, defined as model functions. In our example, we would do something like the following.

public function scopeLatestPost($query)
{
    return $query->whereIn('posts.id', function($query) {
        $query->select('id')
              ->from('posts sq_posts')
              ->whereRaw('sq_posts.user_id = posts.user_id')
              ->orderBy('sq_posts.id', 'desc')
              ->limit(1);
    });
}

This should inject a WHERE IN clause in our base query. But, as we saw earlier this is not going to work due to MySQL’s subquery limitations when using specific clauses like WHERE IN.

So, how do we achieve this using Eloquent?

The answer is simple: By using Eloquent’s built-in function called fromSub. Let’s see how the Eloquent query would look like, and then explain it in more detail.

public function scopeLatestPost($query)
{
    return $query->whereIn('posts.id', function($query) {
        return $query->fromSub(function($subQuery) {
            $query->select('id')
                  ->from('posts sq_posts')
                  ->whereRaw('sq_posts.user_id = posts.user_id')
                  ->orderBy('sq_posts.id', 'desc')
                  ->limit(1);
        }, 'sq');
    });
}

As you can see, we wrapped our subquery inside the fromSub function. If we don’t call any other functions after the fromSub function, it will wrap our subquery, as we saw in our previous example in plain MySQL. The ‘sq’ argument, in the end, defines the alias that Eloquent will give to the outer subquery (it is mandatory or else it will cause an error).

The fromSub function exposes the same functions as every other Eloquent query. For example, if we want to limit the external subquery results to match only posts with an id of 3 or higher (normally we would do this in the inner subquery but just to demonstrate how it’s done, we will do it in the outer one for this example), we could write the following:

public function scopeLatestPost($query)
{
    return $query->whereIn('posts.id', function($query) {
        return $query->fromSub(function($subQuery) {
            $query->select('id')
                  ->from('posts sq_posts')
                  ->whereRaw('sq_posts.user_id = posts.user_id')
                  ->orderBy('sq_posts.id', 'desc')
                  ->limit(1);
        }, 'sq')->where('id', '>', 3);
    });
}

Summary

In this blog post, we discussed how to perform a subquery which limits the amount of returned rows, inside a MySQL WHERE IN clause. We also explained how to implement this logic in the Eloquent ORM, by using the fromSub functions, as well as how we can extend these queries to match our needs.

If you found this blog post useful, you can subscribe to my newsletter and get to know first about any new posts.

Photo by Francesco Ungaro from Pexels

Comments

[Eduar Bastidas]

Amazing! Thank you so much!