Skip to main content

Improve on these ActiveRecord requests?

Rails • Asked by Alan Reid

I am trying to get the top 5 products, based on the impressions that product has recieved.

Now the code below is working ok, but its not in the right order, and I cant work out a better way to get the impressions.

@products.joins("INNER JOIN products ON products.id = I.product_id")
.from(Impression.select("product_id, COUNT(product_id) as visits")
.group("product_id").limit(5).order("visits DESC"),:I)

Also, while i am here, is there also a more effiecient way to write out the getting of the total impressions for the last 30 days, based on a selection of products? So far i have the following...

Once again, it works ok, but it feels a little cumbersome. Maybe there is a i could combine both?

@impressions = Impression.joins(:product)
.where('impressions.created_at > ?', 30.days.ago)
.where(products: { id: @products.pluck(:id) })

You might try adding your own caches to the Product table. You could have a nightly or hourly cron update the cache so it isn't constantly being changed, but still getting regular updates.


Do you mean have a column in the Products table which houses the count figure, and have a job to do the calculation each night, and update that figure?

That would be fine, but I may need to get the impressions for the last 7 days, 30 days, 6 months or even a year. So storing it may not be the mode reusable solution.

I have 2 tables, Products and Impressions which are related, and impressions belong to products.

maybe i need to get all products, and include the impressions count? that way it should be more efficient.


Yeah, just adding a column like visits_last_30_days:integer and having it update every hour or night. You'd just query the impressions, sum up the total and save it to that column each time.

If you had a few common options you could cache multiple values so those common ones were super fast. Then fallback to doing the JOIN query for custom ranges which will be a little slower.

You should be able to query the Product table and join the impressions and use that for sorting. Here's an example of doing that: https://stackoverflow.com/questions/22234983/rails-activerecord-sort-by-count-of-join-table-associations

Skipping the cache will help you keep things simpler, since you'll always need the fallback join query.


I will give this a try, but i get the idea of having last 30 days as the default. It makes sense.

I could use JSONB to store the visits i suppose, and just update them every few hours.

Cheers Chris, given me something to think about this evening.


Login or Create An Account to join the conversation.

Subscribe to the newsletter

Join 22,346+ developers who get early access to new screencasts, articles, guides, updates, and more.

    By clicking this button, you agree to the GoRails Terms of Service and Privacy Policy.

    More of a social being? We're also on Twitter and YouTube.