Help with find in activerecord

Hi

How can I translate this sql using find(*args) in activerecord?

select x.*, y.*, count(distinct y.name) as name_count
from x,y
where x.id1 = y.id2
group by y.name
order by name_count

X.find(:all,
        :select => "x.*, y.*, count(distinct y.name) as name_count",
        :include => "y",
        :group => "y.name",
        :order => "name_count")

doesn't work. Could you please give me some advice?

Thanks

Don't use :include with :select, they're incompatible since :include
will do a ton of weird stuff to your select statement.

You probably should use a join as well:

X.find(:all,
        :select => "x.*, y.*, count(distinct y.name) as name_count",
        :joins => "INNER JOIN #{Y.table_name} ON x.id1 = x.id2",
        :group => "y.name",
        :order => "name_count")

Thanks eden. It works!