Rails checking if a record exists in database

Question

What is the most efficient of way of checking if a database will return a record before processing it. Example: Truck.where("id = ?", id).select('truck_no').first.truck_no

This may or may not return a truck if the truck exists. What is the most efficient way for me to ensure the page will not crash when processing this request. How would I handle this both in the view and the controller if lets say I was using a loop to go through each truck and print out its number.

If the record does not exist I would like to be able to print out a message instead saying no records found.

Answer 1

If you want to check for the existence of an object why not use exists?

if Truck.exists?(10)
  # your truck exists in the database
else
  # the truck doesn't exist
end

The exists? method has the advantage that is not selecting the record from the database (meaning is faster than selecting the record). The query looks like:

SELECT 1 FROM trucks where trucks.id = 10

You can find more examples in the Rails documentation for #exists?.

Answer 2

Here is how you can check this.

if Trucks.where(:id => current_truck.id).blank?
  # no truck record for this id
else
  # at least 1 record for this truck
end

where method returns an ActiveRecord::Relation object (acts like an array which contains the results of the where), it can be empty but never be nil.

Answer 3

OP actual use case solution

The simplest solution is to combine your DB check and retrieval of data into 1 DB query instead of having separate DB calls. Your sample code is close and conveys your intent, but it's a little off in your actual syntax.

If you simple do Truck.where("id = ?", id).select('truck_no').first.truck_no and this record does NOT exists, it will throw a nil error when you call truck_no because first may retrieve a nil record if none are found that match your criteria.

That's because your query will return an array of objects that match your criteria, then you do a first on that array which (if no matching records are found) is nil.

A fairly clean solution:

# Note: using Rails 4 / Ruby 2 syntax
first_truck = Truck.select(:truck_no).find_by(id) # => <Truck id: nil, truck_no: "123"> OR nil if no record matches criteria

if first_truck
  truck_number = first_truck.truck_no
  # do some processing...
else
  # record does not exist with that criteria
end

I recommend using clean syntax that "comments" itself so others know exactly what you're trying to do.

If you really want to go the extra mile, you could add a method to your Truck class that does this for you and conveys your intent:

# truck.rb model
class Truck < ActiveRecord::Base
  def self.truck_number_if_exists(record_id)
    record = Truck.select(:truck_no).find_by(record_id)
    if record
      record.truck_no
    else
      nil # explicit nil so other developers know exactly what's going on
    end
  end
end

Then you would call it like so:

if truck_number = Truck.truck_number_if_exists(id)
  # do processing because record exists and you have the value
else
  # no matching criteria
end

The ActiveRecord.find_by method will retrieve the first record that matches your criteria or else returns nil if no record is found with that criteria. Note that the order of the find_by and where methods is important; you must call the select on the Truck model. This is because when you call the where method you're actually returning an ActiveRelation object which is not what you're looking for here.

See ActiveRecord API for 'find_by' method

General solutions using 'exists?' method

As some of the other contributors have already mentioned, the exists? method is engineered specifically to check for the existence of something. It doesn't return the value, just confirms that the DB has a record that matches some criteria.

It is useful if you need to verify uniqueness or accuracy of some piece of data. The nice part is that it allows you to use the ActiveRelation(Record?) where(...) criteria.

For instance, if you have a User model with an email attribute and you need to check if an email already exists in the dB:

User.exists?(email: "test@test.com")

The benefit of using exists? is that the SQL query run is

SELECT 1 AS one FROM "users" WHERE "users"."email" = 'test@test.com' LIMIT 1

which is more efficient than actually returning data.

If you need to actually conditionally retrieve data from the DB this isn't the method to use. However, it works great for simple checking and the syntax is very clear so other developers know exactly what you're doing. Using appropriate syntax is critical in projects with multiple developers. Write clean code and let the code "comment" itself.

Answer 4

If you just want to check whether the record exists or not. Go with the @cristian's answer i.e.

Truck.exists?(truck_id) # returns true or false

But if truck exists and you want to access that truck then you will have to find truck again which will lead to two database queries. If this is the case go with

@truck = Truck.find_by(id: truck_id) #returns nil or truck
@truck.nil? #returns true if no truck in db
@truck.present? #returns true if no truck in db

Answer 5

You could just do:

@truck_no = Truck.where("id = ?", id).pluck(:truck_no).first

This will return nil if no record is found, or truck_no of only the first record otherwise.

Then in your view you could just do something like:

<%= @truck_no || "There are no truck numbers" %>

If you want to fetch and display multiple results, then in your controller:

@truck_nos = Truck.where("id = ?", id).pluck(:truck_no)

and in your view:

<% truck_nos.each do |truck_no| %>
  <%= truck_no %>
<% end %>

<%= "No truck numbers to iterate" if truck_nos.blank? %>

Answer 6

Rails has a persisted? method for using like you want