Rails 3 + Arel: Left outer join with table aliasing (Part 2)
It wasn’t until I read an article by Ernie Miller on MetaWhere, that I found out the lies that ".to_sql" has been putting me up with. Apparently, ".to_sql" doesn’t show you everything under the hood. For example:
Task.includes(:creator).where(“users.username LIKE ‘%manager%’”).to_sql # Outputs “SELECT `tasks`.* FROM `tasks` WHERE (users.username LIKE ‘%manager%’)”
With MetaWhere's own ".debug_sql", I was then able to see the complete SQL query that was being executed against my tables.
Task.includes(:creator).where(“users.username LIKE ‘%manager%’”).debug_sql # Outputs “SELECT * FROM tasks LEFT OUTER JOIN users ON users.id = tasks.creator_id AND users.deleted_at IS NULL WHERE (users.username LIKE ‘%manager%’)”
Now that the issue with “Includes” has been straightened, lets see the updated Task example that I’ve given in Part I.
IMPORTANT: Take note of the updated alias name for the Users table. I’ve updated it to “creators_tasks” as including :creators into your query will result in a default aliasing of users table as “creators_users” in SQL
Rails 3 + Arel: Left outer join with table aliasing (Part 1)
I recently stumble upon a situation that surprised me while developing a feature for users to create, assign and manage tasks. Here’s the background: A User model and a Task model that consists of both a creator_id using delynn’s Userstamp gem to refer to the user who created the task and a user_assigned_id that refers to the user that was assigned the task.
Like any other application, I need to add a table listing that allows users to search by both creator name and user_assigned name. What better to use than JQuery Datatables. Since it comes with searching capabilities, I only need to hook up the data source from the server side.
I wanted users to be able to search for Tasks by both the creator’s username and user_assigned username. This had logically implied me to use “Includes” since I have understood it to automatically use a LEFT OUTER JOIN on my queries (as learned from Ryan Bigg’s article), but to my surprise the search wasn’t working as intended. I then pulled out the handy .to_sql on my relation and to my surprise, the “Includes” are not using any LEFT OUTER JOINS anymore!
I then decided to use JOINS instead with a LEFT OUTER JOIN statement on the users table to get it working. Below is what I have worked out.
One thing that I still can’t figure it out is that, How on earth can someone use AREL to match by 2 or more fields that are CONCATENATED? i.e. CONCAT(users.first_name,users.last_name). This is something to think about and share once I find out the answer.