How to filter your data in Rails with PostgreSQL

How to filter your data in Rails with PostgreSQL

I started learning Ruby on Rails around the July of 6th this year.

Since then, I’ve met with multitudinous obstacles, one of which was how to filter a data index in Rails. While this may sound simple, the logic work and thinking processes behind it are far from clear. Though there are many ways to do this in Rails (just as there are many roads leading towards Rome), I aim to do it in the simplest (and most elegant) way possible. In fact, I’ve combed many Stack Overflow’s questions and answers, and most, if not, all of them weren’t to my liking.

We’re going to create a new Rails application. Type “rails new jobboard” in your Terminal (I named this project “jobboard” but you can name it anything you like). For the context, I’ll be creating a simple job board that allows for filtering of jobs using the job levels, “Intern”, “Fresh Grad”, “Manager”, etc.

Go into your directory, “jobboard”, by typing: cd jobboard

You need a code editor to edit the codes (Duh). I’m using “Visual Studio Code”, but you could use Sublime Text, Atom, or others as well.

To abstract away from the tedious work in creating a controller, model, view from scratch, I’ll use a special “feature” of Rails to quickly get a CRUD (Create, Read, Update, Delete) application up-and-running. Type “rails generate scaffold Job” and you will see this in the terminal

With this, you have a basic working CRUD application. Next, we’ll need to migrate the database to rails. If you don’t, you will get an error like the one below when you start the server and navigate to your localhost.

ActiveRecord::PendingMigrationError

Before we do the migration, we need to make some tweaks to your migration file. In your migration file under “migrate”, which is under “db”, you need to make these codes.

db>migrate

class CreateColumns < ActiveRecord::Migration[5.1]

def change

create_table :jobs do |t|

t.string :name

t.text :level, array: true

end

end

end

I deliberately highlighted the “array:true” because this is the crux of making the filtering action works. I repeat: It’s important to have “array: true” after the column that you wish to filter.

And we are going to use the PostgreSQL, a powerful database management system that allows for systematic storing of your data. Go to your gemfile and type:

gem 'pg'

Run “bundle install” in your terminal. Next go to your database.yml under your config and change “sqlite3” for default’s adapter to “postgresql”.

default: &default

adapter: postgresql

pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

timeout: 5000

Also, change the database to your database name. Since we are in the development environment, changing just the development environment will suffice.

Once done, type rails db:migrate to create a data table “jobs” in your database with your columns. Double-check your schema to ensure that you have the columns :name and :level reflected there. If these don’t appear, run “rails db:migrate:reset” in your terminal. These columns should appear thereafter.

Type “rails s” to start up your localhost server, and you will see:

This is not what we want to see though. Type this in your URL: http://localhost:3000/jobs to navigate to your jobs index page. And you see this

It looks pretty bare now so let’s add some html to the default scaffolding table tag to show the information better, i.e., the jobs’ headings, and their respective name and level, which will be an array. (The rest of the tags have been omitted for brevity)

<tr>

<th> Name </th>
<th> Level </th>

</tr>

<% @jobs.each do |job| %>

<tr>

<td><%= job.name %></td>
<td><%= job.level %></td>

...

</tr>

<style>

th, td {

border: 1px solid black;

}

</style>

Now, let’s head over to the form partial. When you created a scaffold, Rails very nicely created a form partial at the same time for you. The next thing we need to do is to create the checkboxes for multiple inputs.

*Note: The default form comes with a “form” object builder. I also changed from “form” to “f” to make my codes shorter and to follow the common practice of using “f” for form building in Rails.

Let’s pause for a while to analyse this. I used the check_box form helper with my attribute :level as the first argument, and passed the hash “multiple => true” to tell Rails that there are multiple attributes to be passed to :level in an array form. The “nil” is passed when the checkbox is not checked. I further added some style (margin-top: 10px) to have better spacing between any two checkboxes.

Once done, you’re able to create new jobs by writing the job name and checking on the different job levels needed, i.e., Intern, Fresh Grad, Manager, etc.

Click “Create Job”, and you will see a flash message “Job was successfully created.” Navigate back to the index page by clicking “Back” and you will see your new job created in the table. I took the liberty to further create many other jobs to demostrate the filtering better.

Now, let’s add the filtering link. Head over to your index view and add this above the table tag and below your </h1> tag.

The first argument is the text that you will see on the web page. The second argument “jobs_path” refers to the index action for your jobs controller. It takes a parenthesis of :level => “Intern” because you want to pass this params[:level] = Intern to the controller to select all jobs which have “Intern” under the “:level” attribute. We’ve also added a ‘Clear” link to clear the filtering by passing the jobs instance to the controller.

At this point, we didn’t concern ourselves with the routing as the scaffolding has already created a nice resource for us. In this case, the default routes of jobs will ensure that jobs_path will link to the index action of the jobs controller.

Now go to your jobs controller and type these codes in the index action:

def index

@jobs = if params[:level].present?

Job.where(' ? = ANY (level)', params[:level])

else

Job.all

end

end

*I would like to attribute this code to my friend, “Aaron Reisman”, who kindly sat with me and showed me the way to solve the entire filtering mechanism. My gratitude goes to him and many other helpful developers who pointed me in the right direction in the journey to create this nice feature.

That’s it! The simple application that has filtering is completed. You can navigate to the index page, ‘/jobs’ and click on “New Job” link to create a new job by typing in your job name and clicking on the different job levels. Thereafter, click on the different job level links to filter.