title: Rails 4 JOIN GROUP BY and SELECT
body: <p>I am trying to JOIN 2 tables in Rails 4, to perform a count and also keep a column of the joined table.</p>
  
  <p>Models: User has_many :orders</p>
  
  <p>What I want: the number of orders AND the date of the last order.</p>
  
  <pre><code>has_many :orders, -&gt; { select("users.*, count(orders.id) as orders_count").group('users.id') }
  </code></pre>
  
  <p>I would like to select the created_at column in the ORDERS table like so</p>
  
  <pre><code>select("users.*, orders.created_at, count(orders.id) as orders_count").group('users.id')
  </code></pre>
  
  <p>But in that case I get an error</p>
  
  <pre><code>PG::GroupingError: ERROR:  column "orders.created_at" must appear in the GROUP BY clause or be used in an aggregate function
  </code></pre>
  
  <p>I need the FIRST created_at so I tried the SQL Aggregate Function "FIRST" on the created_at column</p>
  
  <pre><code>select("users.*, first(orders.created_at) as most_recent, count(orders.id) as orders_count").group('users.id')
  </code></pre>
  
  <p>But I would get</p>
  
  <pre><code>PG::UndefinedFunction: ERROR:  function first(character varying) does not exist
  </code></pre>
  
  <p>Any idea how can I achieve this JOIN, COUNT and SELECT on the joined table?</p>