DISTINCT/:select is wiped out/overwritten by COUNT when using .size on an association proxy... with rails + PostgreSQL
If you create a named scope that uses :select, and then call “.size” or “.count” on the resulting proxy served by a call to the scoped method, and if that :select option sets DISTINCT, then it will be ignored because rails ignores the old :select and creates a new one of “COUNT(*) AS count_all”
Here is a made up example:
class Book < ActiveRecord::Base named_scope :on_a_favorite_list, :select => "DISTINCT ON (books.id) books.*", :joins => "INNER JOIN favorites_list_entries ON favorites_list_entries.book_id = books.id" end
Now let’s pretend that there is only one book on anybody’s favorites_list, but that two people have that book on their list.
would return 1, because size would be called on an array that only has 1 integer in it.
Would return 2! Why? Because the proxy object (remember, Book.on_a_favorite_list is NOT an array of books… it’s a proxy object that will fetch the books if needed) realizes it needs to create a COUNT query (it’s not going to actually fetch the books) and it does this by overriding the :select option from “DISTINCT ON (books.id) books.*” with “COUNT(*) as count_all”. So DISTINCT has been lost. Now the duplicate row appears to the select statement and 2 is returned as count_all to rails .size method.
One way around this is to pass the thing being counted, including the DISTINCT keyword, to size…
This will return the expected result of 1.