title: Is it possible to implement SUBSTRING_INDEX logic using Ruby Sequel to create a column alias?
body: <p>I have a client who has a database of images/media that uses a file naming convention that contains a page number for each image in the filename itself.</p>
  
  <p>The images are scans of books and page 1 is often simply the cover image and the actual “page 1” of the book is scanned on something like scan number 3. With that in mind the filenames would look like this in the database field <code>filename</code>:</p>
  
  <ul>
  <li><code>great_book_001.jpg</code></li>
  <li><code>great_book_002.jpg</code></li>
  <li><code>great_book_003_0001.jpg</code></li>
  <li><code>great_book_004_0002.jpg</code></li>
  <li><code>great_book_005_0003.jpg</code></li>
  </ul>
  
  <p>With that in mind, I would like to extract that page number from the filename using MySQL’s <code>SUBSTRING_INDEX</code>. And using pure MySQL it took me about 5 minutes to come up with this raw query which works great:</p>
  
  <pre><code>SELECT `id`, `filename`, SUBSTRING_INDEX(SUBSTRING_INDEX(`filename`, '.',  1), '_',  -1) as `page`
  FROM  `media_files`
  WHERE CHAR_LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(`filename`, '.',  1), '_',  -1)) = 4
  ORDER BY `page` ASC
  ;
  </code></pre>
  
  <p>The issue is I am trying to understand if it’s possible to implement column aliasing using <code>SUBSTRING_INDEX</code> while using the <a href="http://sequel.jeremyevans.net" rel="nofollow">Sequel Gem for Ruby</a>?</p>
  
  <p>So far I don’t seem to be able to do this with the initial creation of a dataset like this:</p>
  
  <pre><code># Fetch a dataset of media files.
  one_to_many :media_files, :class =&gt; MediaFiles,
    :key =&gt; :id, :order =&gt; :rank
  </code></pre>
  
  <p>Since the returned dataset is an array, I am doing is using the <a href="http://www.ruby-doc.org/core-2.1.2/Array.html#method-i-map" rel="nofollow">Ruby <code>map</code> method</a> to roll through the fetched dataset &amp; then doing some string processing before inserting a <code>page</code> into the dataset using <a href="http://www.ruby-doc.org/core-2.1.1/Hash.html#method-i-merge" rel="nofollow">the Ruby <code>merge</code></a>:</p>
  
  <pre><code># Roll through the dataset &amp; set a page value for files that match the page pattern.
  def media_files_final
    media_files.map{ |m|
      split_value = m[:filename].split(/_/, -1).last.split(/ *\. */, 2).first
      if split_value != nil &amp;&amp; split_value.length == 4
        m.values.merge({ :page =&gt; split_value })
      else
        m.values.merge({ :page =&gt; nil })
      end
    }
  end
  </code></pre>
  
  <p>That works fine. But this seems clumsy to me when compared to a simple MySQL query which can do it all in one fell swoop. So the question is, is there any way I can achieve the same results using the <a href="http://sequel.jeremyevans.net" rel="nofollow">Sequel Gem for Ruby</a>?</p>
  
  <p>I gather that perhaps <code>SUBSTRING_INDEX</code> is not easily supported within the Sequel framework. But if not, is there any chance I can insert raw MySQL instead of using Sequel methods to achieve this goal?</p>