nk06
Reputation: 69
Issues with PG in heroku and model sort w.r.t the selected ids in rails
>----< 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.
Upvotes: 0
Views: 110
Answers (2)
Max Williams
Reputation: 32945
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
Upvotes: 1
Richard Peck
Reputation: 76784
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
Upvotes: 0
Related Questions
- Why primary key ids are not in sequence?
- Order with DISTINCT ids in rails with postgres
- Rails: Records are not ordered by id in heroku
- PG error for SELECT DISTINCT, ORDER BY expressions must appear in select list when trying to view on Heroku
- Heroku PG error on select
- Sorting issues on Heroku
- Postgres: Error using GROUP BY and ORDER (on heroku)
- order of records is not proper in heroku
- ORDER BY issue in Postgres (Heroku)
- Postgres sorting problem