#355 Hacking with Arel pro

May 30, 2012 | 15 minutes | Active Record, Refactoring, Search

Here I show a variety of ways to rewrite a long SQL query using only Active Record and Arel. This includes generating scopes dynamically, adding an "or" operator, and adding a powerful "match" method.

Click to Play Video ▶

Download:
source codeProject Files in Zip (80.4 KB)
mp4Full Size H.264 Video (38.7 MB)
m4vSmaller H.264 Video (18.5 MB)
webmFull Size VP8 Video (22.1 MB)
ogvFull Size Theora Video (43 MB)

Tyler Smart over 12 years ago

You can also override from using Class.from('select * from blah') in case you need to do nested selects.

9zb over 7 years ago

Yeah its really helpful!

n1kopuf@l over 12 years ago

Thanks a lot for this :)

Ryan, do you by any chance have in mind doing an episode similar to this, but focusing also on polymorphic associations, or associations in general?

Maybe it's just me, but I find very difficult to work with Arel when in those situations. Specially polymorphic. The solution is always do SQL by hand.

Bharat Ruparel over 12 years ago

Are you aware of this episode? Was revised recently...
http://railscasts.com/episodes/154-polymorphic-association-revised

omar a over 12 years ago

I don't really see the practical benefit of trying to replace SQL code with Ruby, if only for very basic queries.

What I'm really interested in is if SQL scripts can be called and returned to ActiveRecord from Ruby ... so that Ruby and SQL code are separated :-)

hazah over 12 years ago

Abstracting the SQL into ruby removes server specific syntax from your code, lets a driver take care of specifics, and leaving you to deal only with intent. Besides, at the end of the day, you can still use SQL when you really need to. The question I ask myself is "do I want to switch contexts here?"

Brad Phelan over 12 years ago

There is a big win when using a ruby based DSL for SQL. For basic queries it doesn't make a huge difference but when building a complex data querying module being able to compose queries out of smaller pieces is very useful.

I built rocket_tag on top of squeel as an example of how to use complex query building techniques in rails.

Jonas M over 12 years ago

I would love to see the match operator make its way into ActiveSupport. Such an awesome, clean syntax.

Brian McKelvey over 12 years ago

Definitely... this functionality is brilliant and not much code and should absolutely be included in Rails out of the box.

defsan about 12 years ago

Found this episode really great, specially insight how properly do active record mixins. I'm already using arel specially when queries need some complex includes/joins and SQL doesn't look good in model.
I think Match method is overkill for this case, specially in high performance you would do raw queries for search + caching.

Steve Brown about 12 years ago

Great stuff Ryan.

I hit a problem with the overridden - in /config/initializers/scope_operators.rb though. It conflicts with
activerecord-3.2.8/lib/active_record/associations/collection_association.rb:479

unless concat(new_target - target)

The minus operator fires the not() method defined in scope_operators.rb. Removing the alias made it happy again.

kainlite almost 12 years ago

Ryan, I've been watching a lot of railscasts, I'm lacking of knowledge in the subject, but there is a "Right way TM." to do a proper union in a "complex sql query" using ActiveRecord or Arel? I will show you what I'm trying to generate :(

          sql
        
SELECT * FROM users WHERE id = (SELECT enrollable_id FROM enrollments WHERE enrollable_type='User')
UNION ALL
SELECT * FROM users WHERE id = (SELECT user_id FROM memberships WHERE group_id = (SELECT enrollable_id FROM enrollments WHERE enrollable_type='Group'))

Is awesome what you can get with Arel but I don't know if what I'm trying to do is breaking any rule or is just not possible using AR or Arel..

Joel Grannas almost 12 years ago

Any advice on generating a dynamic clause... for example, "saved search" where a user could select which column, operator, value which could be multiple as well... for example:

column, operator, value, next_operator
make == ford |
make == pontiac &
year == 1993..1998

jay thompson almost 11 years ago

I am humbled by how simple the implementation ended up to add some very complex functionality, simply invoked I think that this is the best episode you have on this site. I await your return, eagerly- don't Why out on us!

amil1234 almost 8 years ago

it is good for all