Postgresql数据库查询到Ruby活动记录

debugcn 发表于 Dev

乔·塞宾

有表audits和audit_statuses，需要获取两者中不常见的数据。我已经有一个在postgresql中有效的查询，如何将其转换为Ruby活动记录。

以下是Psql查询

SELECT a.name as status,count(b.audit_status_id) as count 
FROM audit_statuses as a,audits as b 
WHERE a.id=b.audit_status_id 
GROUP BY a.name;

rails_id

您的关联模型是Audit belongs_toAudit_status和Audit_status Audits has_many，不是吗？而且您想获得审计所拥有的Audit_statuses的数量，不是吗？

如果是，则应该使用counter_cache有关属地关联的参考书#counter_cache的阅读方法

虽然：counter_cache选项是在包含属地声明的模型上指定的，但实际的列必须添加到关联的模型中。在上述情况下，您需要向Audit_status模型添加一个名为count_audit的列

通过运行将count_audit属性添加到audit_statuses表：

rails g migration AddCountAuditToAuditStatuses count_audit:integer

并运行 rake db:migrate

在您的模型上看起来像：

class Audit < ActiveRecord::Base
  attr_accessible :audit_status_id, :status
  belongs_to :audit_status, :counter_cache => :count_audit
end

class AuditStatus < ActiveRecord::Base
  attr_accessible :count_audit, :name

  has_many :audits
end

示例创建一条记录 audit_status

irb(main):001:0> audit_status = AuditStatus.create!(:name => "John Noe")
  ←[1m←[36m (0.0ms)←[0m  ←[1mBEGIN←[0m
  ←[1m←[35mSQL (114.0ms)←[0m  INSERT INTO "audit_statuses" ("count_audit", "created_at", "name", "up
dated_at") VALUES ($1, $2, $3, $4) RETURNING "id"  [["count_audit", nil], ["created_at", Fri, 06 Jun
 2014 15:17:49 WIB +07:00], ["name", "John Noe"], ["updated_at", Fri, 06 Jun 2014 15:17:49 WIB +07:0
0]]
  ←[1m←[36m (17.0ms)←[0m  ←[1mCOMMIT←[0m
=> #<AuditStatus id: 1, name: "John Noe", count_audit: nil, created_at: "2014-06-06 08:17:49", updat
ed_at: "2014-06-06 08:17:49">

并建立审计的两项纪录，并获得audit_status.id了audit_status_id

irb(main):002:0> audit = Audit.create!({:audit_status_id => audit_status.id, :status => true})
  ←[1m←[35m (0.0ms)←[0m  BEGIN
  ←[1m←[36mSQL (6.0ms)←[0m  ←[1mINSERT INTO "audits" ("audit_status_id", "created_at", "status", "up
dated_at") VALUES ($1, $2, $3, $4) RETURNING "id"←[0m  [["audit_status_id", 1], ["created_at", Fri,
06 Jun 2014 15:19:00 WIB +07:00], ["status", true], ["updated_at", Fri, 06 Jun 2014 15:19:00 WIB +07
:00]]
  ←[1m←[35mAuditStatus Load (1.0ms)←[0m  SELECT "audit_statuses".* FROM "audit_statuses" WHERE "audi
t_statuses"."id" = 1 LIMIT 1
  ←[1m←[36mSQL (2.0ms)←[0m  ←[1mUPDATE "audit_statuses" SET "count_audit" = COALESCE("count_audit",
0) + 1 WHERE "audit_statuses"."id" = 1←[0m
  ←[1m←[35m (1.0ms)←[0m  COMMIT
=> #<Audit id: 1, audit_status_id: 1, status: true, created_at: "2014-06-06 08:19:00", updated_at: "
2014-06-06 08:19:00">
irb(main):003:0> audit = Audit.create!({:audit_status_id => audit_status.id, :status => false})
  ←[1m←[36m (1.0ms)←[0m  ←[1mBEGIN←[0m
  ←[1m←[35mSQL (1.0ms)←[0m  INSERT INTO "audits" ("audit_status_id", "created_at", "status", "update
d_at") VALUES ($1, $2, $3, $4) RETURNING "id"  [["audit_status_id", 1], ["created_at", Fri, 06 Jun 2
014 15:19:23 WIB +07:00], ["status", false], ["updated_at", Fri, 06 Jun 2014 15:19:23 WIB +07:00]]
  ←[1m←[36mAuditStatus Load (1.0ms)←[0m  ←[1mSELECT "audit_statuses".* FROM "audit_statuses" WHERE "
audit_statuses"."id" = 1 LIMIT 1←[0m
  ←[1m←[35mSQL (0.0ms)←[0m  UPDATE "audit_statuses" SET "count_audit" = COALESCE("count_audit", 0) +
 1 WHERE "audit_statuses"."id" = 1
  ←[1m←[36m (0.0ms)←[0m  ←[1mCOMMIT←[0m
=> #<Audit id: 2, audit_status_id: 1, status: false, created_at: "2014-06-06 08:19:23", updated_at:
"2014-06-06 08:19:23">

因此，您只调用audit_status的记录，如下所示：

irb(main):004:0> @audit_statuses = AuditStatus.all
  ←[1m←[35mAuditStatus Load (1.0ms)←[0m  SELECT "audit_statuses".* FROM "audit_statuses"
=> [#<AuditStatus id: 1, name: "John Noe", count_audit: 2, created_at: "2014-06-06 08:17:49", update
d_at: "2014-06-06 08:17:49">]

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。