PostgreSQL syntax error Rails 4

Question

I have a Rails 4 app deployed on heroku.

In development using MySQL I receive no errors.

In production on heroku I get the following:

Mar 16 09:40:22 squareshaped app/web.1:  Started GET "/" for 87.83.45.218 at 2015-03-16 16:40:22 +0000 
Mar 16 09:40:22 squareshaped app/web.1:  Processing by PagesController#index as HTML 
Mar 16 09:40:23 squareshaped app/web.1:  PG::SyntaxError: ERROR:  syntax error at or near ")" 
Mar 16 09:40:23 squareshaped app/web.1:  LINE 1: SELECT COUNT(*) FROM "courses"  WHERE "courses"."id" IN () 
Mar 16 09:40:23 squareshaped app/web.1:                                                                   ^ 
Mar 16 09:40:23 squareshaped app/web.1:  : SELECT COUNT(*) FROM "courses"  WHERE "courses"."id" IN () 
Mar 16 09:40:23 squareshaped app/web.1:    Rendered pages/index.html.erb within layouts/application (69.8ms) 
Mar 16 09:40:23 squareshaped app/web.1:  ActionView::Template::Error (PG::SyntaxError: ERROR:  syntax error at or near ")" 
Mar 16 09:40:23 squareshaped app/web.1:  LINE 1: SELECT COUNT(*) FROM "courses"  WHERE "courses"."id" IN () 
Mar 16 09:40:23 squareshaped app/web.1:                                                                   ^

I thought that this might be because my code is asking it to look up a value in an empty array. So I added an if statement so it wouldn't look up if the array was empty. This isn't working.

def index
@courses = Course.limit(7).order(created_at: :desc)
@reviews = Review.limit(7).order(created_at: :desc)
if user_signed_in?
  # Find peer recommended courses
  @peers = User.where("position LIKE ?", current_user.position).where('id <> ?', current_user.id)
  @peer_recommended_courses = []
  @peers.each do |peer|
    @peer_recommended_courses << peer.reviews.where(recommended: true).pluck(:course_id)
  end
  if @peer_recommended_courses.empty?
    @peer_recommendations = []
  else
    @peer_recommendations = Course.where(id: @peer_recommended_courses)
  end

  #Find company recommended courses
  @colleagues = User.where("company LIKE ?", current_user.company).where('id <> ?', current_user.id)
  @colleague_recommended_courses = []
  @colleagues.each do |peer|
    @colleague_recommended_courses << peer.reviews.where(recommended: true).pluck(:course_id)
  end
  if @colleague_recommended_courses.empty?
    @colleague_recommendations = []
  else
    @colleague_recommendations = Course.where(id: @colleague_recommended_courses)
  end

end
end

I'd be grateful if anyone could explain why this error is occurring.

p.s. apologies for awful coding, I'm very much a noob and think that most of this should probably be in my Course model. I'm working on it. Just want to ship this update.

@peer_recommendations = Course.where(id: @peer_recommended_courses) in this line or@colleague_recommendations = Course.where(id: @colleague_recommended_courses) in this line @peer_recommended_courses or @colleague_recommended_courses is nil — Sonalkumar sute
– Sonalkumar sute, Commented Mar 16, 2015 at 17:02
Are you using any gems extending ActiveRecord (like postgres_ext)? Because in your case AR should handle this (I mean empty array) and generate valid SQL. Required your Gemfile for any case. — Maxim
– Maxim, Commented Mar 16, 2015 at 17:15
Sontya - That's what I've tried to mitigate against with the if statements. It isn't working though. Is there a better way to allow for this exception? Have just tried @peer_recommended_courses.nil? and @peer_recommended_courses.blank? and that doesn't solve it — rcrcr
– rcrcr, Commented Mar 16, 2015 at 17:20
Maxd - No not using any gems to extend ActiveRecord. Agree ActiveRecord should handle this. Unless it's something to do with my if statement declaring a new empty array? — rcrcr
– rcrcr, Commented Mar 16, 2015 at 17:22

Lukas Eklund · Accepted Answer · 2015-03-16 17:20:33Z

2

The query is still running because @peer_recommended_courses isn't an empty array.

@peer_recommended_courses = []
  @peers.each do |peer|
    @peer_recommended_courses << peer.reviews.where(recommended: true).pluck(:course_id)
end

If if there are no peer reviews where recommended is true the value of @peer_recommended_courses is now actually [[]] not just []. Flatten the array so that Arel handles the query properly (or flatten the array before the empty? check):

@peer_recommendations = Course.where(id: @peer_recommended_courses.flatten)

answered Mar 16, 2015 at 17:20

Lukas Eklund

6,1481 gold badge34 silver badges34 bronze badges

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

2 Comments

mu is too short Over a year ago

Or use flat_map instead of the each.

rcrcr Over a year ago

This solved it perfectly. And allowed me to get rid of those clunky if statements. Thanks very much! So it was the << in the do loop that was creating an array within the array? I didn't realise it would do that.

Collectives™ on Stack Overflow

PostgreSQL syntax error Rails 4

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related