In my daily job, I mainly use Rails framework. To make code readable and maintainable, we seldom write raw SQL in the codebase. Instead, we use Rails's
ActiveRecord::QueryMethods module which helps developers write beautiful queries quickly. For example,
School.joins(:students).where('students.age > ?', 7).limit(100)
Recently, I was assigned to query different operation metrics, product metrics, and marketing metrics. Due to the diversity of these metrics, I found out that if I know what the raw SQL of the metrics is, not only can I complete the query quickly, but also I can use
ActiveRecord::QueryMethods more efficiently.
Here are the 4 steps that how I practice writing raw SQL.
1. Browse the Active Record association declaration
Each metric must derive from one or more models. In the huge codebase, we might not be familiar with every model, only when we can clearly understand the association declaration between the models do we write the correct query condition.
2. Imitation is the start of all learning
At first, I know how to query by Rails
ActiveRecord::QueryMethods, but I don't know how to write the raw SQL.
It's OK. Rails console can help us.
When asking about the result below, we can realize that it'll return an
# rails console Student.where(name: 'Lynn').class #=> Student::ActiveRecord_Relation
ActiveRecord::Relation class provides a
#to_sql method, it will return sql statement for the relation after it is called.
# rails console Student.where(name: 'Lynn').to_sql #=> "SELECT \"students\".* FROM \"students\" WHERE \"students\".\"name\" = 'Lynn'"
When we use
#to_sql frequently, we can be more familiar with raw SQL.
3. Practice by Blazer gem
#to_sql is like a learning process of input. Output is essential if we would like to be good at writing SQL.
Blazer is a Ruby gem.
Explore your data with SQL. Easily create charts and dashboards, and share them with your team.
After installing and configuring it to the application, we can write the raw SQL directly.
Blazer does help my SQL skills a lot.
4. Take a quiz from SQLZOO
SQLZOO is a SQL tutorial, which provides many query samples and quizzes that we can try. After learning by imitating and practicing writing SQL, I can finish more and more examination questions in SQLZOO now.
I have a sense of accomplishment during this learning process.
Hope you find this information helpful, also.