rails 4.2 query with operator in

rubyonrails-talk
1

Hi, there.

I’m trying to solve a problem that previously worked on rails 3.2 and isn’t in rails 4.2:

The query is the following:

Investment.revenues

.where(‘projects.id IN (?)’, @projects.map { |p| p.id })

.includes(:expenses, :baseline => [:project, :coordinator]).

The error is this:

SQLite3::SQLException: no such column: projects.id: SELECT "investments".* FROM "investments" INNER JOIN "classifications" ON "classifications"."id" = "investments"."classification_id" INNER JOIN "baselines" ON "baselines"."id" = "investments"."baseline_id" WHERE "classifications"."pl_line_mask" IN (0, 1, 8, 10, 11) AND "baselines"."type_mask" = ? AND (projects.id IN (169,177,286,292,301,360,361,365,422,423,424,438,443,452,472,520,525,566,575,583,592,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620,622))



I believe this error is related to how the in operator is been used. I can't find documentation related to this operator.



How can I solve this situation?





Thank you in advance,

João Bordalo
2

Hi João,

The problem is that you don’t have a field called ‘projects.id’ in the ‘investments’ table. Does your investment model belongs to one project? Then you would do

Investment.revenues

.where(project_id: @projects.map { |p| p.id })

.includes(:expenses, baseline: [:project, :coordinator])

However, I need to have more information on the database schema to have a better insight. It could also be that you have a many to many relationship between projects and investments and you would need other setup for that, but it depends on how the fields are setup.

Kind regards,

/ Marco

3

First of all, thank you for your quick reply!!!

The relation is a bit complex:

in the investment’s model there is this relation:

belongs_to :project, :class_name => “Baseline”, :foreign_key => :baseline_id this is the relation between investment and project

4

I suspect the issue is that you’ve referred directly to a primary key called projects.id in your SQL, but (from the belongs_to call) we can see that column is actually called baseline_id.

Have you tried changing ‘projects.id IN (?)’ to ‘projects.baseline_id IN (?)’ in your initial query? That might sort it.