sub query issue with active record.

i can manage the MySQL queries into active record.but in some cases basically with sub-queries.i didn’t get the correct output.

here when i trying to change the MySQL queries into active-record.

MySQL Query

select count() from ( SELECT transactions.loyalty_id FROM transactions JOIN loyalties ON (transactions.loyalty_id = where loyalties.created_at > ‘2011-11-02’ GROUP BY transactions.loyalty_id HAVING COUNT()>1 ) as t

Here just i tried to convert into corresponding active record queries. But i didn’t get the correct output.

Transaction.count(:loyalty_id) , :joins=> [“JOIN loyalties ON (transactions.loyalty_id =” ] , :conditions => ["loyalties.created_at > ? ",cdate], :group=> [“transactions.loyalty_id”], :having=> ["count(*) > 1 "] )

How to change that issue?

   Modify your query to

   loyalties = Transaction.count(:loyalty_id , :joins=> :loyalties,
:conditions => ["loyalties.created_at > ? ",cdate], :group=>
["transactions.loyalty_id"], :having=> ["count(*) > 1 "]

   loyalties contain OrderedHash. Now do loyalties.count to get actual
count of filtered loyalties and that matches with your raw sql output.