CODEWITHSUNDEEP
ruby-on-railsactiverecord
Andrei Statescu
Andrei Statescu

Reputation: 329

Avoid making duplicate queries in ActiveRecord - Ruby on Rails

I have piece of code with a few simple queries and I am trying to optimize it by compacting them into one single query. But I have no idea how.

posts = Post.where(category: "article")
num_posts = posts.count
first_post = posts.first
posts_with_updated_name = posts.map { |post| post[:name] = "UPDATE_" + post[:name] }

What I wished, was to have a single query that will be performed on the first line and then posts be an array on which to perform the operations on lines 2, 3 and 4. Instead, what I get is that I have 3 separate queries. Any idea how to optimize this into a single query?

LE: You can assume that num_posts, first_post and posts_with_updated_name are just variables that I'm going to need later on

Upvotes: 0

Views: 641

Answers (2)

Jay-Ar Polidario
Jay-Ar Polidario

Reputation: 6603

Because converting an ActiveRecord::Relation object into an Array can potentially consume high amount of memory (and may even ran out of memory) for a big database, I would do the following, which would still use (2 + number-of-batches) SQL queries, but that your application would scale in the long-run.

posts = Post.where(category: "article")
num_posts = posts.count
first_post = posts.first

# || is a PostgreSQL operator for string concatenation
# if you are using MySQL check out CONCAT instead
posts_with_updated_name = posts.select('posts.*', "'UPDATE_' || name AS updated_name")

# example usage
posts_with_updated_name.find_each do |post|
  puts post.id
  puts post.name
  puts post.updated_name
end

# example output:
# 6
# 'Spider Man is Back'
# 'UPDATE_Spider Man is Back'
# 84
# 'Hello World'
# 'UPDATE_Hello World'

Recommended Solution

1. Temporary "Updated Name"

I wouldn't store the "updated name" into the variable, but that I will append/prepend the string only by demand, as follows:

posts = Post.where(category: "article")
num_posts = posts.count
first_post = posts.first

# somewhere in your code where you are actually already gonna be looping over `posts`
posts.find_each do |post|
  updated_name = "UPDATE_#{post.name}'"
  # do something here about updated name
end

2. Permanent "Updated Name"

Or... if this "Updated Name" value is a permanent logic across the application, then just simply write a method in the model:

app/models/post.rb

class Post < ApplicationRecord
  # ...

  def updated_name
    "UPDATE_#{name}"
  end
end

Then in your code you'll just do the following without the need of setting up the value for "updated name",

posts = Post.where(category: "article")
num_posts = posts.count
first_post = posts.first

# example:
puts first_post.updated_name
# => 'UPDATE_Hello World'

Upvotes: 2

Jose Manuel
Jose Manuel

Reputation: 183

I'm not sure if I understand your question well, but I think you want to transform the ActiveRecord collection into an array? If that's so, you can do it with posts = Post.where(category: "article").to_a

Upvotes: 1

Related Questions