Rail Search Function: ‘Where…LIKE’ doesn’t always cut it.

Active Record, an ORM, is the interface that allows us to work with the database, whether MySQL, Postgres, etc., using, in our case, Ruby code and logic. In conjunction with Ruby on Rails, the Active Record gem aids in the creation and use of objects whose data is stored in the database. An ORM, Object Relational Management, is the ‘library’ that maps our objects to the database table.

Active Record makes it possible to query (request data) from our database, without using raw sql statements. In order to make our interactions with the database faster and seamless, Active Record supplies us with a number of different finder methods to help us make these queries. [See the documentation here: https://guides.rubyonrails.org/active_record_querying.html

Image for post
Image for post
A raw sql query on the database implemented in a Model method.

One of the more frequently used of these methods, where, allows us to set conditions in our queries, which will limit the records returned to us according to these conditions. Unlike find or select, where returns to us a list of records. It will come as no surprise, then, that Active Records’ where method will come in handy when implementing a search bar into our program.

There are, of course, many ways we can implement a search function into a RoR program. I found two ways that were successful and gave me, mostly, the same result. I’ll share both here and the reason I went with the latter.

My 3rd Project was centered around providing a platform for users (Model #1) to sign up/log in and have the ability to not only see events (Model #2) posted, but through a join model, users_events, ‘RSVP’ to these events and if so desired, create their own events. After all models were successfully migrated into the database, controllers and views were set up to function so our MVC would operate as desired. I wanted users to have the ability through the events/index.html.erb view to not only see all instances of events, but to also search all events for the specifics they desired, whether it be in the events’ title, state, info, or category. Enter said search bar.

Image for post
Image for post
Event Search Bar

Behind every great search bar, lies a few lines of code in our controller and view to make all the magic happen.

Image for post
Image for post
events_controller.rb
Image for post
Image for post
event/index.html.erb calling on our partial file
Image for post
Image for post
the one, the only, the partial for our search bar form

Not only is the method in our controller using Active Record to query our database with where and LIKE, but a few other great things are happening here. We are searching through 4 columns (title, info, category & state) to find data that matches the params of our search (params[:search), but with the use of “?” and “%…%” we are helping Rails & Active Record sanitize our search so no code injection of invalid data is possible. Hey, you never know, and we don’t want to take any chances. Then we explicitly tell our program that once it queries the db, and grabs all the data we are looking for, to explicitly render the search view with the data we requested. The one downfall here is not any 2 users input data or search the same. If using the Postgres db, LIKE is case sensitive, so we run the risk of users missing out on events/information if they don’t search using exact case, etc. In my findings, thats where our next method steps in to replace this query, like a superhero, cape and all.

Image for post
Image for post
super-query, to save the day

ARel, a Ruby gem that builds queries for Active Record, makes it safer and easier to write complex SQL queries. It’s automatically included in all Rails programs. ARel uses objects as queries, instead of strings as queries. Because Ruby is an object oriented program, doesn’t it make sense to query objects, then?!?!? With ARel, we don’t have to write the SQL manually and Active Record constructs the query, also protecting against bad data injections. In this ARel, .matches will decide for us whether LIKE or ILIKE is the best method for the search. This query, in all of its glory, gives us a case-insensitive search.

Written by

Software Engineer proficient in Ruby, Ruby On Rails, Javascript & React JS.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store