Rails where query fails when using variable in query

Question

Can some Rails expert throw some light on this behaviour in Rails 4:

>query_string = "agent_id = '1'"
 => "agent_id = '1'"

>Lead.includes('agents').where(query_string).length
 ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column 'agent_id' in 'where clause'

>Lead.includes('agents').where(agent_id = '1').length
 Lead Load (0.5ms)  SELECT `leads`.* FROM `leads` WHERE (1)
 LeadsAssignment Load (0.4ms)  SELECT `leads_assignments`.* FROM `leads_assignments` WHERE `leads_assignments`.`lead_id` IN (1, 2, 3, 4, 5)
Agent Load (0.5ms)  SELECT `agents`.* FROM `agents` WHERE `agents`.`id` IN (1, 2)
 => 5

The two queries should be identical. Why would one fail and the other not?

Thanks! Charlie

Lead.includes('agents').where(agent_id = '1').length is not even valid syntax and can not work — Andrey Deineko
– Andrey Deineko, Commented Nov 4, 2016 at 12:56
It's valid, it's just not doing what you expect. It's making a new variable called agent_id, assigning '1' to it, then using that to execute where('1')...which is valid, it's just going to match every row. — gmcnaughton
– gmcnaughton, Commented Nov 4, 2016 at 13:54
@gmcnaughton is spot on. This should be offered as an answer. — jeffdill2
– jeffdill2, Commented Nov 4, 2016 at 14:06

Magnuss · Accepted Answer · 2016-11-04 14:01:47Z

5

As @Andrey pointed out

Lead.includes('agents').where(agent_id = '1').length

Should be written as:

Lead.includes('agents').where(agent_id: 1).length

Because where is a ruby method and you are passing a ruby hash, where hash's key is the column name and value is the cell's value.

EDIT:

Yes, just noticed the error. You don't have agent_id column, you have agents table, which has a (hopefully) primary key id. So your query should be:

Lead.includes('agents').where(agents: { id: 1 }).length

Or:

Lead.includes('agents').where("agents.id = 1").length

edited Nov 4, 2016 at 14:01

answered Nov 4, 2016 at 13:01

Magnuss

2,32019 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Andrey Deineko Over a year ago

it does not answer the question in any way :)

Andrey Deineko Over a year ago

Lead.includes('agents').where(query_string).length is valid and should return a dataset, whereas Lead.includes('agents').where(agent_id = '1').length is invalid and should raise an argument of WHERE clause exception. You somehow say, that have vice versa working, which makes no sense.

gmcnaughton Over a year ago

It would also work if if were where("agent_id = '1'"). The query needs to be a string (wrapped in quotes) in order for activerecord to pass it along to MySQL. As it is now, it's ruby code and not a string, so Ruby evaluates it first.

Deepak Mahakale · Accepted Answer · 2016-11-04 14:22:47Z

Lead.includes('agents').where(query_string).length

This is a valid query which will return you proper result but as you can see the error

ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column 'agent_id' in 'where clause'

You don't have agent_id in leads table

Whereas

Lead.includes('agents').where(agent_id = '1').length

This syntax is wrong but the query is returning you some results because agent_id = '1' is an assignment operation which returns '1' to where clause

and the query which executes is

 Lead.includes('agents').where(1).length

which will return you the length of all records in Lead

The query should be

Lead.includes('agents').where(agent_id: 1).length

and you need to add agent_id in leads table

Collectives™ on Stack Overflow

Rails where query fails when using variable in query

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related