Gagan Gagan - 4 months ago 9
Ruby Question

what's the difference between by passing a symbol and string in where clause with joins

I have two models User and Book. User have username and email field, Book have author and title field. When I am using joins to get the users based on associated data with conditions passed in where clause, I am getting different results


  1. When I run this

    User.joins(:books).where('author = ? ','xxxx')



Mysql query generated is :

"SELECT `users`.* FROM `users` INNER JOIN `books` ON `books`.`user_id` = `users`.`id` WHERE (author = 'xxxxx' )"


gives me the user whose book include xxxx author whereas


  1. When I run this

    User.joins(:books).where(author: 'xxxx')



Mysql query generated is:

"SELECT `users`.* FROM `users` INNER JOIN `books` ON `books`.`user_id` = `users`.`id` WHERE `users`.`author` = 'xxxx'">


gives Mysql2::Error: Unknown column 'users.author' in 'where clause':

SELECT `users`.* FROM `users` INNER JOIN `books` ON `books`.`user_id` = `users`.`id` WHERE `users`.`author` = 'xxxx'


ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column 'users.author' in 'where clause': SELECT
users
.* FROM
users
INNER JOIN
book
ON
book
.
user_id
=
users
.
id
WHERE
users
.
author
= 'Dan Brown'

My question : is when I am passing a string encapsulated field and value in where clause it gives me result but when I pass a symbol in where clause it gives me mysql unknown column error. So how does ruby interpreter know from where to fetch data in symbol passing and string passing

Answer

When we join the tables especially in rails the rails has a power to take the aliasing of the joined tables.

Your query, User.joins(:books).where(author: 'xxxx') searches for the author field in the user table

User.joins(:books).where(author: 'xxxx') always takes the field of first table and

User.joins(:books).where('author = ? ','xxxx') searches in both the tables

For example, User.joins(:books).where('id = ? ','xxxx'), try this query.

In the above You will get an error because id field is present in both the tables and it gets confused

but,

User.joins(:books).where(id: 'xxxx') works because it searches only in the users table.

so, you can use, refer author through books books: {author: 'xxxx'}

User.joins(:books).where(books: {author: 'xxxx'})   // your required query

or

User.joins(:books).where('author = ? ','xxxx')