CODEWITHSUNDEEP
activerecordarelruby-on-rails-3
empz
empz

Reputation: 11798

Rails 3 Query: How to get most viewed products/articles/whatever?

I always wondered how to query and get results that doesn't fit in a model. Similar how it's done using LINQ and projecting into anonymous objects.

So here's the simple schema:

# Product.rb
class Product < ActiveRecord::Base
   has_many :product_views

   # attributes: id, name, description, created_at, updated_at
end

# ProductView.rb
class ProductView < ActiveRecord::Base
   belongs_to :product

   # attributes: id, product_id, request_ip, created_at, updated_at
end

Basically I need to get a list of Products (preferably just id and name) along with the count of views it had. Obviously ordered by view count desc.

This is the SQL I want to get:

select 
    p.id,
    p.name,    
    count(pv.product_id) as views
from 
    product_views pv
inner join
    products p on pv.product_id = p.id
group by
    pv.product_id
order by
    count(product_id) desc

I tried the following and similar, but I'm getting ProductView objects, and I would like to get just an array or whatever.

ProductView.includes(:product)
           .group('product_id')
           .select("products.id, products.name, count(product_id)")

This kind of thing are trivial using plain SQL or LINQ, but I find myself stucked with this kind of queries in Rails. Maybe I'm not thinking in the famous 'rails way', maybe I'm missing something obvious.

So how do you do this kind of queries in Rails 3, and specifically this one? Any suggestions to improve the way I'm doing this are welcome.

Thank you

Upvotes: 1

Views: 1742

Answers (4)

MarkD
MarkD

Reputation: 454

Within a class method in the Product class:

Product.includes(:product_views).all.map { |p| [p.id, p.name, p.product_views.size] }

Then sort it however you want.

Upvotes: 2

Kelly
Kelly

Reputation: 41561

You can use Arel to do what you're looking for:

products = Product.arel_table
product_views = ProductView.arel_table

# expanded for readability:
sql = products.join(product_views)
              .on(product_views[:product_id].eq(product[:id]))
              .group(product_views[:product_id])
              .order('views DESC')
              .project(products[:id],
                       products[:name],
                       product_views[:id].count.as('views'))

products_with_views = Product.connection.select_all(sql.to_sql) # or select_rows to just get the values

Yes, it is long, but Arel is a very smart way to deal with creating complex queries that can be reused regardless of the database type.

Upvotes: 3

mdinstuhl
mdinstuhl

Reputation: 150

Try this:

@product = Product.find(#product_id)
@product_views = @product.product_views.count

(Source - http://ar.rubyonrails.org/classes/ActiveRecord/Calculations/ClassMethods.html#M000292)

Hope this helps!

Upvotes: -1

Austin Taylor
Austin Taylor

Reputation: 5477

I don't know if there's a way to do it using your models. I would probably resort to:

Product.connection.select_rows(sql)

Which will give you an array of arrays. You can use select_all if you'd rather have an array of hashes.

Upvotes: 0

Related Questions