问题描述
我正在尝试创建一个已发表评论的唯一患者列表,按照发表最新评论的患者的顺序排列.
I am trying to create a list of unique patients who have left comments, in order of patient who left the most recent comment first.
这是我创建列表的 Ruby .erb 代码:
This is my Ruby .erb code to create the list:
@comment_list.order("created_at desc").each_with_index do |comment, index|
@comment_list 在控制器中定义为:
@comment_list is defined in the controller as:
@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")
我收到一条 ActiveRecord::StatementInvalid 消息:
I get an ActiveRecord::StatementInvalid message:
PG::ProtocolViolation: 错误:绑定消息提供 0 个参数,但准备好的语句"需要 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
我试图按照 24619117 我的输出是这个和答案的组合 29660396.
I tried to follow the answer on 24619117 and my output is a combination of this and the answer top 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
我对 PostgresSQL 缺乏经验,部分问题是我使用 Ruby on Rails 来获取 SQL,而且方法并不简单.我一直在使用 http://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-from
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 have been using http://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-from
请提出建议
推荐答案
在您的情况下,似乎因为您使用的是 @comments.to_sql
您将准备好的语句拉入您的子选择中而没有带来在它的参数中.您可以尝试只包含这样的评论数据:
In your case it seems that because you are using the @comments.to_sql
you are pulling that prepared statement into your subselect without bringing in the parameter for it. You can try just including the 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)
这个问题似乎也来自于在 Rails 中构建准备好的语句的方式,并且可能是由 Rails 本身的任何一个问题引起的(Rails 问题 #15920,已在 Rails 4.2 中修复)或各种有助于生成查询的 gem 问题(例如:Rails 问题 #20236),甚至通过定义模型关联的方式(Rails 问题 #12852).
This issue also seems to come from the way that the prepared statements are built in Rails and could be caused by either issues within Rails itself (Rails issue #15920, which has been fixed in Rails 4.2) or by issues with various gems that help to generate queries (example: Rails issue #20236) or even by the way you define your model associations (Rails issues #12852).
可以通过在 database.yml
文件中添加指令来彻底禁用准备好的语句:
It is possible to just outright disable prepared statements by adding a directive to your database.yml
file:
production:
adapter: postgresql
database: prod_dbname
username: prod_user
password: prod_pass
prepared_statements: false
但首先,您可能需要检查并确保您没有在模型关联中使用不必要的参数,如下所示:
But first, you might want to check and make sure you aren't using unnecessary parameters in your model associations like this:
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
...应该省略foreign_key
,像这样:
...which should just leave out foreign_key
, like this:
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
这篇关于ProtocolViolation:错误:绑定消息提供 0 个参数,但准备好的语句“"需要 1的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!