title: Laravel Eloquent : Record of Records for model instance
body: <p>I'm trying to figure out what's the best way of getting the index of the given Model in the Collection.</p>
  
  <p>Scenario : list of videos, which belong to a specific title. Title can have one or more videos. When on the given video page with the <code>Video</code> model at hand, I would like to display something along the lines <code>Video ? of ?</code>.</p>
  
  <p>Basically what I need to achieve is something that would reflect the following, raw sql statement:</p>
  
  <pre><code>SELECT `v`.*
  (
      SELECT COUNT(`id`)
      FROM `videos`
      WHERE `title_id` = ?
      AND `identity` &lt;= `v`.`identity`
  ) AS `collection_index`
  FROM `videos` `v`
  WHERE `v`.`id` = ?
  </code></pre>
  
  <p>Where <code>identity</code> is simply some field that is used to sort videos - might be numerical but also string in the format <code>01-03</code> etc. In the above <code>collection_index</code> would store the index (or rather sequential position) of the given record in the collection.</p>
  
  <p>Is it possible with a single statement on the Eloquent model - anyone came across a similar problem?</p>
  
  <p><strong>UPDATE / SOLUTION</strong></p>
  
  <p>After some digging and help from @JarekTkaczyk, I've managed to come up with a solution - for those who also might face the same problem.
  Here's my model using sub-query to achieve the above.</p>
  
  <pre><code>return Video::select('videos.*')
      -&gt;selectSub("
          SELECT COUNT(v.id)
          FROM videos as v
          WHERE v.title_id = videos.title_id
          AND v.identity &lt;= videos.identity
          ",
          'collection_index'
      )
      -&gt;selectSub("
          SELECT COUNT(v.id)
          FROM videos as v
          WHERE v.title_id = videos.title_id
          ",
          'collection_count'
      )
      -&gt;find(id);
  </code></pre>
  
  <p>Hope it will help someone.</p>