title: Getting N rows from far related table with conditions on close related table
body: <p>I have two tables, <code>schedules</code> and <code>shifts</code>, with the relationship of one-to-many. </p>
  
  <p>A <code>schedule</code> has <code>day</code>, <code>month</code>, <code>year</code> fields, as well as a <code>is_published</code> boolean. A <code>shift</code> has a <code>user_id</code> to have a relationship of one-to-one with <code>users</code>.</p>
  
  <p>Now, I want to get the next 5 upcoming <code>shifts</code> of a <code>user</code>. I would have to start from <code>schedule</code> since that table has the date in it. But, more importantly, I should only retrieve the <code>shifts</code> that belong to a published <code>schedule</code>. </p>
  
  <p>So the big problem is:</p>
  
  <p>The number of entries should be the 5 shifts. However, starting from schedule, I would not know how many schedules I need to retrieve until there are 5 shifts belonging to a user. </p>
  
  <p>Other than trial and error (i.e. retrieving x next schedules and testing to see if enough shifts exists. If not, retrieve the next n schedules until the quota is satisfied), is there an alternative?</p>
  
  <p><strong>Schema of Schedule</strong></p>
  
  <pre><code>Schema::create('schedules', function(Blueprint $table) 
  {
      $table-&gt;increments('id');
      $table-&gt;integer('user_id', false, true);
      $table-&gt;integer('client_id', false, true);
      $table-&gt;datetime('for');
      $table-&gt;enum('type', array('template', 'revision', 'common'));
      $table-&gt;string('name', 50)-&gt;default('Untitled Template');
      $table-&gt;boolean('is_published');
      $table-&gt;timestamp('published_at');
      $table-&gt;softDeletes();
      $table-&gt;timestamps();
  });
  </code></pre>
  
  <p>Here, the <code>user_id</code> is the creator's id, NOT who the schedule belongs to. This is used to keep track on how created a schedule in the future. </p>
  
  <p><strong>Schema of Shift</strong></p>
  
  <pre><code>Schema::create('shifts', function(Blueprint $table) 
  {
      $table-&gt;increments('id');
      $table-&gt;integer('schedule_id', false, true);
      $table-&gt;integer('user_id', false, true);
      $table-&gt;foreign('schedule_id')-&gt;references('id')-&gt;on('schedules')-&gt;onDelete('cascade');
      $table-&gt;softDeletes();
      $table-&gt;timestamps();
  });
  </code></pre>