Active Record Deep Dive
27 Jul 2012If you’re new to Rails and Active Record, or you’ve been using them for some time, there are likely methods you’ve been overlooking. This week I take a look at the most common SQL Query interfaces for Active Record. Finally we wrap up by talking about how Active Record allows us to chain together methods, how we can prevent SQL injection attacks on our websites, and how to read Ruby Documentation.
Watch the video Recap of last week and take the quiz before looking at the solutions.
Feel free to skip around, most of the videos are self explanatory. This is week 7 of my Databases & Rails course taught at the University of Texas. If you’re new to databases you might want to start out with Why and how to use a database, and if you’re not familiar with Active Record Relationships you will get more out of this if you watch modeling relationships first.
With a Active Record we can use where to pull data from our database using a set of criteria. We can use a hash syntax, insert plain SQL, or use a query string escaped syntax. The where method can be used to return back objects or a collection of objects from the database.
Active Record: includes helps us to eager load data where appropriate and avoid N+1 query problems.
Using Find in Active Record is much like where but only looks up via primary key. If the record is not found an error will be raised.
The order of your data can be controlled in Active Record using the order method.
Sometimes we don’t want to return too much data, when we have a large dataset we can use limit to decrease our results.
Imagine you’re an online retailer like Amazon if someone is looking for frying pans, you’re not going to show them every frying pan you have on one page, it would be hard to look at, and wreak havoc on your server. You can use offset and limit together to “paginate” data. That is only showing 20, 50, 100, etc. items per page.
Understanding joining data is the foundation of building and traversing databases. Learn the concepts behind joining two tables and the differences between an inner and outer join. If you’re new to the concept of has many or belongs to you should watch the videos from week 2 modeling relationships with Active Record.
I came to Rails with no database background, learning to effectively understand and leverege joins has made a tremendous impact on the performance and quality of code i’m able to write in Rails.
We can group together similar terms inside of our database, we can use this for performing aggregate calculations, or for limiting search results to one result per grouped attribute.
Possibly one of the most confusing methods of the set, having is used to query against aggregated results. Typically having and group are used together. Having can be very useful when getting metrics about your data. For example if you have a user model, a course model, and a role model so that users can sign up to take courses through roles then we might want to see the average number of courses users have taken. We can do this with having.
Just in Time SQL Queries with Active Record
The interface for Active Record has improved dramatically since the early days of rails. See the mechanism behind how we are able to chain together methods to produce SQL queries, and how this same mechanism protects against un-used sql queries from taking up too much time on a page.
Preventing SQL Injection Attacks
As even XKCD knows, SQL injection is an all to real danger to database backed software. Learn how to craft a SQL injection attack inorder to protect your own code by using safe Active Record practices.
Quite possibly the most important skill of any Ruby programmer, reading documentation effectively is critical. We’ll take a look at the Ruby docs for Array which will help quite a bit with the exercise. Sometimes we might want to manipulate the results of our data set after we’ve gotten it back from the database, since returning a collection of objects will return as an Array in Rails, we can
If you liked the videos please take a moment to subscribe to my youtube channel, follow me, @schneems, on twitter, or subscribe to my tumblr blog. If you know someone learning rails for the first time or just looking to get better, a referral to this material would make a huge difference. Thanks for sticking around and have a great week!
Exercise
Use the docs to get the tests to pass for Arrays, Active Record and Hashes
