Using SQL View in Rails For Complex Queries
6 min read
In rails community, one of the stronger convention is to avoid writing plain SQL queries and stick to using the magnificent Active Record for all your query needs. This is especially true amongst developers whom have rails as their first web framework to learn/work with. The minute you say "why not write in plain SQL for that query?" they will stare at you as if to say "ain't nobody got time for dat". I know this because I used to feel that way. Rails is the first web development framework I learned and advanced so far without knowing how to write even a simple SELECT statement is SQL. At the end of the day, why try to reinvent the wheel? Why try learning SQL when I got the 'ol reliable Active Record to count on?
Well there comes a time in every young developer's career in which the emphasis is put on them not only on the task completion, but also on how well the task is done. In this case, it is how fast the feature performs. Basically I was tasked to do a number of features that generate reports related to class activities which comprise of data across a lot of database tables. The simplest of the report has to gather from 4 tables and the most complex one has to retrieve data from 10 tables and then formulate it. Active Record can definitely do the job for you, though its elegance would be lost because joining that many tables, then applying a bunch of subqueries will definitely make the AR query ugly as heck.
Then there is of course the performance-side of it. As good as you are at writing complex queries using AR, there will still be some cost to it. It may be okay for small sets of data, but when the data becomes large it definitely will be felt. In my case, the query to generate an activity of all students in a classroom takes about 2+ seconds (only the query time, not the whole page load) when the number of students in a classroom reaches more than 30+.
The VP of engineering, pushing me to reduce the load time, then suggested me to try use SQL Views as the query, then hook it to rails via a model class using ActiveRecord, just like you use any database table. I do not know about you, but for me this was mindblowing for two things: 1) I did not know anything about SQL View at that time (ie. I did not know that it exists at all), 2) although SQL views is not actually a table, we can still use it in rails as a model class using Active Record.
What The F Is a SQL View
Well it is basically set of results retrieved from table(s) with SQL statement. Example speaks louder than words in programming, so here we go starting from the simplest one:
SELECT email, name, address FROM users WHERE email is NOT NULL ORDER BY name
Suppose that is a query you will be using often, rather than typing it out everytime, you can be lazy by doing this:
CREATE VIEW user_infos AS SELECT email, name, address FROM users WHERE email is NOT NULL ORDER BY name
Now everytime you want to call that query, for example to get infos of users with first name of 'Michael', you can just write:
SELECT * FROM user_infos WHERE name ILIKE 'Michael%'
And basically that is that, it is the equivalent of using ActiveRecord Scope. As the examples shown above are quite simple, the benefit of using SQL View is negligible and it'd be more efficient just using plain AR. But suppose you have this SQL query:
SELECT student_id, w.klass_id, gradebook_id, partner_id, exam_type, w.gradebook_type, percentage, result FROM ( SELECT student_id, gradebook_type, gradebook_id, klass_id, (CASE is_average WHEN 't' THEN AVG(score) ELSE SUM(score) END) * 100 AS result FROM ( SELECT y.content_section_unit_id, klass_id, gradebook_id, gradebook_type, is_average, student_id, y.user_id, (CASE WHEN score IS NOT NULL THEN score::numeric ELSE 0::numeric END) AS score FROM ( SELECT content_section_unit_id, g.klass_id, gradebook_id, gradebook_type, is_average, cla.student_id, (SELECT id FROM users WHERE personable_id = student_id AND personable_type::text = 'Student'::text) AS user_id FROM ( SELECT c.id AS content_section_unit_id FROM sections AS a JOIN section_unit AS b ON a.id = b.section_id JOIN content_section_unit AS c ON b.id = c.section_unit_id ) z JOIN gradebook_components AS gc ON z.content_section_unit_id = gc.component_id AND gc.component_type ILIKE 'ContentSectionUnit' JOIN gradebooks AS g ON gc.gradebook_id = g.id AND gradebook_type::text IN ('post_quiz'::text, 'individual_assignment'::text, 'pre_quiz'::text, 'quiz'::text) JOIN class_attendees AS cla ON g.klass_id = cla.klass_id ) y LEFT JOIN quiz_responses AS qzr ON y.content_section_unit_id = qzr.content_section_unit_id AND qzr.user_id = y.user_id AND qzr.id IN (SELECT MAX(id) FROM quiz_responses GROUP BY user_id, content_section_unit_id) ) x GROUP BY student_id, gradebook_type, gradebook_id, klass_id, is_average ) w JOIN gradebooks ON w.gradebook_id = gradebooks.id
Do not sweat it whether you understand it or not, the point I am trying to make by showing you this query (which is basically only a quarter of the original query where it was taken from) is that the benefit of using SQL View is directly proportional to the level of complexities of query you need to make.
Using it in Rails
To use it in rails first we have to create the migration file to insert the SQL view into the database.
class SQLViewMigration < ActiveRecord::Migration def up execute " CREATE OR REPLACE VIEW classroom_scores AS SELECT student_id, w.klass_id, gradebook_id, partner_id, exam_type, w.gradebook_type, percentage, result FROM ( SELECT student_id, gradebook_type, gradebook_id, klass_id, (CASE is_average WHEN 't' THEN AVG(score) ELSE SUM(score) END) * 100 AS result FROM ( -- redacted for brevity, you know the gist of it ) x GROUP BY student_id, gradebook_type, gradebook_id, klass_id, is_average ) w JOIN gradebooks ON w.gradebook_id = gradebooks.id " end def down execute "DROP VIEW IF EXISTS classroom_scores" end end
Then do not forget to run
rake db:migrate. All that we have to to make the classroom_scores SQL view in our rails app is by writing a class model for it:
class ClassroomScore < ActiveRecord::Base end
Notice that the model name follows the rails convention tightly, which is to use the singular and camelized form of the table name (because the table is called 'classroom_scores'). If for any reason you want to use a different name to the table name, then you HAVE to do this instead:
class StudentScore < ActiveRecord::Base self.table_name = 'classroom_scores' end
Well bloody done, we can now utilize the SQL View in our rails app by calling:
Notice that we also can use methods provided by ActiveRecord with it. And basically that is it, with a little work you got the efficiency of SQL queries and the easiness and comfort of using AR. Hope this article will help you in improving the perfomance of database-heavy features in your app.