Stephen's Thoughts, and Other Nonsense...

<?php echo $cleverComments[array_rand($cleverComments)]; ?>

speaker_notes

Limiting Belongs To Many database queries with Pivot table values in Laravel.

This thought occured 3 years ago about Eloquent, Howtos & Laravel.

This post was updated on 2014-01-22.

The Proposal that I mentioned in the original article was approved and then implemented by Taylor Otwell, and has since been released in Laravel 4.1.

You can now write the command like this:

$owners = Company::users()->wherePivot('owner', 1)->get();

Instead of:

$owners = Company::users()->where('company_user.owner', 1);

You can also use orWherePivot(), for an OR clause.

Thanks Taylor!

Original Article

If you haven't used it before, Laravel is a fantastic PHP framework, designed to be really simple and yet quite powerful. One of it's features is a fully featured Eloquent ORM that makes interacting with databases a easy. Like most ORMs, it provides tools for working with table relationships without having to do much extra coding. This includes support the common many-to-many relationship model with what they call a Belongs To Many relationship using a Pivot Table. A Pivot Table is a third database table that sits between the two tables being linked and provides the linking metadata between records.

Note: For the rest of this post I'm assuming you already know how to set up and use Belongs To Many Eloquent relationships in Laravel. If not, then I suggest you go read the documentation first.

So for example, we have a users table and a company table. These are joined by the company_user table. Normally that table would contain three columns: id, company_id, and user_id. It'd look something like this:

+----+------------+---------+
| id | company_id | user_id |
+----+------------+---------+
|  1 |          1 |       1 |
|  2 |          1 |       2 |
|  3 |          1 |       3 |
+----+------------+---------+

But what if you want to specific users to be owners of the companies they are a part of. (Note: A user can be an owner of one company, and a non-owner of another company.) We can cater for this easily in the database, and in Laravel, by adding in an extra field into the company_user table:

+----+------------+---------+-------+
| id | company_id | user_id | owner |
+----+------------+---------+-------+
|  1 |          1 |       1 |     1 |
|  2 |          1 |       2 |     0 |
|  3 |          1 |       3 |     0 |
+----+------------+---------+-------+

Easy right?

So, what happens when we want to query all of the owners of a company. One possible way to do this is:

$owners = array();
foreach (Company::find(1)->users as $user) {
    if ($user->pivot->owner) {
        $owners[] = $user;
    }
}
return $owners;

While that's a perfectly acceptable method, it's going to be quite slow for a large database. You don't want to retrieve and loop through every user to find the 5 owners in a list of 10,000 users.

So we need to find a better solution. I went searching through the Laravel documentation to find a helper method, or even some mention of a way to do it, but had no success. I also did a Google search, and found some similar ideas, but nothing actually solved the problem is a more efficient way. I then searched the Laravel GitHub Issues and Pull Requests to see if anyone else had this problem, with no luck.

Finally, I settled on digging through the Laravel source code and discovered that Laravel translates the column names into SQL in a flexible way and the query included the Pivot table in an accessible way, so you can actually hijack a where() query to apply to the Pivot table value. This means that, assuming you know the name of the Pivot table and column, you can easily specify it directly in a where() on the Belongs To Many relationship query:

$owners = $company->users()->where('company_user.owner', 1);

There we go, one query, one SQL call, and you only retrieve the Owners from the User table. Now, wasn't that simple :-)

It's worth nothing that although this method works, I don't like it either. It involves hard coding the pivot table name - which is a bad idea, since everything else automatically generates it, and if you want to change it, you've gotta go through a Find and Replace dance... So I've created a Proposal to add in helper functions into Laravel to do it for you. But until that is approved, you can use my workaround.

;