how to reduce number of similar queries

I'm making a very basic analytics feature for my Rails app. I want to provide statistics that tell users how many visitors have viewed their profile, and then also break those down according to the particular role (I'm using 'rolify' in my app) each visitor has.

In the show action of the Users controller, I do this

@profileviews = Profileview.where(:user_id => @user.id)
@profileviewsbysomerole = Profileview.where({:user_id => @user.id, :viewer_role => 'someRole'})
@profileviewsbysomeotherrole = Profileview.where({:user_id => @user.id, :viewer_role => 'someOtherRole'})

and then in the show action, I'd do

Your profile has been viewed <%= @profileviews.size %> times.
Your profile has been viewed by users with a particular role <%= @profileviewsbysomerole.size %> times.
Your profile has been viewed by users with some other role <%= @profileviewsbysomeotherrole.size %> times.

Is there a way to accomplish what I'm trying to do without making three separate queries, or is this the best way (in terms of not degrading performance) to obtain these stats.

Answers


In terms of performance I think it is OK. The other option would be to query for all the objects and then filter in memory, but I don't think this is a good idea. It's best to let the database do what it does best.

One thing that comes to mind -- you could use a single query and group_by in order to avoid making the second and third calls, but that's relevant if you're looking to get some aggregated data.

In terms of coding style, you could define scopes for the different queries and use them from the view, but it might be an overkill.


If you find yourself using these things together a lot, it might be nice to bundle them up together something like this:

# in user model
def profile_view_hash(*roles)
  views = { 'all' => Profileview.where(:user_id => id).all }
  roles.each do |role|
    views.merge!({
      role => Profileview.where(:user_id => id, :viewer_role => role).all
    })
  end
  views
end

This should allow you to use it like this:

# in controller
@profile_views = @user.profile_view_hash('someRole','someOtherRole')

# in view
<%= @profile_views['all'] %>
<%= @profile_views['someRole'] %>
<%= @profile_views['someOtherRole'] %>

By the way, in your example, you are only showing the output of .size. If that's all you need, you should be using count instead of all


I would leave them all out of the controller, and make the view like this:

Your profile has been viewed <%= @user.profileviews.size %> times.
Your profile has been viewed by users with a particular role <%= @user.profileviews.select { |profile_view| profile_view.viewer_role == 'someRole' }.size %> times.
Your profile has been viewed by users with some other role <%= @user.profileviews.select { |profile_view| profile_view.viewer_role == 'someOtherRole' }.size %> times.

You could take this even further by moving this logic to the Profileview, maybe like:

def views_for_role( role )
  select { |profile_view| profile_view.viewer_role == role }.size
end

and delegating it in the User, maybe like:

delegate :views_for_role, :to => :profileview

Which would make your view look like this:

Your profile has been viewed <%= @user.profileviews.size %> times.
Your profile has been viewed by users with a particular role <%= @user.views_for_role 'someRole' %> times.
Your profile has been viewed by users with some other role <%= @user.views_for_role 'someOtherRole' %> times.

Need Your Help

Applying effects to Images in sequece

jquery image effects fade

I started jQuery this morning (so I'm a noob) and it seemed promising at first but then this....

How to make a code reusable in jQuery?

javascript jquery wordpress

In my WordPress' projects, I'm using the following code again and again for many of my fields where I'm using a button to initiate the WordPress media uploader and on selection of the file I'm send...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.