rails 4.2 query with operator in

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

rails 4.2 query with operator in

João Bordalo
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

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/51bb61be-7eda-4548-9d3c-21c0652b2a61%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: rails 4.2 query with operator in

Marco Antonio Filho
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

On Wed, May 31, 2017 at 12:16 PM João Bordalo <[hidden email]> wrote:
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

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/51bb61be-7eda-4548-9d3c-21c0652b2a61%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/CACMkcE5Q0M9gsBq%3DAsMhcrUsQtLg9FpSCt5Lm21Fqik2%3DqFRrQ%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: rails 4.2 query with operator in

João Bordalo
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

Em quarta-feira, 31 de maio de 2017 11:47:19 UTC+1, Marco Antonio Almeida escreveu:
Hi João,

The problem is that you don't have a field called '<a href="http://projects.id" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fprojects.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNF1WUfrW20pOczXWfU5mkpSkDwtsw&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fprojects.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNF1WUfrW20pOczXWfU5mkpSkDwtsw&#39;;return true;">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| <a href="http://p.id" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fp.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFIAR8e5XORlw08iI1aHcV7TDh77g&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fp.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFIAR8e5XORlw08iI1aHcV7TDh77g&#39;;return true;">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

On Wed, May 31, 2017 at 12:16 PM João Bordalo <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="ZvUVY8kdBQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">bordalo...@...> wrote:
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('<a href="http://projects.id" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fprojects.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNF1WUfrW20pOczXWfU5mkpSkDwtsw&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fprojects.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNF1WUfrW20pOczXWfU5mkpSkDwtsw&#39;;return true;">projects.id IN (?)', @projects.map { |p| <a href="http://p.id" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fp.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFIAR8e5XORlw08iI1aHcV7TDh77g&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fp.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFIAR8e5XORlw08iI1aHcV7TDh77g&#39;;return true;">p.id })
            .includes(:expenses, :baseline => [:project, :coordinator]). 

The error is this: 
SQLite3::SQLException: no such column: <a href="http://projects.id" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fprojects.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNF1WUfrW20pOczXWfU5mkpSkDwtsw&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fprojects.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNF1WUfrW20pOczXWfU5mkpSkDwtsw&#39;;return true;">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 (<a href="http://projects.id" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fprojects.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNF1WUfrW20pOczXWfU5mkpSkDwtsw&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fprojects.id\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNF1WUfrW20pOczXWfU5mkpSkDwtsw&#39;;return true;">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

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="ZvUVY8kdBQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">rubyonrails-ta...@googlegroups.com.
To post to this group, send email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="ZvUVY8kdBQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">rubyonra...@googlegroups.com.
To view this discussion on the web visit <a href="https://groups.google.com/d/msgid/rubyonrails-talk/51bb61be-7eda-4548-9d3c-21c0652b2a61%40googlegroups.com?utm_medium=email&amp;utm_source=footer" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://groups.google.com/d/msgid/rubyonrails-talk/51bb61be-7eda-4548-9d3c-21c0652b2a61%40googlegroups.com?utm_medium\x3demail\x26utm_source\x3dfooter&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/msgid/rubyonrails-talk/51bb61be-7eda-4548-9d3c-21c0652b2a61%40googlegroups.com?utm_medium\x3demail\x26utm_source\x3dfooter&#39;;return true;">https://groups.google.com/d/msgid/rubyonrails-talk/51bb61be-7eda-4548-9d3c-21c0652b2a61%40googlegroups.com.
For more options, visit <a href="https://groups.google.com/d/optout" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;">https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/30fe6f15-2d39-41a3-8c15-ae1f8362deb5%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: rails 4.2 query with operator in

Graham Ashton-3
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.

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/2265d327-8a03-4eca-bd61-91da536747f0%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Loading...