ProtocolViolation: ERROR: bind messages with parameters 0, but prepared statement "" requires 1
I am trying to create a list of the unique patients who left comments, in order of the patients who left the last comment first.
This is my Ruby.erb code for creating a list:
@comment_list.order("created_at desc").each_with_index do |comment, index|
@comment_list is defined in the controller like this:
@comments = current_clinician.comments.select('ON (patient_id) *').uniq
@comments = @comments.order("patient_id, created_at DESC")
@comment_list = Comment.select('*').from("(#{@comments.to_sql}) sub")
I am getting ActiveRecord :: StatementInvalid message:
PG :: ProtocolViolation: ERROR: binds messages with 0 parameters, but the prepared statement "requires 1: SELECT * FROM (SELECT DISTINCT ON (patient_id) * FROM" comments "WHERE" comments "." Clinician_id "= $ 1 ORDER BY patient_id, created_at DESC) sub ORDER BY created_at desc
I tried to follow the answer to 24619117 and my result is a combination of this and the top answer 29660396 .
$ rails -v
Rails 4.1.8
$ ruby -v
ruby 2.2.1p85 (2015-02-26 revision 49769) [x86_64-darwin14]
$ psql --version
psql (PostgreSQL) 9.4.1
I am inexperienced with PostgresSQL and part of the problem is that I am using Ruby on Rails to get SQL and the methods are not straightforward. I used http://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-from
Suggestions please
source to share
In your case, it seems that since you are using @comments.to_sql
, you are pulling this prepared statement into your subquery without injecting a parameter into it. You can try including comment data like this:
@comments = current_clinician.comments.select('ON (patient_id) *').uniq.order("patient_id, created_at DESC").include(:comment)
@comment_list = @comments.include(:comment)
This issue also occurs because prepared statements are created in Rails and can be caused either by issues within Rails itself (issue with Rails # 15920 , which was fixed in Rails 4.2) or issues with various gems that help generate requests ( e.g . : Rails issue # 20236 ) or even by defining your model associations (Rails issues # 12852 ).
You can simply disable pre-made statements by adding a directive to your file database.yml
:
production:
adapter: postgresql
database: prod_dbname
username: prod_user
password: prod_pass
prepared_statements: false
But first you can check and make sure that you are not using unnecessary parameters in your model associations, for example:
class DashboardTab < ActiveRecord::Base
has_many :dashboard_tab_feeds, foreign_key: :dashboard_tab_id, dependent: :destroy
has_many :social_feeds, through: :dashboard_tab_feeds
end
class DashboardTabFeed < ActiveRecord::Base
belongs_to :social_feed
belongs_to :dashboard_tab
end
class SocialFeed < ActiveRecord::Base
has_many :dashboard_tab_feeds, foreign_key: :social_feed_id, dependent: :destroy
end
... which should just leave foreign_key
, like:
class DashboardTab < ActiveRecord::Base
has_many :dashboard_tab_feeds, dependent: :destroy
has_many :social_feeds, through: :dashboard_tab_feeds
end
class DashboardTabFeed < ActiveRecord::Base
belongs_to :social_feed
belongs_to :dashboard_tab
end
class SocialFeed < ActiveRecord::Base
has_many :dashboard_tab_feeds, dependent: :destroy
end
source to share