One Less Join using Laravel Pivot Tables

Laravel’s BelongsToMany relationship has a relatively unknown allRelatedIds method that might just shave off a MySQL join.

The Problem

Especially when building a SPA or otherwise building an API, you often only want to send the IDs of an attached ressource instead of the full data. Imagine an API response like this:

{
  "posts": [{
    "id": 1,
    "title": "Article about SCSS Mixins",
    "category_ids": [2]
  }, {
    "id": 2,
    "title": "Article about Responsive Web Design",
    "category_ids": [1, 2, 3],
  }]
}

There is a n:n relationship between posts and categories. Each category can have multiple posts, and each post can have multiple categories. In Laravel, this is a BelongsToMany relationship, defined like this:

class Post extends Model
{
  public function categories()
  {
    return $this->belongsToMany(Category::class);
  }
}

The database for this case would consist of three tables. One table for the posts, one table for the categories and one pivot table, to store a combination of post ID and category ID in each row.

The standard Laravel way of fetching all category IDs belonging to a post would be $post->categories()->pluck('categories.id'), resulting in a query like this:

select `categories`.`id` from `categories` inner join `category_post` on `categories`.`id` = `category_post`.`category_id` where `posts_tag`.`post_id` = ?

Even more complex would be $post->categories->pluck('id'):

select `categories`.*, `category_page`.`page_id` as `pivot_page_id`, `category_page`.`category_id` as `pivot_category_id` from `categories` inner join `category_page` on `categories`.`id` = `category_page`.`category_id` where `category_page`.`page_id` = ?

Both queries have an unnecessary join and load more data than we need. If there are scopes or pivot timestamps in play, the queries become even more complex.

The Solution

If we only care about the IDs of the related models and do not need to do any filtering based on their content, we can use the allRelatedIds() method of the BelongsToMany instance, resulting in a simpler, more performant query:

select `category_id` from `category_post` where `category_post`.`post_id` = ?