Calling empty? on a named_scope with :select => "distinct table_name.*" throws error

This also seems to happen when using MyModel.the_named_scope.count.

Seems like the best way to fix this issue would be to allow a :distinct options for find. Right now this appears to only be a valid option on count.

@micah if you use MyModel.the_named_scope, the result is an Array, you have to use length method or size method MyModel.the_named_scope.length

Are you using the DISTINCT with select because you are also using :joins and it returns dup records?

If so, use :include instead for the associations you are doing the :joins on and add :group with either id or an attribute on the model you want to distinguish the result set. :include does a LEFT OUTER JOIN on the association and the calculation methods do a DISTINCT in that case. count, length and size will all return the same result and what you expect. Here is an example where you want to do :include instead of :joins:

class Group < ActiveRecord::Base

  has_many :group_invites

  named_scope :invited_to,
              lambda {|person| { :include => :group_invites,
                                 :conditions => ["group_invites.person_id = ?", person.id],
                                 :group => "groups.name" } }
end

If you use :joins and :group, the returned association set will be what you expect (no dup objects), but calling size, length and count will all return a possibly larger number of rows that SELECT returned but include duplicates.

This is something of a general problem for any aggregation operator on a relation including a hand-coded projection (select). #empty? appears to effectively operate as "count.zero?" (or is it size.zero?) on a relation, which in turn sets up a query that REPLACES the custom projection with "COUNT(*)." This in turn will throw an error if the custom select contained matter necessary to make the rest of the query valid SQL.

I recently chased a bug with a query that originally had something like (giving the sql to show how the difficulty occurs).

SELECT primaries.*, associateds.date AS most_recent_date
FROM primaries JOIN associateds LEFT JOIN ...complex join goes here ...
WHERE most_recent_date < '2001-1-1'

This was accomplished with something like:

primary_with_most_recent_date_before_2001 = Primary.joins(custom_string).select(custom_string)

which worked fine for all purposes, until the code

primary_with_most_recent_date_before_2001.empty?

blew up with an invalid SQL error.

This happens because the expression becomes invalid when the SELECT phrase was replaced with "COUNT(*)," as the definition of 'most_recent_date' is lost. The query works just fine without using .count or .empty?, but fails when rails tries to convert the relation sql generated.

Of course, for now you can simply replace the complex

   relation.joins(custom_string).select(custom_string).empty?

with

   relation.joins(custom_string).select(custom_string).all.empty?

and it will work fine, however inefficiently. Perhaps it would be better to translate relations having custom language with "COUNT(*) FROM" a subselection to assure valid SQL and hope the optimizer can sort the wheat from the chaff?

louis vuitton watches