2
\$\begingroup\$

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. How can I improve this?

  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
\$\endgroup\$

1 Answer 1

Reset to default
5
\$\begingroup\$

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')
\$\endgroup\$
12
  • \$\begingroup\$ 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 \$\endgroup\$ Commented Nov 28, 2012 at 16:06
  • 2
    \$\begingroup\$ 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. \$\endgroup\$ Commented Nov 28, 2012 at 16:22
  • \$\begingroup\$ Thanks for getting back to me. It doesn't seem to work. More info posted above :) \$\endgroup\$ Commented Nov 28, 2012 at 18:31
  • \$\begingroup\$ @user15872 What does Cause.select('causes.id,count(users) as people').joins(:users).group('causes.id').first.people output? \$\endgroup\$ Commented Nov 29, 2012 at 0:44
  • 1
    \$\begingroup\$ 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 \$\endgroup\$ Commented Nov 29, 2012 at 16:52

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.