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

TestNG, factory and parametrized Test description

java testing testng factory parameterized

I just learned that I can nest TestNG factories. Yay! However I don't like that all test names are same. This is my example:

How to find the cause of blocking finalizer in .NET?

.net blocking finalizer

In Java I need only to press a single key (ctrl-break) to see the stacktrace of a blocking finalizer.