The new problem

In Part 1 of this article, I demonstrated how we could utilize a subquery to apply dynamic scopes on the latest related item in a Laravel HasMany relationship.

While the subquery is an elegant and very easy implementation, it has an unfortunate downside: subqueries aren’t exactly fast.

In a real world application, the following query took over 2.5 seconds right in tinker:

User::limit(1000)->usingDevice('mobile');
// Execution time: 2.65s

When performance is desired, there’s a much quicker solution and it requires the slightest bit of extra effort. First, let’s run that same query to prove it:

User::limit(1000)->usingDevice('mobile');
// Execution time: 0.061s

To understand why, let’s take a look at the differences in the queries built.

Subquery (macro):

SELECT * FROM `users` WHERE EXISTS (
SELECT
        *
    FROM
        `logins`
    WHERE
        `users`.`id` = `logins`.`user_id`
        AND `id` = (
            SELECT
                max(id)
            FROM
                `logins`
            WHERE
                `logins`.`id` = `logins`.`user_id`)
            AND `device_type` = ?)
LIMIT 1000

Intermediate View:

SELECT * FROM `users` WHERE EXISTS (
SELECT
        *
    FROM
        `logins`
        INNER JOIN `user_last_logins` ON `user_last_logins `.`login_id` = `logins`.`id`
    WHERE
        `users`.`id` = `user_last_logins`.`user_id`
        AND `device_type` = ?)
LIMIT 1000

The difference being the intermediate view is creating a join and is able to do so without having to determine the latest login id.

I’ve experimented with recreating this as a Query Builder macro, but so far have not succeeded. So, for now, I’ll walk you through the manual method of creating an intermediate MySQL view in Laravel.

MySQL views are virtually generated tables using queries, and come in handy when you need to structure data in the database. Brent of Spatie wrote a detailed article regarding MySQL views in Laravel that’s worth checking out: Eloquent MySQL views - stitcher.io

Time to get our hands dirty:

1. Create Intermediate LastLogin Model with Relationships

php artisan make:model LastLogin --migration

class LastLogin extends Pivot
{
    public $table = 'user_last_logins';

    public function user()
    {
        return $this->belongsTo('App\User');
    }

    public function login()
    {
        return $this->belongsTo('App\Login');
    }
}

2. Setup Migration & Migrate

Our MySQL View will use a query to fetch the latest login for each user and generate our virtual intermediate table:

id user_id login_id
1 1 10 
/**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        DB::statement($this->dropView());
        DB::statement($this->createView());
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        DB::statement($this->dropView());
    }

    private function createView()
    {
        return <<<SQL
CREATE VIEW `user_last_logins` AS
SELECT
    users.id as id,
    users.id AS user_id,
    max(logins.id) AS login_id
FROM
    logins,
    users
WHERE
    logins.user_id = users.id
GROUP BY
    user_id;
SQL;
    }

    private function dropView()
    {
        return <<<SQL
DROP VIEW IF EXISTS `user_last_login`;
SQL;
    }

Note: You can of course utilize external SQL files in the migration, otherwise make sure you comply with Heredoc (until PHP 7.3, we’re stuck with some ugly code as you’ll see with the indentation below).

php artisan migrate

3. Add Relationship to the User model

public function lastLogin()
{    
    return $this->hasOneThrough(
        'App\User', // related model
        'App\LastLogin', // intermediate model
        'user_id', // match on LastLogin (foreign key)
        'id', // User local key
        'id', // Login local key
        'login_id' // foreign key on LastLogin
    );
}

And finally, enjoy the performance improvements

public function scopeUsingDevice($query, $device)
{
    return $query->whereHas('lastLogin', function ($query) use ($device) {
        $query->whereLatest('device_type', $device);
    });
}

In my testing, the intermediate table using a MySQL view was over 40 times faster than the subquery. While it takes a few extra minutes to setup, in the long run it’s well worth it for me in the current project.

What’s next?

Ideally, I’d really like to see if the intermediate table performance could be created in an easy-to-implement macro/package. So as time permits, I’ll be working on that.

For now: To be continued… maybe?

Resources & Inspiration

Know of a better way to accomplish this?

I’d love to hear from you and collaborate on this. Reach out to me on Twitter: @jani_gyllenberg