In Rails, if you want to count the number of records returned in one query seems very simple, just call the functions available as .count
, .size
, .length
seems you will have the results immediately. Why does one count the number of records that must have so many functions? Today, I would like to share about how the above functions work?
The .count
function
count
is a function of Ruby, it returns the number of elements in an array. However, ActiveRecord :: Relation override the count
function to execute a COUNT()
statement in SQL
1 2 3 |
<span class="token constant">User</span> <span class="token punctuation">.</span> all <span class="token punctuation">.</span> count <span class="token comment"># SELECT COUNT(*) FROM `users`</span> |
The returned result will not be cached because each time we call .count
ActiveRecord :: Relation automatically executes for us a COUNT()
query COUNT()
1 2 3 4 5 6 7 8 |
users <span class="token operator">=</span> <span class="token constant">User</span> <span class="token punctuation">.</span> all users <span class="token punctuation">.</span> count <span class="token comment"># SELECT COUNT(*) FROM `users`</span> users <span class="token punctuation">.</span> count <span class="token comment"># SELECT COUNT(*) FROM `users`</span> |
The .size
function
Like the .count
function, the .size
function is also defined in Ruby (to count the number of elements in an array) and ActiveRecord :: Relation. However, in ActiveRecord :: Relation the .size
function is a little different.
When the records are not loaded, the .size
function .size
like .count
, it will execute a COUNT()
query COUNT()
1 2 3 4 5 |
users <span class="token operator">=</span> <span class="token constant">User</span> <span class="token punctuation">.</span> all users <span class="token punctuation">.</span> loaded <span class="token operator">?</span> <span class="token comment"># => nil</span> users <span class="token punctuation">.</span> size <span class="token comment"># SELECT COUNT(*) FROM `users` </span> |
However, the difference is that when the records are loaded earlier, the .size
function will count the number of elements without doing the query.
1 2 3 4 5 6 |
users <span class="token operator">=</span> <span class="token constant">User</span> <span class="token punctuation">.</span> all users <span class="token punctuation">.</span> load users <span class="token punctuation">.</span> loaded <span class="token operator">?</span> <span class="token comment"># => true</span> users <span class="token punctuation">.</span> size <span class="token comment"># => Return results</span> |
There is one case where even though the records are not loaded, .size
still returns the result without performing a COUNT()
query. It happens when we use counter_cache
1 2 3 4 5 6 7 8 9 10 11 12 |
<span class="token keyword">class</span> <span class="token class-name">Address</span> <span class="token operator"><</span> <span class="token constant">ActiveRecord</span> <span class="token punctuation">:</span> <span class="token punctuation">:</span> <span class="token constant">Base</span> belongs_to <span class="token symbol">:user</span> <span class="token punctuation">,</span> counter_cache <span class="token punctuation">:</span> <span class="token boolean">true</span> <span class="token keyword">end</span> <span class="token keyword">class</span> <span class="token class-name">User</span> <span class="token operator"><</span> <span class="token constant">ActiveRecord</span> <span class="token punctuation">:</span> <span class="token punctuation">:</span> <span class="token constant">Base</span> has_many <span class="token symbol">:addresses</span> <span class="token keyword">end</span> user <span class="token operator">=</span> <span class="token constant">User</span> <span class="token punctuation">.</span> first user <span class="token punctuation">.</span> addresses <span class="token punctuation">.</span> size <span class="token comment"># => Result without query</span> |
Remove the counter_cache option in the Address model, and you will see the .size
function do the COUNT()
query COUNT()
The .length
function
The .length
function is a Ruby function that also counts the number of elements in an array, but ActiveRecord :: Relation doesn’t have this function. Be careful when using this function with an object of ActiveRecord :: Relation because it essentially loads all records into an array and then counts the number of elements in that array.
1 2 3 4 5 6 7 8 9 10 |
users <span class="token operator">=</span> <span class="token constant">User</span> <span class="token punctuation">.</span> all users <span class="token punctuation">.</span> loaded <span class="token operator">?</span> <span class="token comment"># => nil</span> users <span class="token punctuation">.</span> length <span class="token comment"># SELECT `users`.* FROM `users`</span> <span class="token comment"># => Results (Không có select COUNT)</span> users <span class="token punctuation">.</span> loaded <span class="token operator">?</span> <span class="token comment"># => true</span> users <span class="token punctuation">.</span> length <span class="token comment"># => Results (Tuy nhiên sau khi records đã được load thì nó cũng ko thực hiện truy vấn nữa)</span> |
Check for the existence of records
Now we do not want to count the number of records anymore but simply want to know whether the data query result exists a record or not. Do we have .any?
methods .any?
, .empty?
, .present?
Let’s continue to see how they work?
The .any?
function .any?
In Ruby, .any?
checks every element in the array until it meets a value that evaluates to true then returns true
1 2 3 4 5 6 7 8 |
arr <span class="token operator">=</span> <span class="token punctuation">[</span> <span class="token keyword">nil</span> <span class="token punctuation">,</span> <span class="token boolean">false</span> <span class="token punctuation">,</span> <span class="token number">1</span> <span class="token punctuation">]</span> arr <span class="token punctuation">.</span> any <span class="token operator">?</span> <span class="token comment"># => true</span> arr <span class="token operator">=</span> <span class="token punctuation">[</span> <span class="token keyword">nil</span> <span class="token punctuation">,</span> <span class="token boolean">false</span> <span class="token punctuation">]</span> arr <span class="token punctuation">.</span> any <span class="token operator">?</span> <span class="token comment"># => false</span> |
In ActiveRecord :: Relation, .any?
works similarly to .size
. If the records are not loaded it will execute a SELECT LIMIT 1
query and return true / false, otherwise if the records are loaded then it will no longer query.
1 2 3 4 5 6 7 8 9 10 11 |
users <span class="token operator">=</span> <span class="token constant">User</span> <span class="token punctuation">.</span> all users <span class="token punctuation">.</span> loaded <span class="token operator">?</span> <span class="token comment"># => nil</span> users <span class="token punctuation">.</span> any <span class="token operator">?</span> <span class="token comment"># SELECT 1 AS one FROM `users` LIMIT 1</span> <span class="token comment"># => true</span> users <span class="token punctuation">.</span> load users <span class="token punctuation">.</span> loaded <span class="token operator">?</span> <span class="token comment"># => true</span> users <span class="token punctuation">.</span> any <span class="token operator">?</span> <span class="token comment"># => true</span> |
The .empty?
function .empty?
In contrast to .any?
, .empty?
will return true if no records exist
In ActiveRecord :: Relation, .empty?
Works like .any?
, to check for the existence of the record, it will execute a SELECT LIMIT 1
query if the records are not loaded and if the records are already loaded, it returns the result.
However, what is the difference between .empty?
and .any?
comes from the way they work in Ruby. As mentioned above .any?
checks every element in the array until it meets a value that evaluates to true then returns true. While .empty?
then it doesn’t care much about the value of the element in the array, as long as that array has the element return false
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
arr <span class="token operator">=</span> <span class="token punctuation">[</span> <span class="token keyword">nil</span> <span class="token punctuation">,</span> <span class="token boolean">false</span> <span class="token punctuation">,</span> <span class="token number">1</span> <span class="token punctuation">]</span> arr <span class="token punctuation">.</span> any <span class="token operator">?</span> <span class="token comment"># => true</span> arr <span class="token punctuation">.</span> empty <span class="token operator">?</span> <span class="token comment"># => false</span> arr <span class="token operator">=</span> <span class="token punctuation">[</span> <span class="token keyword">nil</span> <span class="token punctuation">,</span> <span class="token boolean">false</span> <span class="token punctuation">]</span> arr <span class="token punctuation">.</span> any <span class="token operator">?</span> <span class="token comment"># => false</span> arr <span class="token punctuation">.</span> empty <span class="token operator">?</span> <span class="token comment"># => false</span> arr <span class="token operator">=</span> <span class="token punctuation">[</span> <span class="token punctuation">]</span> arr <span class="token punctuation">.</span> any <span class="token operator">?</span> <span class="token comment"># => false</span> arr <span class="token punctuation">.</span> empty <span class="token operator">?</span> <span class="token comment"># => true</span> |
The .present?
function .present?
.present?
Not a function of Ruby, it is a function of Rails that checks whether an array has elements or not, a string has characters other than space or not, a variable has nil or not.
In ActiveRecord :: Relation, .present?
Works similar to .length
, it will load records into the array and check for the existence of a record value on that array. So be careful when using .present?
with an object of ActiveRecord :: Relation (unless you’re sure the records are loaded)
1 2 3 4 5 6 7 8 9 10 11 |
users <span class="token operator">=</span> <span class="token constant">User</span> <span class="token punctuation">.</span> all users <span class="token punctuation">.</span> loaded <span class="token operator">?</span> <span class="token comment"># => nil</span> users <span class="token punctuation">.</span> present <span class="token operator">?</span> <span class="token comment"># SELECT `users`.* FROM `users`</span> <span class="token comment"># => true</span> users <span class="token punctuation">.</span> loaded <span class="token operator">?</span> <span class="token comment"># => true</span> users <span class="token punctuation">.</span> present <span class="token operator">?</span> <span class="token comment"># => true</span> |
Optimal choice
According to what I have shown above, in most cases, when counting the number of records you should use .size
, when checking the existence of records, you should use .empty?
or .any?
.
However, in some cases, the above options are not always the most optimal. For example:
1 2 3 4 5 6 7 |
<span class="token operator"><</span> <span class="token operator">%</span> <span class="token keyword">if</span> <span class="token variable">@users</span> <span class="token punctuation">.</span> any <span class="token operator">?</span> <span class="token string">%> <h1></span> <span class="token constant">List</span> of users <span class="token operator"><</span> <span class="token operator">/</span> h1 <span class="token operator">></span> <span class="token operator"><</span> <span class="token operator">%</span> <span class="token variable">@users</span> <span class="token punctuation">.</span> <span class="token keyword">each</span> <span class="token keyword">do</span> <span class="token operator">|</span> user <span class="token operator">|</span> <span class="token string">%> ... <% end %></span> <span class="token operator"><</span> <span class="token operator">%</span> <span class="token keyword">end</span> <span class="token operator">%</span> <span class="token operator">></span> |
The code above is trying to render the list of users when making sure that users have records existing. With the above code, you will have to execute 2 queries, the first is SELECT COUNT
for @users.any?
, then SELECT *
to load data. However, what if you use .present?
In this case, you only need to do exactly one SELECT *
query to do both.
Through the article, hopefully you can understand how the 6 methods that I have presented, so that you can use them correctly in each specific case to get the best effect. Thank you for reading this article
Reference source: https://longliveruby.com/articles/active-record-counting-records