Alias Both Table Names in Rails JOINS Method
Ruby on Rails is great for simplifying calls to SQL databases. Using Rails’ DSL, we can use Ruby methods to make simple select statements, where clauses and joins operations. While this DSL works great for simple queries, sometimes we need more complex SQL operations.
For these more complex operations we can still use the Rails DSL methods, but instead of passing in symbols we will pass in raw SQL queries as strings. To view this in action here is a simple joins call in which we have a Store model and want to join all the corresponding Order records for a single table:
This will result in a INNER JOIN (joins method’s default operation) of all our records. If we wanted to write this using raw SQL strings while still using Rails’ DSL and have a LEFT JOIN we would have this:
Both approaches are good and very intuitive! An issue I have run into comes when you work with tables that are heavily namespaced. Say instead of Store and Order you have Contractor::Rewards::Store and Contractor::Orange::Order. With rails instead of having tables of ‘stores’ and ‘orders’ you’d have to reference ‘contractor_rewards_stores’ and ‘contractor_orange_order’. The actual query would look like this:
This is lot, but not terrible if this is our full SQL operation. What if we want to add some where clauses, groupings and ordering? We’re going to have to write out the full name so many times. Not only is this a lot of typing that can lead to typo bugs, but it also is hard to read and follow. This is where aliasing table names comes in.
In SQL we can use the AS keyword when we introduce the table to change how we refer to it. This could be something simple like “SELECT * FROM order AS o” which would allow us to reference the order table as ‘o’ in later queries.
We’re familiar with how to do this in SQL, but how do we does this with Rails DSL? I’ve seen in many Rails projects the correct use of AS on the table that is being joined we have something like: “LEFT JOIN contractor_orange_order AS order…” but where would we put the alias for the table we’re joining on?
It looks a little funky, but to rewrite our previous JOIN statement we would preface the JOIN table specification with our main table’s alias. With aliasing we would turn our previous example to:
With these simple aliases we can know use “store” and “order” in any subsequent select, where, group or order query methods!
Conclusion
Making your code readable is one of the key tenants of good code. Adding appropriate aliases greatly helps in readability. I hope that this article helped clarify how to wed Rails DSL with raw SQL queries to create easily maintainable and readable code. Let me know if you have any other SQL tips or tricks!
Notes
