Arel Tech Talk

Refactoring SQL Queries using Arel and Query Builders

Arel will:

1. Simplify the generation of complex SQL queries
2. Adapt to various RDBMSes

More about Arel

Case in point:

ActiveRecord is built with Arel.

Let's look at some code

A Custom SQL We need to get into code.

SELECT d.* FROM distributions d
INNER JOIN distributions_salepoints ds ON ds.distribution_id = d.id
INNER JOIN salepoints s ON s.id = ds.salepoint_id
INNER JOIN petri_bundles pb ON pb.id = d.petri_bundle_id
INNER JOIN albums a ON a.id = pb.album_id
INNER JOIN upcs u ON u.upcable_id = a.id AND u.upcable_type = 'Album'
WHERE d.converter_class = 'MusicStores::Itunes::Converter'
AND s.store_id = 1
AND u.number IN (859709356761,859709363271)
ORDER BY d.id;
        

The cheap and easy way

Distribution.find_by_sql("
SELECT d.* FROM distributions d
INNER JOIN distributions_salepoints ds ON ds.distribution_id = d.id
INNER JOIN salepoints s ON s.id = ds.salepoint_id
INNER JOIN petri_bundles pb ON pb.id = d.petri_bundle_id
INNER JOIN albums a ON a.id = pb.album_id
INNER JOIN upcs u ON u.upcable_id = a.id AND u.upcable_type = 'Album'
WHERE d.converter_class = 'MusicStores::Itunes::Converter'
AND s.store_id = 1
AND u.number IN (859709356761,859709363271)
ORDER BY d.id;")
          

What is wrong with this code?

1. Not abstracted from database
2. Not object oriented
3. Not possible to have this syntax checked
4. Not actually ruby code. It's just embedded SQL code hiding in your ruby.
5. Not reusable (unless you define reusable as copy/paste)

So how can we make this better.

Using some AR methods

Distribution
  .select("distributions.*")
  .joins("
           INNER JOIN distributions_salepoints ds ON ds.distribution_id = distributions.id
           INNER JOIN salepoints s ON s.id = ds.salepoint_id
           INNER JOIN petri_bundles pb ON pb.id = distributions.petri_bundle_id
           INNER JOIN albums a ON a.id = pb.album_id
           INNER JOIN upcs u ON u.upcable_id = a.id AND u.upcable_type = 'Album'
        ")
  .where("
           distributions.converter_class = 'MusicStores::Itunes::Converter'
           AND s.store_id = 1
           AND u.number IN (859709356761)
        ")
          

Not much better since its still just embedding SQL into our method calls

Is there a way we can write this query using purely Ruby code?

Using Arel::Table instances to formulate our joins

Distribution
  .select(Distribution.arel_table[Arel.star])
  .joins(:salepoints, petri_bundle: { album: :upcs })
          

This is gonna get verbose, lets define methods to return the arel_table's we need.

def distribution
  Distribution.arel_table
end

def upc
  Upc.arel_table
end

def petri_bundle
  PetriBundle.arel_table
end

def salepoint
  Salepoint.arel_table
end
          

Using our new methods

Distribution
  .select(distribution[Arel.star])
  .joins(:salepoints, petri_bundle: { album: :upcs })
          

Building our where clauses

Arel comes with a wide assortment of predication and expression methods we can pass to the AR where method

Lets take a look at a few:

Arel Predication Source
Arel Expression Source

Using these on our where

where(distribution[:converter_class].eq(converter_class))

where(salepoint[:store_id].eq(store_id))

where(upc[:number].in(upcs))

# Performs a LIKE
where(distribution[:converter_class].matches("%#{converter_class}%"))

# Inequality
where(salepoint[:store_id].not_eq(store_id))

          

All these where clauses are chainable, so this is perfectly valid

where(distribution[:converter_class].eq(converter_class)).where(salepoint[:store_id].eq(store_id)).where(upc[:number].in(upcs))

# Or all passed to one where clause, chained with the .and method
.where(distribution[:converter_class].eq(converter_class).and(salepoint[:store_id].eq(store_id).and(upc[:number].in(upcs))))
          

Finishing up our new composed query

converter_class = "MusicStores::Itunes::Converter"
store_id = 1
upcs = [1,2,3]

Distribution
  .select(distribution[Arel.star])
  .joins(:salepoints, petri_bundle: { album: :upcs })
  .where(
          distribution[:converter_class].eq(converter_class)
        )
  .where(
           salepoint[:store_id].eq(store_id)
        )
  .where(
          # Note that i can pass an array to the in method
          upc[:number].in(upcs)
        )
          

Putting this in an object that can be reused.

• There are many ways to implement a query building object.
• One option: Use reflector methods to be able to chain methods off the instance. Example
• Another option: Use a factory/builder pattern to be able to pass in a hash of options that will build the query for you.

Some other things you can do with Arel.

• String together or and and methods
• Pass objects to the joins methods that represent OUTER, FULL OUTER, RIGHT OUTER, etc.. joins
• You can even define NameFunctions for DB specific methods like COALESCE
• Perform nested selects
• NOTE: Arel documentation is scarce so best bet is to read some source code.

Summary

• Extracted Raw SQL out of our code
• We now get syntax checking for free, since its just ruby
• Can be used on an DB
• Object oriented
• As demonstrated, can be using to build reuable query builder objects
• Got a lot of this information from this talk.

End