What's a database efficient way to check whether an object has one or more children?

I'd like to check whether an object contains one or more children:

if @question.replies.count > 0 
  // do this
else
  // do that
end

however I'd rather not do the db lookups involved in this. Is there a nice efficient way of doing this query?

Answers


Just use :counter_cache ( http://asciicasts.com/episodes/23-counter-cache-column )


If you've already loaded replies through an include, you can use @question.replies.length or @question.replies.size (more info here). You can also simplify your if statement to

if @question.replies.present?

Otherwise, you can use a counter_cache column, which will just maintain a column on your questions table (replies_count) that you can access at any time.


If you have eager loaded associations, then use the present? method

class User
  has_many :messages
end

class Message
  belongs_to :user
  has_many   :replies
end

current_user.messages.all(:include => :replies).each do |message|
  if message.replies.present?
  else
  end
end

If you haven't loaded the associations to memory, then use the exists? method.

current_user.messages.each do |message|
  if message.replies.exists?
  else
  end
end

The exists? call results in EXISTS check which returns upon finding the first matching row without incurring the cost of getting the row data back from DB.

Note

Other option is to use the counter_cache approach as suggested by @dimuch. The counter_cache solution is efficient if you can alter your DB table structure.


Need Your Help

linear solution of grid constraints

algorithm dynamic-programming pseudocode greedy max-flow

You have a grid n x n with n rows and n columns. For every column j you are given a number Cj and for every row i you are given a number Ri.

KeyTyped events - Why can I enter a character but when I delete I cannot enter anymore?

java swing

I use this code to restrict the input only for numbers, but if the first key I press is a letter, the code let me enter that letter, only one time, then when I erase it I cannot input anymore lette...