Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

Here I retrieve a collection of causes from the database. Along with the information from each cause, I want to send the number of users in each cause. This way works, but if experience has told me anything there is probably a much more efficient method to do this. Any advice would be greatly appreciated.

  def index
    @causes = Cause.near([params[:lat], params[:lng]],10)
    @causes.each do |i|
      i['count'] = i.users.count
    end
    respond_to do |format|
      format.json { render json: {:success=> 1, :data_type => 'cause',:results => @causes} }
    end
  end

Thanks!

Update:

I tried the solution below but it is not working.

  Cause.select('causes.id,count(users) as
  people').joins(:users).group('causes.id')

I can't get any of my aliases(AS) to show up. Results:

Cause Load (0.8ms)  SELECT causes.id,count(users) as people FROM "causes" INNER JOIN "cause_user_memberships" ON "cause_user_memberships"."cause_id" = "causes"."id" INNER JOIN "users" ON "users"."id" = "cause_user_memberships"."user_id" GROUP BY causes.id

 => [#<Cause id: 25>, #<Cause id: 27>, #<Cause id: 12>, #<Cause id: 19>, #<Cause id: 1>, #<Cause id: 2>, #<Cause id: 3>, #<Cause id: 23>, #<Cause id: 26>, #<Cause id: 11>, #<Cause id: 4>, #<Cause id: 20>, #<Cause id: 13>, #<Cause id: 24>] 

Any advice? I'm using postgres. Thanks again!

Update 2:

User.rb (snippet)

has_many :cause_user_memberships
has_many :causes, :through => :cause_user_memberships

Cause.rb

attr_accessible :title, :location, :description,...
has_many :cause_user_memberships
has_many :users, :through => :cause_user_memberships

Cause_User_Membership.rb (<--probably not my best model name)

    # == Schema Information
#
# Table name: cause_user_memberships
#
#  id         :integer          not null, primary key
#  user_id    :integer          not null
#  cause_id   :integer          not null
#  created_at :datetime         not null
#  updated_at :datetime         not null
#

class CauseUserMembership < ActiveRecord::Base
  attr_accessible :cause_id, :user_id

  belongs_to :user #, :class_name => "User", :foreign_key => "user_id" 
  belongs_to :cause #, :class_name => "Cause", :foreign_key => "cause_id"

end

Last Update: Success

EXPLAIN (1.3ms)  EXPLAIN SELECT causes.id,count(users) as people FROM "causes" INNER JOIN "cause_user_memberships" ON "cause_user_memberships"."cause_id" = "causes"."id" INNER JOIN "users" ON "users"."id" = "cause_user_memberships"."user_id" GROUP BY causes.id
EXPLAIN for: SELECT causes.id,count(users) as people FROM "causes" INNER JOIN "cause_user_memberships" ON "cause_user_memberships"."cause_id" = "causes"."id" INNER JOIN "users" ON "users"."id" = "cause_user_memberships"."user_id" GROUP BY causes.id
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 HashAggregate  (cost=51.26..51.36 rows=10 width=4212)
   ->  Hash Join  (cost=20.90..51.21 rows=10 width=4212)
         Hash Cond: (cause_user_memberships.cause_id = causes.id)
         ->  Hash Join  (cost=10.22..40.17 rows=70 width=4212)
               Hash Cond: (cause_user_memberships.user_id = users.id)
               ->  Seq Scan on cause_user_memberships  (cost=0.00..24.00 rows=1400 width=8)
               ->  Hash  (cost=10.10..10.10 rows=10 width=4212)
                     ->  Seq Scan on users  (cost=0.00..10.10 rows=10 width=4212)
         ->  Hash  (cost=10.30..10.30 rows=30 width=4)
               ->  Seq Scan on causes  (cost=0.00..10.30 rows=30 width=4)
(10 rows)

 => [{"id"=>25, "people"=>"1"}, {"id"=>27, "people"=>"2"}, ...]
share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

You can perform an inner-join on the users table and then group by the causes.id.

@causes = Cause
  .near([params[:lat], params[:lng]], 10)
  .joins('users')
  .group('causes.id')
  .select('causes.*, count(users) as count')
share|improve this answer
    
Thanks for the answer! I'm not trying to be a smart ass, but is this better or just different? Can you explain why? Thanks –  Emin Israfil Nov 28 '12 at 16:06
2  
This is more efficient because the number of queries to database is reduced to one. It also places the work onto the database which is optimized for this task through caching and indexing. –  peterpan Nov 28 '12 at 16:22
    
Thanks for getting back to me. It doesn't seem to work. More info posted above :) –  Emin Israfil Nov 28 '12 at 18:31
    
@user15872 What does Cause.select('causes.id,count(users) as people').joins(:users).group('causes.id').first.people output? –  peterpan Nov 29 '12 at 0:44
1  
You say its not in the hash but AR is not returning a hash it is returning a ActiveRecord::Relation. What it is printing to the screen is not necessarily what will be in the json. What does this show? Cause.select('causes.id,count(users) as people').joins(:users).group('causes.id').as_json –  peterpan Nov 29 '12 at 16:52
show 7 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.