title: Laravel Eloquent - where in, pivot table, potentially very large query
body: <p>Imagine a Twitter style scenario where one user can follow another. A user may then see the posts (or Tweets) that users he/she follows have made.</p>
  
  <p>Example database structure:</p>
  
  <pre><code>users (id, username, created_at, updated_at)
  posts (id, user_id, body, created_at, updated_at)
  follows (id, follower_id, followed_id, created_at, updated_at)
  </code></pre>
  
  <p>User model</p>
  
  <pre><code>class User extends Eloquent implements UserInterface, RemindableInterface {
  
      use UserTrait, RemindableTrait;
  
      protected $table = 'users';
  
      /* A user can follow another user */
      public function followers()
      {
          return $this-&gt;belongsToMany('User', 'follows', 'follower_id', 'followed_id')-&gt;withTimestamps();
      }
  
      // ...
  }
  </code></pre>
  
  <p>Now, my initial approach using Eloquent would be to do something like:</p>
  
  <pre><code>// Within the User model
  public function getFeed()
  {
      // Ids of all follows
      $ids = $this-&gt;follows()-&gt;lists('followed_id');
  
      // Append this user's id
      $ids[] = $this-&gt;attributes['id'];
  
      // Grab up to 20 posts by people with these ids, ordered in descending order by date (Eager loading the user object associated with each post)
      return Post::whereIn('user_id', $ids)-&gt;with('user')-&gt;latest()-&gt;take(20)-&gt;get();
  }
  </code></pre>
  
  <p>Which will result in the following underlying queries:</p>
  
  <pre><code>select `followed_id` from `users` inner join `follows` on `users`.`id` = `follows`.`followed_id` where `follows`.`follower_id` = ?
  select * from `posts` where `user_id` in (?, ?) order by `created_at` desc limit 20
  select * from `users` where `users`.`id` in (?, ?)
  </code></pre>
  
  <p>In this case, I have only 2 user records in my table, who follow each other. What would happen if the user followed 1 thousand, 1 million, 10 million people? The second query would become very long, perhaps too long? Perhaps this approach would cause other issues.</p>
  
  <p>My natural approach to this problem in SQL would be to do one query, something like:</p>
  
  <pre><code>SELECT p.body, p.created_at, u.username FROM follows AS f
  LEFT JOIN posts AS p ON f.followed_id = p.user_id
  LEFT JOIN users AS u ON p.user_id = u.id
  WHERE f.follower_id = 1
  ORDER BY p.created_at DESC
  LIMIT 20
  </code></pre>
  
  <p>Is the approach I have taken using eloquent appropriate, and will it be able to cope (not knowing the number of user/follows records there may be in the future)? If not, is there another approach using eloquent that would be better? Would it be better to do everything in one SQL query like I've done above? Any other thoughts/suggestions are appreciated.</p>
  
  <p>I love using Laravel with Eloquent, but I am worried that the way it performs queries under-the-hood, might not always prove to be the best approach. It's pretty clever, but I don't expect it to be able to do everything in the best way.</p>