Is it possible to implement SUBSTRING_INDEX logic using Ruby Sequel to create a column alias?

Question

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.

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 filename:

• great_book_001.jpg
• great_book_002.jpg
• great_book_003_0001.jpg
• great_book_004_0002.jpg
• great_book_005_0003.jpg

With that in mind, I would like to extract that page number from the filename using MySQL’s SUBSTRING_INDEX. And using pure MySQL it took me about 5 minutes to come up with this raw query which works great:

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
;

The issue is I am trying to understand if it’s possible to implement column aliasing using SUBSTRING_INDEX while using the Sequel Gem for Ruby?

So far I don’t seem to be able to do this with the initial creation of a dataset like this:

# Fetch a dataset of media files.
one_to_many :media_files, :class => MediaFiles,
  :key => :id, :order => :rank

Since the returned dataset is an array, I am doing is using the Ruby map method to roll through the fetched dataset & then doing some string processing before inserting a page into the dataset using the Ruby merge:

# Roll through the dataset & 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 && split_value.length == 4
      m.values.merge({ :page => split_value })
    else
      m.values.merge({ :page => nil })
    end
  }
end

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 Sequel Gem for Ruby?

I gather that perhaps SUBSTRING_INDEX 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?

Answer 1

If you want your association to use that additional selected column and that filter, just use the :select and :conditions options:

substring_index = Sequel.expr{SUBSTRING_INDEX(SUBSTRING_INDEX(:filename, '.',  1), '_',  -1)}
one_to_many :media_files, :class => MediaFiles,
  :key => :id, :order => :page,
  :select=>[:id, :filename, substring_index.as(:page)],
  :conditions => {Sequel.function(:CHAR_LENGTH, substring_index) => 4}