title: How do I join a table to itself in AREL to find duplicates?
body: <p>I have a table, <code>transactions</code>, that may contain duplicates (for us, a duplicate is a <code>Transaction</code> with the same <code>account_id</code>, <code>date</code>, and <code>amount</code>).</p>
  
  <p>My English-language functional requirement is "I want to see all transactions for which there exist more than 1 transaction with the same account_id, date, and amount".</p>
  
  <p>Giving up on AREL temporarily, I produced something like this in SQL:</p>
  
  <pre><code>SELECT * FROM transactions t1, transactions t2
      WHERE t1.id != t2.id 
        AND t1.date = t2.date
        AND t1.amount = t2.amount
        AND t1.account_id = t2.account_id
  </code></pre>
  
  <p>I'm using Rails 3.2.x and Postgres.</p>
  
  <p>Originally, I tried this in AREL:</p>
  
  <p><code>Transaction.group(:account_id, :date, :amount).having("count(id) &gt; 1")</code></p>
  
  <p>But that gave me the SQL error about aggregate functions:</p>
  
  <pre><code>PG::Error: ERROR:  column "transactions.id" must appear in the GROUP BY clause or be used in an aggregate function
  </code></pre>
  
  <p>.. which is frustrating, because I do <em>not</em> want the ID in the group by clause -- the whole point is that I want the IDs to be ignored in checking for dupes.</p>
  
  <p>I'd appreciate it if anyone can point me in the right direction of the AREL I need to make this a scope -- <code>find_by_sql</code> is great when you need records, but I am trying to create an ActiveAdmin scope -- it doesn't like arrays.</p>