Ask A Question

Notifications

You’re not receiving notifications from this thread.

Improve on these ActiveRecord requests?

Alan Reid asked in Rails

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) })
Reply

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.

Reply

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.

Reply

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.

Reply

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.

Reply
Join the discussion
Create an account Log in

Want to stay up-to-date with Ruby on Rails?

Join 78,890+ developers who get early access to new tutorials, screencasts, articles, and more.

    We care about the protection of your data. Read our Privacy Policy.

    Screencast tutorials to help you learn Ruby on Rails, Javascript, Hotwire, Turbo, Stimulus.js, PostgreSQL, MySQL, Ubuntu, and more.

    © 2023 GoRails, LLC. All rights reserved.