ActiveRecord: The Great SQLizer Pt. 1

Does it pay to understand what’s going on under the hood? Yes! This article covers some SQL essentials.

Note: The SQL commands within this article are primarily being used with PostgreSQL, these commands may vary with other databases.The article also assumes you have working knowledge of Ruby/Rails.

Active Record is a wonderful abstraction given to us by Rails that lets us interact with our database in an easy and meaningful manner. This however can be troublesome for those who don’t have a good understanding of SQL in of itself. I’ve been guilty of this and have always had it in the back of my mind to round out this area of my knowledge. I recently took a SQL bootcamp course on Udemy and realized that SQL isn’t as intimidating as I initially thought and there’s a few important aspects of it that you need to be aware of. I’ll be discussing some aspects of SQL in an effort to not only reinforce my knowledge but to bring light to the fact that having a strong foundation in SQL is important to being a developer, especially in a full-stack or back-end role.

As someone who is new to SQL, you may feel intimidated in interacting with SQL statements. What you can do while you’re building up your skill is to activate your ActiveRecord logger so you can see the SQL queries being executed behind your ActiveRecord methods. Adding the below command to your environment file will enable the logger.

ActiveRecord::Base.logger = Logger.new(STDOUT)

Once you have that in place, you will be able to see logs in your terminal like this, where you can see that content and favorites for the user with user ID of 1 are being selected:

The corresponding ruby code as the logger is saying is on line 16 of my auth controller. The image below shows that the methods we were calling that invoked this query were @user.content and @user.favorites. I’m able to call these methods because of the relationships that I’ve created within my models. Even the schema created through ActiveRecord Migrations is executing create table statements in SQL! We’re able to have these relationships through foreign keys and join tables.

From this point forward, I’m going to break down some of the important aspects of SQL.

SQL Basics

The most basic statement is the SELECT statement. The format is SELECT col1 FROM table;. This is pretty much self explanatory, you’re choosing to select a particular column from a particular table. You might have also seen a SELECT *… statement, which is querying for all the records of a particular table. This is generally not recommended as you can potentially query for thousands of records without a need for it. You can also query for distinct values in a column by adding the keyword DISTINCT in front of the column name. In order to query for specific rows within your table you would use syntax like this: SELECT col1 FROM table WHERE condition;. The condition portion would include a condition based on the following operators:

1. = (Equal)
2. > (Greater than)
3. < (Less than)
4. >= (Greater than or equal)
5. <= (Less than or equal)
6. <> OR != (Not equal)
7. AND (logical operator AND)
8. OR (logical operator OR)

One thing to note about the AND operator is that you can’t use it on the same column, however you can use OR on the same column of a table.

You can also get back the count of the number of rows of a certain column in a table using the following: SELECT COUNT(col1) FROM table;. COUNT is an aggregate function which I’ll go over a little further on.

The keyword LIMIT is used to limit the number of rows that you’re returned. An example would be SELECT col1 FROM table LIMIT 5;. This works great when you want to find the highest price of something or lowest price for example. You can potentially order your rows in ASC or DESC order and limit 1. The ORDER BY statement is used to dictate the order and the syntax for it is as follows: SELECT col1 FROM table ORDER BY col1 ASC;.

As a supplement to the logical operators, we can also use BETWEEN to indicate a range of values. For example we might want to return the prices of items that are within a certain range, we would use the following statement: SELECT prices BETWEEN 5 AND 10 FROM items;. We can also use the IN syntax to check if a column has a certain value amongst a set of values. The syntax there would be for example: SELECT prices FROM items WHERE prices IN (4, 10, 2);

Another powerful keyword that is used for pattern matching amongst strings is the keyword LIKE. LIKE statements use wildcards to indicate the pattern of the matching strings.

1. % (Used for matching any number of characters)
2. _ (Used for matching a single character)

For example, you can have the following statement SELECT name FROM customer WHERE name LIKE ‘Jen%’;. This statement would potentially return matches with names such as Jen, Jennifer and Jenny.

SQL Grouping And Aggregate Functions

SQL has a keyword called GROUP BY which is pretty powerful and something that requires extra attention. A GROUP BY statement divides rows into groups which lets you run aggregate functions. A GROUP BY statement is like running DISTINCT on a column, because it will only return the unique instances in that column. For example if you’re using GROUP BY on a customer’s id it will return the unique IDs in that column. GROUP BY statements are often used in conjunction with aggregate functions because you can find out aggregate values for say each customer ID. I’ll list the aggregate functions below and explain how they would be used together with a GROUP BY.

1. AVG(col1) — will return the average value for that column.
2. MIN(col1) — will return the min value for that column.
3. MAX(col1) — will return the max value for that column.
4. SUM(col1) — will return the sum of the values in that column.
5. COUNT(col1) — as mentioned earlier, count will return the number of entries in that column.

The syntax for the GROUP BY statement is as follows: SELECT col1 FROM table GROUP BY col1;. A real world example of using GROUP BY could be trying to find the average price at which each customer rents movies. The statement would be something like this: SELECT customer_id, AVG(price) FROM rentals GROUP BY customer_id;

When using a GROUP BY statement we can also use the HAVING statement which is used to specify a certain condition on the GROUP BY. The syntax is as follows: SELECT col1 FROM table GROUP BY col1 HAVING condition;.

SQL Joins

In the real world, we often have to relate one piece of data to another. In SQL, we have join statements that let us relate data from one table to another. Join statements are being executed in the background when we’re using ActiveRecord to pull information relative to the relationships we’ve defined in our models and DB schema. The three types of joins that exist are: Inner, Outer and Self Joins. The way a join statement works is that you’re referencing primary/foreign keys on two different tables. The syntax for a join is as follows: SELECT A.pka, A.c1, B.pkb, B.c2 FROM A INNER JOIN B ON A.pka = B.fka;. I’ll unpack the statement so you can understands all the parts of it.

1. A in this case would be the table, pka would be the primary key from that table. For example it can be a customer_id for the customers table.
2. A.c1 is just another column from the A table.
3. B.pkb references the primary key from the second table, B.
4. B.c2 is another column from the second table.

In essence, you specify the columns you want data from, then you specify the main table, third you specify the table that the main table joins to using the primary key/foreign key that they have in common.

The different types of joins and their keywords are below:

1. INNER JOIN — produces rows in the result that match both table A and table B.
2. FULL OUTER JOIN — produces all the rows in both tables and fills in null where there is no matching information.
3. LEFT JOIN — returns all the rows in table A whether there is a match or not, but not table B where there aren’t matching records.
4. RIGHT JOIN — returns all the rows in table B whether there is a match or not, but not table A where there aren’t matching records.

Some tips regarding using joins.

1. If we want records in table A that are not in table B then we do a LEFT JOIN WHERE tableB.id IS NULL;.
2. If we want records that are unique to both table A and table B then we do a FULL OUTER JOIN WHERE tableA.id IS NULL OR tableB.id IS NULL;.

You can also join all the records of a table with another table with the UNION keyword. The syntax is as follows:

SELECT col1, col2, FROM table1 UNION SELECT col1, col2 FROM table2

The UNION keyword can come in handy in a real-world situation where you have multiple tables holding sales data for all the quarters in a fiscal year. You can use UNION to concatenate the records and run a GROUP BY statement to find out which employee had the most sales over the course of the year.

Combining the various keywords that you’ve learned up until this point will let you compose some powerful queries that can give you great insight into your data. Having a deeper knowledge of how SQL statements work will also help you when it comes time to debugging ActiveRecord errors!

This article was written to cover some of the essentials of SQL and to help you become comfortable with it. Stay tuned for pt. 2 of the article, which will cover advanced SQL commands and creating tables.