The Proposal that I mentioned in the original articlewas 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.