Issues with PG in heroku and model sort w.r.t the selected ids in rails

Question

>----< means has_many_and_belongs_to in this post.

Let me start with the model and relationships:

events >-----< categories , events >-----< domains , events >-----< eligibles

I am sorting out the event data in a refresh_controller based on the selection made. Here is the code i'm using. It works perfectly fine with sqlite3. While in production, PG is causing some issues. When I have categories that are not linked to events and do a search it's throwing an error. Let me know if there is another way to do it.

Here is the controller.

def index
@cat = Category.find(params[:category][:category_id]) if params[:category][:category_id].present?
@dom = Domain.find(params[:domain][:domain_id]) if params[:domain][:domain_id].present?
@eli = Eligible.find(params[:eligible][:eligible_id]) if params[:eligible][:eligible_id].present?
@e_1=[]
@e_2=[]
@e_3=[]
@e_1 << @cat.events.collect{|p| p.id} if @cat.present?
@e_2 << @dom.events.collect{|p| p.id} if @dom.present?
@e_3 << @eli.events.collect{|p| p.id} if @eli.present?
@e_f = @e_1 | @e_2 | @e_3
@all = Event.where(id: @e_f)
@user = current_user || User.new

#@event_ids || @events.collect{|p| p.id}
#@event_ids = id_array.collect{|id| id.to_i}

end

index.html.erb:

 <div class="container">
  <b> <%= @cat.name if @cat.present? %> <%= @dom.name if @dom.present? %> <%= @eli.name if @eli.present? %></b>
<%= render partial: "static_pages/event", collection: @all %>

</div>

Here are my heroku logs:

     heroku logs
2014-03-12T09:39:28.894581+00:00 app[web.1]:    /app/vendor/ruby-2.1.0/lib/ruby/2.1.0/webrick/server.rb:170:in `select'
2014-03-12T09:39:28.894581+00:00 app[web.1]: [2014-03-12 09:39:28] FATAL SignalException: SIGTERM
2014-03-12T09:39:28.894581+00:00 app[web.1]:    /app/vendor/ruby-2.1.0/lib/ruby/2.1.0/webrick/server.rb:160:in `start'
2014-03-12T09:39:28.894581+00:00 app[web.1]:    /app/vendor/ruby-2.1.0/lib/ruby/2.1.0/webrick/server.rb:170:in `block in start'
2014-03-12T09:39:28.894581+00:00 app[web.1]:    /app/vendor/ruby-2.1.0/lib/ruby/2.1.0/webrick/server.rb:32:in `start'
2014-03-12T09:39:28.894581+00:00 app[web.1]:    /app/vendor/bundle/ruby/2.1.0/gems/rack-1.5.2/lib/rack/handler/webrick.rb:14:in `run'
2014-03-12T09:39:28.894834+00:00 app[web.1]:    /app/vendor/bundle/ruby/2.1.0/gems/railties-4.0.2/lib/rails/commands.rb:71:in `<top (required)>'
2014-03-12T09:39:28.894834+00:00 app[web.1]:    bin/rails:4:in `require'
2014-03-12T09:39:28.894834+00:00 app[web.1]:    bin/rails:4:in `<main>'
2014-03-12T09:39:30.273961+00:00 heroku[web.1]: Process exited with status 143
2014-03-12T09:39:33.095928+00:00 heroku[web.1]: Starting process with command `bin/rails server -p 5234 -e production`
2014-03-12T09:39:38.514112+00:00 app[web.1]: [2014-03-12 09:39:38] INFO  WEBrick 1.3.1
2014-03-12T09:39:38.514112+00:00 app[web.1]: [2014-03-12 09:39:38] INFO  ruby 2.1.0 (2013-12-25) [x86_64-linux]
2014-03-12T09:39:38.514323+00:00 app[web.1]: [2014-03-12 09:39:38] INFO  WEBrick::HTTPServer#start: pid=2 port=5234
2014-03-12T09:39:41.211555+00:00 heroku[web.1]: Stopping all processes with SIGTERM
2014-03-12T09:39:41.702711+00:00 app[web.1]:    /app/vendor/bundle/ruby/2.1.0/gems/rack-1.5.2/lib/rack/server.rb:264:in `start'
2014-03-12T09:39:41.702711+00:00 app[web.1]: [2014-03-12 09:39:41] FATAL SignalException: SIGTERM
2014-03-12T09:39:41.702711+00:00 app[web.1]:    /app/vendor/ruby-2.1.0/lib/ruby/2.1.0/webrick/server.rb:170:in `select'
2014-03-12T09:39:41.702711+00:00 app[web.1]:    /app/vendor/ruby-2.1.0/lib/ruby/2.1.0/webrick/server.rb:32:in `start'
2014-03-12T09:39:41.702711+00:00 app[web.1]:    /app/vendor/ruby-2.1.0/lib/ruby/2.1.0/webrick/server.rb:170:in `block in start'
2014-03-12T09:39:41.702711+00:00 app[web.1]:    /app/vendor/bundle/ruby/2.1.0/gems/rack-1.5.2/lib/rack/handler/webrick.rb:14:in `run'
2014-03-12T09:39:41.702711+00:00 app[web.1]:    /app/vendor/bundle/ruby/2.1.0/gems/railties-4.0.2/lib/rails/commands/server.rb:84:in `start'
2014-03-12T09:39:41.702976+00:00 app[web.1]: [2014-03-12 09:39:41] INFO  going to shutdown ...
2014-03-12T09:39:41.702711+00:00 app[web.1]:    /app/vendor/bundle/ruby/2.1.0/gems/railties-4.0.2/lib/rails/commands.rb:71:in `tap'
2014-03-12T09:39:41.702976+00:00 app[web.1]:    /app/vendor/bundle/ruby/2.1.0/gems/railties-4.0.2/lib/rails/commands.rb:71:in `<top (required)>'
2014-03-12T09:39:41.702976+00:00 app[web.1]:    bin/rails:4:in `require'
2014-03-12T09:39:41.702976+00:00 app[web.1]:    bin/rails:4:in `<main>'
2014-03-12T09:39:41.702711+00:00 app[web.1]:    /app/vendor/ruby-2.1.0/lib/ruby/2.1.0/webrick/server.rb:160:in `start'
2014-03-12T09:39:41.702711+00:00 app[web.1]:    /app/vendor/bundle/ruby/2.1.0/gems/railties-4.0.2/lib/rails/commands.rb:76:in `block in <top (required)>'
2014-03-12T09:39:41.702976+00:00 app[web.1]: => Rails 4.0.2 application starting in production on http://0.0.0.0:5234
2014-03-12T09:39:41.702976+00:00 app[web.1]: => Run `rails server -h` for more startup options
2014-03-12T09:39:41.702976+00:00 app[web.1]: => Booting WEBrick
2014-03-12T09:39:41.702976+00:00 app[web.1]: => Ctrl-C to shutdown server
2014-03-12T09:39:41.702976+00:00 app[web.1]: Exiting
2014-03-12T09:39:41.702976+00:00 app[web.1]: [2014-03-12 09:39:41] INFO  WEBrick::HTTPServer#start done.
2014-03-12T09:39:42.930276+00:00 heroku[web.1]: Process exited with status 143
2014-03-12T09:39:51.611779+00:00 heroku[web.1]: Starting process with command `bin/rails server -p 53475 -e production`
2014-03-12T09:39:58.904726+00:00 app[web.1]: [2014-03-12 09:39:58] INFO  WEBrick 1.3.1
2014-03-12T09:39:58.904726+00:00 app[web.1]: [2014-03-12 09:39:58] INFO  ruby 2.1.0 (2013-12-25) [x86_64-linux]
2014-03-12T09:39:58.904963+00:00 app[web.1]: [2014-03-12 09:39:58] INFO  WEBrick::HTTPServer#start: pid=2 port=53475
2014-03-12T09:39:59.295118+00:00 heroku[web.1]: State changed from starting to up
2014-03-12T09:40:00.368665+00:00 app[web.1]: => Run `rails server -h` for more startup options
2014-03-12T09:40:00.368665+00:00 app[web.1]: Started GET "/refresh?utf8=%E2%9C%93&category%5Bcategory_id%5D=&domain%5Bdomain_id%5D=&eligible%5Beligible_id%5D=4&commit=Search" for 122.167.180.66 at 2014-03-12 09:40:00 +0000
2014-03-12T09:40:00.368665+00:00 app[web.1]: => Booting WEBrick
2014-03-12T09:40:00.368665+00:00 app[web.1]: => Rails 4.0.2 application starting in production on http://0.0.0.0:53475
2014-03-12T09:40:00.368665+00:00 app[web.1]: => Ctrl-C to shutdown server
2014-03-12T09:40:00.368665+00:00 app[web.1]: Started GET "/refresh?utf8=%E2%9C%93&category%5Bcategory_id%5D=&domain%5Bdomain_id%5D=&eligible%5Beligible_id%5D=4&commit=Search" for 122.167.180.66 at 2014-03-12 09:40:00 +0000
2014-03-12T09:40:00.493767+00:00 app[web.1]: Processing by RefreshController#index as HTML
2014-03-12T09:40:00.493767+00:00 app[web.1]: Processing by RefreshController#index as HTML
2014-03-12T09:40:00.531632+00:00 app[web.1]:   User Load (2.5ms)  SELECT "users".* FROM "users" WHERE "users"."id" = 1 ORDER BY "users"."id" ASC LIMIT 1
2014-03-12T09:40:00.565344+00:00 app[web.1]:   Eligible Load (3.0ms)  SELECT "eligibles".* FROM "eligibles" WHERE "eligibles"."id" = $1 LIMIT 1  [["id", "4"]]
2014-03-12T09:40:00.493974+00:00 app[web.1]:   Parameters: {"utf8"=>"✓", "category"=>{"category_id"=>""}, "domain"=>{"domain_id"=>""}, "eligible"=>{"eligible_id"=>"4"}, "commit"=>"Search"}
2014-03-12T09:40:00.493974+00:00 app[web.1]:   Parameters: {"utf8"=>"✓", "category"=>{"category_id"=>""}, "domain"=>{"domain_id"=>""}, "eligible"=>{"eligible_id"=>"4"}, "commit"=>"Search"}
2014-03-12T09:40:00.531632+00:00 app[web.1]:   User Load (2.5ms)  SELECT "users".* FROM "users" WHERE "users"."id" = 1 ORDER BY "users"."id" ASC LIMIT 1
2014-03-12T09:40:00.565344+00:00 app[web.1]:   Eligible Load (3.0ms)  SELECT "eligibles".* FROM "eligibles" WHERE "eligibles"."id" = $1 LIMIT 1  [["id", "4"]]
2014-03-12T09:40:00.599548+00:00 app[web.1]:   Event Load (1.6ms)  SELECT "events".* FROM "events" WHERE "events"."id" IN ()
2014-03-12T09:40:00.592159+00:00 app[web.1]:   Event Load (5.2ms)  SELECT "events".* FROM "events" INNER JOIN "eligibles_events" ON "events"."id" = "eligibles_events"."event_id" WHERE "eligibles_events"."eligible_id" = $1  [["eligible_id", 4]]
2014-03-12T09:40:00.599548+00:00 app[web.1]:   Event Load (1.6ms)  SELECT "events".* FROM "events" WHERE "events"."id" IN ()
2014-03-12T09:40:00.599548+00:00 app[web.1]: LINE 1: SELECT "events".* FROM "events"  WHERE "events"."id" IN ()
2014-03-12T09:40:00.599548+00:00 a
pp[web.1]: : SELECT "events".* FROM "events"  WHERE "events"."id" IN ()
2014-03-12T09:40:00.599548+00:00 app[web.1]:                                                                  ^
2014-03-12T09:40:00.599548+00:00 app[web.1]: : SELECT "events".* FROM "events"  WHERE "events"."id" IN ()
2014-03-12T09:40:00.600282+00:00 app[web.1]:   Rendered refresh/index.html.erb within layouts/application (3.7ms)
2014-03-12T09:40:00.599548+00:00 app[web.1]:                                                                  ^
2014-03-12T09:40:00.600282+00:00 app[web.1]:   Rendered refresh/index.html.erb within layouts/application (3.7ms)
2014-03-12T09:40:00.599548+00:00 app[web.1]: PG::Error: ERROR:  syntax error at or near ")"
2014-03-12T09:40:00.600424+00:00 app[web.1]: Completed 500 Internal Server Error in 106ms
2014-03-12T09:40:00.592159+00:00 app[web.1]:   Event Load (5.2ms)  SELECT "events".* FROM "events" INNER JOIN "eligibles_events" ON "events"."id" = "eligibles_events"."event_id" WHERE "eligibles_events"."eligible_id" = $1  [["eligible_id", 4]]
2014-03-12T09:40:00.599548+00:00 app[web.1]: LINE 1: SELECT "events".* FROM "events"  WHERE "events"."id" IN ()
2014-03-12T09:40:00.602492+00:00 app[web.1]: ActionView::Template::Error (PG::Error: ERROR:  syntax error at or near ")"
2014-03-12T09:40:00.602492+00:00 app[web.1]:                                                                  ^
2014-03-12T09:40:00.602492+00:00 app[web.1]: : SELECT "events".* FROM "events"  WHERE "events"."id" IN ()):
2014-03-12T09:40:00.602492+00:00 app[web.1]:     2:   <b> <%= @cat.name if @cat.present? %> <%= @dom.name if @dom.present? %> <%= @eli.name if @eli.present? %></b>
2014-03-12T09:40:00.602492+00:00 app[web.1]:     4: 
2014-03-12T09:40:00.602492+00:00 app[web.1]: LINE 1: SELECT "events".* FROM "events"  WHERE "events"."id" IN ()
2014-03-12T09:40:00.602492+00:00 app[web.1]:     1: <div class="container">
2014-03-12T09:40:00.599548+00:00 app[web.1]: PG::Error: ERROR:  syntax error at or near ")"
2014-03-12T09:40:00.602693+00:00 app[web.1]:     6:    <script type="text/javascript" language="javascript" charset="utf-8">
2014-03-12T09:40:00.602693+00:00 app[web.1]:   app/views/refresh/index.html.erb:3:in `_app_views_refresh_index_html_erb___3814188212292096351_70209931602340'
2014-03-12T09:40:00.602693+00:00 app[web.1]: 
2014-03-12T09:40:00.602693+00:00 app[web.1]: 
2014-03-12T09:40:00.602693+00:00 app[web.1]: ActionView::Template::Error (PG::Error: ERROR:  syntax error at or near ")"
2014-03-12T09:40:00.602693+00:00 app[web.1]:                                                                  ^
2014-03-12T09:40:00.600424+00:00 app[web.1]: Completed 500 Internal Server Error in 106ms
2014-03-12T09:40:00.602693+00:00 app[web.1]:     1: <div class="container">
2014-03-12T09:40:00.602881+00:00 app[web.1]:     2:   <b> <%= @cat.name if @cat.present? %> <%= @dom.name if @dom.present? %> <%= @eli.name if @eli.present? %></b>
2014-03-12T09:40:00.602881+00:00 app[web.1]:     4: 
2014-03-12T09:40:00.602881+00:00 app[web.1]: 
2014-03-12T09:40:00.602693+00:00 app[web.1]: : SELECT "events".* FROM "events"  WHERE "events"."id" IN ()):
2014-03-12T09:40:00.602492+00:00 app[web.1]:     3: <%= render partial: "static_pages/event", collection: @all %>
2014-03-12T09:40:00.602693+00:00 app[web.1]: LINE 1: SELECT "events".* FROM "events"  WHERE "events"."id" IN ()
2014-03-12T09:40:00.602881+00:00 app[web.1]:     3: <%= render partial: "static_pages/event", collection: @all %>
2014-03-12T09:40:00.602881+00:00 app[web.1]:     5: </div>
2014-03-12T09:40:00.602693+00:00 app[web.1]: 
2014-03-12T09:40:00.602881+00:00 app[web.1]:     6:    <script type="text/javascript" language="javascript" charset="utf-8">
2014-03-12T09:40:00.602492+00:00 app[web.1]: 
2014-03-12T09:40:00.602881+00:00 app[web.1]: 
2014-03-12T09:40:00.602492+00:00 app[web.1]:     5: </div>
2014-03-12T09:40:00.602881+00:00 app[web.1]:   app/views/refresh/index.html.erb:3:in `_app_views_refresh_index_html_erb___3814188212292096351_70209931602340'
2014-03-12T09:40:00.967239+00:00 heroku[router]: at=info method=GET path=/favicon.ico host=floating-castle-4592.herokuapp.com request_id=d1fe2f58-7a19-42dc-9ef4-940d4841d822 fwd="122.167.180.66" dyno=web.1 connect=2ms service=7ms status=200 bytes=228
2014-03-12T09:40:00.609749+00:00 heroku[router]: at=info method=GET path=/refresh?utf8=%E2%9C%93&category%5Bcategory_id%5D=&domain%5Bdomain_id%5D=&eligible%5Beligible_id%5D=4&commit=Search host=floating-castle-4592.herokuapp.com request_id=578810a1-52fc-4d2a-a095-97a8983d926b fwd="122.167.180.66" dyno=web.1 connect=1ms service=243ms status=500 bytes=1543

Edits:

Also, I would like to know how to sort the events w.r.t to their popularity in the selection.

Can you post more of your stack trace for the error? This will point you to the line in your code causing the problem. You can see in the log that the problem is caused by this sql being executed: `SELECT "events".* FROM "events" WHERE "events"."id" IN ()`. This isn't valid sql - there needs to be something in the brackets. So, the next step is to work out which line is making this call - the stack trace will show you. — Max Williams, Mar 12 '14 at 10:32
Edited the post. Well! I'm new to this terminology. BTW whats stack trace? Where to get it? I just posted more heroku logs :| — nk06, Mar 12 '14 at 10:40
The stack trace is the list of files/lines where the code handles an exception - it "bubbles up" through lots of layers of code. Usually in rails you will see a lot of rails gem references, then eventually some of your code (ie files in your rails project folder). It's almost always the first one of these that is the problem. — Max Williams, Mar 12 '14 at 10:55

Max Williams · Accepted Answer · 2014-03-12T14:25:45.563

I think this is your problem:

@e_1 << @cat.events.collect{|p| p.id} if @cat.present?
@e_2 << @dom.events.collect{|p| p.id} if @dom.present?
@e_3 << @eli.events.collect{|p| p.id} if @eli.present?
@e_f = @e_1 | @e_2 | @e_3
@all = Event.where(id: @e_f)

In this case, if @e_1, @e_2 and @e_3 are all blank (nil) then @e_f is nil, then you do this

@all = Event.where(id: nil)

and i think that's what's causing your sql problem.

A much nicer way of doing what you want to do is this:

def index 
  @cat = Category.find(params[:category][:category_id]) if params[:category][:category_id].present?
  @dom = Domain.find(params[:domain][:domain_id]) if params[:domain][:domain_id].present?
  @eli = Eligible.find(params[:eligible][:eligible_id]) if params[:eligible][:eligible_id].present?
  @all = Event.find([@cat, @dom, @eli].reject(&:blank?).collect(&:event_ids).flatten.uniq)
  @user = current_user || User.new  
end

I think this could be refactored further too, there's still a lot of repetition.

EDIT: you asked about ordering the results by most popular first. Here's one way, it's probably not the most efficient and almost certainly not the prettiest. It uses 'order by field(columname, values)` which is a way to specify an ordering in sql.

def index 
  @cat = Category.find(params[:category][:category_id]) if params[:category][:category_id].present?
  @dom = Domain.find(params[:domain][:domain_id]) if params[:domain][:domain_id].present?
  @eli = Eligible.find(params[:eligible][:eligible_id]) if params[:eligible][:eligible_id].present?
  ids = [@cat, @dom, @eli].reject(&:blank?).collect(&:event_ids).flatten
  sorted_ids = ids.sort_by{|id| ids.select{|id2| id2 == id}.size}.reverse
  @all = Event.find(sorted_ids, :order => "field(id, #{sorted_ids.join(",")})" )
  @user = current_user || User.new  
end

Cool! Is there a way to sort out the end result on the basis of the event repetition? As far I know uniq merges the repetitive records. Does it sort too? — nk06, Mar 12 '14 at 11:06
Can you explain what you mean by `sort out the end result on the basis of the event repetition` please? — Max Williams, Mar 12 '14 at 11:47
So you want to order the events by how many times they appear in the — Max Williams, Mar 12 '14 at 13:38
Looks like that works only in mysql. I tried this: ids = [cat, dom, eli].reject(&:blank?).collect(&:event_ids).flatten sorted_ids = ids.sort_by{|id| ids.select{|id2| id2 == id}.size}.reverse.uniq records = Event.find(sorted_ids).group_by(&:id) all_events = sorted_ids.map { |id| records[id].first } w.r.t this post [link](http://stackoverflow.com/questions/801824/clean-way-to-find-activerecord-objects-by-id-in-the-order-specified) It works perfectly. — nk06, Mar 12 '14 at 16:43

score 0 · Answer 2 · answered Mar 12 '14 at 10:54

This is the error:

ActionView::Template::Error (PG::Error: ERROR:  syntax error at or near ")"
SELECT "events".* FROM "events"  WHERE "events"."id" IN ())

We had a similar issue before -- it's basically that PGSql is not as forgiving as MYSQL or SQLite etc. I would say the best way to fix this is to refactor your code:

def index
   cat = params[:category][:category_id]
   domain = params[:domain][:domain_id]
   eli = params[:eligible][:eligible_id]

   queries = [cat, domain, eli]
   queries.each_with_index do |query, i|
       if query.present?
           instance_variable_set("@#{query}", Category.find(query))

           e = instance_variable_get("@#{query}")
           instance_variable_set("@e_#{i}" = e.events.collect{|p| p.id}) if defined?(e)
       end
   end

   @all = Event.where(id: [@e_1, @e_2, @e_3])
   @user = current_user || User.new

end

This probably won't work at first, but with some fixing, I'm sure it will fix any of the issues you've been having

Issues with PG in heroku and model sort w.r.t the selected ids in rails

2 Answers2