CODEWITHSUNDEEP
ruby-on-railspostgresql
Joe Sebin
Joe Sebin

Reputation: 450

Postgresql database query to Ruby active record

There are tables audits and audit_statuses, and need to fetch the data which are not common in both. I already have a query that works in postgresql , how to convert convert it into Ruby active record .

the following is the Psql query

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;

Upvotes: 0

Views: 607

Answers (2)

Joe Sebin
Joe Sebin

Reputation: 450

@audit_status = Audit.joins( :audit_status).select("name as status,count(audit_status_id) as count").group(:name)

Upvotes: 0

rails_id
rails_id

Reputation: 8220

Your association models are Audit belongs_to Audit_status, and Audit_status has_many Audits, aren't there? And You want to get counts how many Audit_statuses owned by Audit, won't you?

If yes, you should use counter_cache read this about belongs_to Association Reference #counter_cache

Although the :counter_cache option is specified on the model that includes the belongs_to declaration, the actual column must be added to the associated model. In the case above, you would need to add a column named count_audit to the Audit_status model

Add count_audit attribute to audit_statuses table by running :

rails g migration AddCountAuditToAuditStatuses count_audit:integer

and run rake db:migrate

On your models looks like :

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

Example create one record of 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">

And create two records of audit, and get audit_status.id for 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">

So, you only call audit_status's records looks like :

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">]

Upvotes: 2

Related Questions