Utilizing the functionality of SQL in Ruby through ActiveRecords

This aims to illustrate to the beginner some of the “baked in” tools of ActiveRecords that allow for SQL operations in a Ruby environment. These tools can be extremely time-saving, but can only be exercised under certain specific conditions. As a novice, I was under the impression that I could not take advantage of SQL commands within Ruby at all. I hope my initial misunderstandings and frustrating mistakes may be of use and are enlightening to ambitious inquisitive new-comers.

During the first group project in coding bootcamp, our team had created a command line interface CRUD music playlist app that used as one of its models a Song class, with attributes which included title and artist. One of the various ways users could select a song to add to their playlist was handled by a search method. Although it was functional, it was rather unforgiving to the end-user as the search term had to match *exactly* letter for letter, including whether it was uppercase, lowercase, or had accent marks (sorry, Beyoncé.)

I ventured to modify this search method to become more forgiving by allowing a partial string match. After researching for some possible avenues to pursue, I began to write my code. To set the stage, there is a model class called Song with attributes of artist, title, and genré. My rationale was to set the partial string to a variable, and then use an enumerable to iterate over the entire class using .include?

Using .all on class of Song and setting the array to variable all_songs

Iterating over array with variable set to string “ng” with .include? — *FAIL*

It failed, and it was returning the entire array. Serendipitously, this misguided attempt in Ruby led me down a path I would have otherwise not taken in exploring ActiveRecords, while simultaneously making clear my erroneous assumptions. I will elaborate on what I learned later, but let’s investigate the issue here now….

In this case, .each was iterating over every record and returning it. The proper way of how I should have proceeded was with .select. This iterates over every record, but returns only the ones which match the criterion.

.select returning only the matching three records

This *is* functioning, but exactly how forgiving is it to the end-user? Will it be flexible enough to accommodate for mismatched Capitol and lowercase letters? Let’s see by searching a mix-cased partial string of Awkwafina….

This found no records, and returned an empty array. The work around is to set all the records to .upcase or .downcase, and doing the same operation to the search term.

The Ruby way of doing partial string, matching mixed cases

This manner of doing it in Ruby was very elusive to me and above my pay-grade at the time. Luckily, by hitting a wall with my initial foray with .each, I investigated other methods which proved most fruitful — and that is using ActiveRecords as a bridge between your Ruby and SQL database.

While the Ruby code discussed above returns the desired result, behold how succinct the code below is in comparison.

using ActiveRecords where LIKE ?

Only the search term had to be set to a variable and then interpolated, and this action was taken on the entire class. Every song instance was not required to be set to an array. No iteration was needed. And it automatically adjusts for mixed cases. In addition, by using the SQL wildcard ‘%’, it allows for a more finely tuned search options— in such cases as only wanting to return results where it matches as a prefix (search term%) or as a suffix (%search term).

Once I found this out, I realized I could use many SQL queries in a Ruby environment that I previously had thought unavailable (e.g. INNER JOIN with .joins, find_by, and new tools like .pluck). A complete list of these can be found here.

A word of caution, however….these SQL tools can only be used on “pure” database records, and not arrays formed in Ruby or database records transformed in Ruby. I will illustrate below.

sql records being transformed into a Ruby artifact

Remember from our first example a variable of all_songs being set to the action Song.all. The above all_songs_copy is a simple .each iteration copying every Song instance in all_songs over and over into a new array. The contents of the two are identical. The difference between them is that the original all_songs is an array of “pure” SQL records, while the second is an array created in ruby, and is thus a Ruby artifact.

The .where query can be used on the class Song. It can even be used on a variable set to an array of its records taken straight from the database. But once this array has been iterated over, SQL queries available through ActiveRecords are no longer applicable and cannot be used.

So happy coding, enjoy these tools, and take note of where they can be used.