May 9, 2015 by Daniel P. Clark

Chaining an Array into Arel ors

I’d like to keep this post short.  So the basic idea behind this is that Arel helps you build SQL queries.  When you want to join multiple queries together into one result you use Arel’s or method.  The way Arel chains additional query commands is by nesting inside the current query.  For example, if you had 1,2,3 queries you wanted to “or”, then it would look something like this where(arel_table[1].or(arel_table[2].or(arel_table[3]))).  Arel also lets you build commands without executing them.  So similarly to what I’ve written above but without the where method.

I’ll demonstrate by giving a code example of finding orphaned polymorphic objects that I’ve written in my gem PolyBelongsTo.  First, here’s the code from the project, then I’ll explain the parts.

# Return Array of current Class polymorphic records that are orphaned from parents
# @return [Array<Object>] ActiveRecord orphan objects
def self._pbt_polymorphic_orphans
  accumulative = nil
  pbt_valid_types.each do |type|
    arel_part = arel_table[pbt_id_sym].not_in(type.constantize.pluck(:id)).
                                       and(arel_table[pbt_type_sym].eq(type))

    accumulative = accumulative.present? ? accumulative.or(arel_part) : arel_part
  end
  where(accumulative)
end

This is a private method I’ve made that will be included into every ActiveRecord object model.  The public method to use is pbt_orphans which can be called on AR models like User, Contact, Story, or whatever your db has for record objects.

The variable accumulative will be the query we plan to build with Arel.  The method called pbt_valid_types returns an Array of each used polymorphic type in the DB (that are valid types).  We then iterate over each type to find missing records.

So if we’re calling the method on the object Profile which belongs_to either User or Contact through it’s polymorphic table then we get [“User”, “Contact”] back for pbt_valid_types.

This next line:

arel_part = arel_table[pbt_id_sym].not_in(type.constantize.pluck(:id)).
                                   and(arel_table[pbt_type_sym].eq(type))

evaluates to:

arel_part = arel_table[:profileable_id].not_in("User".constantize.pluck(:id)).
                                   and(arel_table[:profileable_type].eq("User"))

and then gets added into an or chain with:

accumulative = accumulative.present? ? accumulative.or(arel_part) : arel_part

This assigns arel_part to accumulative if it’s not present, otherwise it builds on the existing query with accumulative.or(arel_part).  The loop goes over each valid record, plucks existing ids out and finds records that don’t include them but do include the type (those are orphaned), and appends the query.  Once the loop exits the query is called with where(accumulative).

These queries can get rather large as you’re plucking many ids out to test against so it maybe be great for manually finding issues with the DB, but might be too heavy on big DBs at times.  I don’t know how expensive the cost is.

I’ve written an individual method, that will be easy on the DB, called orphan? .  This method is available on record instances themselves so you can do Profile.first.orphan? and get a boolean response of either true or false.  Feel free to weigh the costs in design and implementation for yourself.

UPDATE 5/13/2015

I’ve changed out the pluck(: id) methods from the orphan lookup to arel_table.project(:id) as this will prevent the database from being hit multiple times and makes the method far more efficient.  It instead passes in additional SQL to get the same data rather than retrieving it out into Ruby to then passing it back into the DB.

Summary

So Arel query parts can be assigned to variables and built/combined.  I’ve shown here one example of building an OR query from an Array.  Hopefully you’ve found this insightful.  If you’re interested in finding orphaned records in your own DB feel free to use the gem PolyBelongsTo in your own projects!

Please feel free to comment, share, subscribe to my RSS Feed, and follow me on twitter @6ftdan!

God Bless!
-Daniel P. Clark

Image by Petri Damstén via the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 Generic License.

P.S. If you are a database expert I would be glad to receive any optimization pull requests you have for this.

#arel#buidling#build#builder#database#db#or#orhan#orphaned#orphans#query#rails#record#ror#ruby on rails

Leave a Reply

Your email address will not be published / Required fields are marked *