CODEWITHSUNDEEP
ruby-on-railsruby
Darkisa
Darkisa

Reputation: 2047

Rails ActiveRecord::Relation Keeps Hitting the DB

I have a simple controller method: 

products = Product.where(name: name, color: color, size: size, available: true)

Which returns a Product::ActiveRecord_Relation object. I want to take the first object and pull out a field e.g. products.first.product_code

But this and some other methods that I tried re-query the database. I've tried:

products[0].product_code
products.take.product_code

All of which re-query the database, hitting the database twice. Once for the where and one to take the field. Is there a simple solution that won't hit the db?

What does work is converting the ActiveRecord to an array (products.to_a.[0].product_code) but this seems inefficient.

Below is the server log to show the two separate hits:

enter image description here

Here is my controller method for reference:

  def update_selection
    size = params[:size]
    color = params[:color]
    name = params[:product_name]
    products = Product.where(name: name, color: color, size: size, available: true)
    product_code = products.empty? ? 'sold out' : products.first.product_code
    respond_to do |format|
      format.json { render json: { count: products.length, code: product_code}}
    end
  end

Upvotes: 1

Views: 693

Answers (2)

Mitch
Mitch

Reputation: 546

My best guess is that you're not actually hitting the database when assigning to products. ActiveRecord relations are lazy, meaning they don't query the database until the moment a result of that query is needed.

To put it in terms of your issue - this line:

products = Product.where(name: name, color: color, size: size, available: true)

doesn't require any data to actually be pulled from the db, so the query isn't carried out.

products.first.product_code, on the other hand, will cause a query to fire.

EDIT:

I believe the issue is coming from using .exists? (which is an ActiveRecord method that always fires a query). If you're checking to see if the query returned any results, try using .present? or .any? instead of .exists?.

EDIT 2:

OK, thanks for posting the code. You've got a couple options here.

  1. Use .to_a:

products = Product.where(name: name, color: color, size: size, available: true).to_a

This loads all of the products into memory, which makes sense if you expect there to be a small number of products. This only fires one query.

  1. You can change this line to only fire one query:

product_code = products.first.try(:product_code) || 'sold out'

This is more memory-efficient (since you're loading a maximum of one product into memory), but uses two queries (products.count is the other one).

Upvotes: 6

David Aldridge
David Aldridge

Reputation: 52386

You might find this to be efficient, as it gets a SQL-based count, and then reruns a product query if required to get a single product.

Do you really want the "first" in the sense of the one with the lowest id? If so then use first, otherwise use take.

def update_selection
  size          = params[:size]
  color         = params[:color]
  name          = params[:product_name]
  products      = Product.where(name: name, color: color, size: size, available: true)
  product_count = products.count
  product_code  = product_count.zero? ? 'sold out' : products.take.product_code
  respond_to do |format|
    format.json { render json: { count: product_count, code: product_code}}
  end
end

Upvotes: 1

Related Questions