sql - 将选择添加到模型会删除 .count 功能(导轨)

Question

创建连接表时如下

class Project < ActiveRecord::Base
  belongs_to :user
  has_many :time_pledges, dependent: :destroy
  has_many :volunteers, through: :time_pledges

class User < ActiveRecord::Base
  has_many :projects, dependent: :destroy
  has_many :time_pledges, foreign_key: "volunteer_id", dependent: :destroy
  has_many :volunteering, through: :time_pledges, source: :project

class TimePledge < ActiveRecord::Base
  belongs_to :volunteer, class_name: "User"
  belongs_to :project

我能够执行以下操作 @project.volunteers.count 并得到当时有多少用户志愿参与特定项目的答案。但是，当我更新项目模型以便能够访问连接表模型 (time_pledges) 的 hours_pledged 属性时，如下所示:

  has_many :volunteers, -> { select('users.*, time_pledges.hours_pledged as hours_pledged')}, through: :time_pledges

我无法再访问@project.volunteers.count. 我得到的错误如下

 P1.volunteers
  User Load (1.6ms)  SELECT users.*, time_pledges.hours_pledged as hours_pledged FROM "users" INNER JOIN "time_pledges" ON "users"."id" = "time_pledges"."volunteer_id" WHERE "time_pledges"."project_id" = $1  [["project_id", 300]]
=> #<ActiveRecord::Associations::CollectionProxy [#<User id: 1, email: "durham@example.com", encrypted_password: "$2a$10$4fyCd4GGtwZ0NRzrJuPDd.KWAhXWWumJ1LqtqZOSYWQ...", reset_password_token: nil, reset_password_sent_at: nil, remember_created_at: nil, sign_in_count: 0, current_sign_in_at: nil, last_sign_in_at: nil, current_sign_in_ip: nil, last_sign_in_ip: nil, created_at: "2015-03-06 08:59:06", updated_at: "2015-03-06 08:59:06">]>
>> P1.volunteers.count
PG::SyntaxError: ERROR:  syntax error at or near "as"
LINE 1: SELECT COUNT(users.*, time_pledges.hours_pledged as hours_pl...
                                                         ^
: SELECT COUNT(users.*, time_pledges.hours_pledged as hours_pledged) FROM "users" INNER JOIN "time_pledges" ON "users"."id" = "time_pledges"."volunteer_id" WHERE "time_pledges"."project_id" = $1
ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at or near "as"
LINE 1: SELECT COUNT(users.*, time_pledges.hours_pledged as hours_pl...
                                                         ^
: SELECT COUNT(users.*, time_pledges.hours_pledged as hours_pledged) FROM "users" INNER JOIN "time_pledges" ON "users"."id" = "time_pledges"."volunteer_id" WHERE "time_pledges"."project_id" = $1

此外，在我尝试执行 @project.volunteers.count 之后，我无法再访问我的任何用户(直到我重新启动控制台 session )。例如，如果我在 @project.volunteers.count 之后执行类似 U5=User.find_by(id:5) 的操作，我会收到以下消息:

User Load (1.6ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 5]]
PG::InFailedSqlTransaction: ERROR:  current transaction is aborted, commands ignored until end of transaction block
: SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1
ActiveRecord::StatementInvalid: PG::InFailedSqlTransaction: ERROR:  current transaction is aborted, commands ignored until end of transaction block
: SELECT  "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1

感谢任何帮助谢谢

编辑 1

架构

  create_table "time_pledges", force: :cascade do |t|
    t.integer  "volunteer_id"
    t.integer  "project_id"
    t.integer  "hours_pledged"
    t.datetime "created_at",    null: false
    t.datetime "updated_at",    null: false
  end

  add_index "time_pledges", ["project_id"], name: "index_time_pledges_on_project_id", using: :btree
  add_index "time_pledges", ["volunteer_id", "project_id"], name: "index_time_pledges_on_volunteer_id_and_project_id", unique: true, using: :btree
  add_index "time_pledges", ["volunteer_id"], name: "index_time_pledges_on_volunteer_id", using: :btree

使用 times_pledges 而不是 time_pledges

  >> P1=Project.first Project Load (1.7ms) SELECT "projects".* FROM "projects" ORDER BY "projects"."created_at" DESC LIMIT 1 => #<Project id: 301, title: "awe", user_id: 101, created_at: "2015-03-06 15:19:17", updated_at: "2015-03-06 15:19:17", required_hours: 7> >> P1.volunteers NameError: uninitialized constant Project::TimesPledge

使用

has_many :volunteers, -> { select('users.*, time_pledges.hours_pledged hours_pledged')}, through: :time_pledges

给予

    >> P1.volunteers    
  User Load (1.4ms)  SELECT users.*, time_pledges.hours_pledged hours_pledged FROM "users" INNER JOIN "time_pledges" ON "users"."id" = "time_pledges"."volunteer_id" WHERE "time_pledges"."project_id" = $1  [["project_id", 301]]
    => #<ActiveRecord::Associations::CollectionProxy []>
    >> P1.volunteers.count
    PG::SyntaxError: ERROR:  syntax error at or near "hours_pledged"
    LINE 1: SELECT COUNT(users.*, time_pledges.hours_pledged hours_pledg...
                                                             ^
    : SELECT COUNT(users.*, time_pledges.hours_pledged hours_pledged) FROM "users" INNER JOIN "time_pledges" ON "users"."id" = "time_pledges"."volunteer_id" WHERE "time_pledges"."project_id" = $1
    ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at or near "hours_pledged"
    LINE 1: SELECT COUNT(users.*, time_pledges.hours_pledged hours_pledg...
                                                             ^
    : SELECT COUNT(users.*, time_pledges.hours_pledged hours_pledged) FROM "users" INNER JOIN "time_pledges" ON "users"."id" = "time_pledges"."volunteer_id" WHERE "time_pledges"."project_id" = $1

Answer 1

在使用 Postgresql 时，我不使用 AS，除非它用于我要加入的选择语句，即

SELECT parts.*, alias_skus
  FROM parts
    LEFT JOIN (
       SELECT part_id, array_agg(sku) alias_skus
       FROM part_aliases GROUP BY part_id)
  AS sub1
  ON parts.id = sub1.part_id;

对于列别名，只需省略“AS”。你可以在这里看到我正在使用别名 alias_skus。

因此:

  has_many :volunteers, -> { select('users.*, time_pledges.hours_pledged hours_pledged')}, through: :time_pledges