Postgresql database query to Ruby active record

Question

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;

are you looking to have the query in rails return objects, or do you want just the raw results set back? if the latter, check out the execute method for method the connection in active record. — Doon
– Doon, Commented Jun 6, 2014 at 5:33
i need exact converted ruby code which i can use instead of find_by_sql(".....") — Joe Sebin
– Joe Sebin, Commented Jun 6, 2014 at 5:55
<--audit_status table--> id - integer, name -string <--audit table--> id – integer, audit_status_id -integer — Joe Sebin
– Joe Sebin, Commented Jun 6, 2014 at 6:46
No, I mean association on your models. audit belongs_to audit_status, and audit_status has_many audits ? — rails_id
– rails_id, Commented Jun 6, 2014 at 7:48

rails_id · Accepted Answer · 2014-06-06 08:45:47Z

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

thanks for u r reply.but i solvethe problem in another way.am adding dat also
@user3713745 : But the best practice like your case seems to be use counter_cache. It does exactly what you want to do, and it is built-in into ActiveRecord instead Audit.joins( :audit_status).select("name as status,count(audit_status_id) as count").group(:name)

Kick Buttowski · Accepted Answer · 2018-06-05 04:28:25Z

0

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

edited Jun 5, 2018 at 4:28

Kick Buttowski

6,74713 gold badges39 silver badges61 bronze badges

answered Jun 7, 2014 at 10:36

Joe Sebin

4606 silver badges24 bronze badges

Collectives™ on Stack Overflow

Postgresql database query to Ruby active record

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related