title: Symfony doctrine orderby and group by (distinct)
body: <p>I am trying to get the latest booked courses (unique). I have tried the following with the Doctrine querybuilder:</p>
  
  <pre><code>$repository = $this-&gt;getDoctrine()-&gt;getRepository('AppBundle:Booking');
  
  $query = $repository-&gt;createQueryBuilder('b')
  -&gt;select('(b.course) AS course')
  -&gt;orderBy('b.id', 'DESC')
  -&gt;groupBy('b.course')
  -&gt;setMaxResults(5)
  -&gt;getQuery();
  </code></pre>
  
  <p>Without the orderby it works but it will traverse the bookings ascending. I need the latest booked courses. With this query I am getting this error: </p>
  
  <blockquote>
    <p>SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1
    of ORDER BY clause is not in GROUP BY clause and contains
    nonaggregated column ... which is not
    functionally dependent on columns in GROUP BY clause; this is
    incompatible with sql_mode=only_full_group_by 500 Internal Server
    Error - DriverException</p>
  </blockquote>
  
  <p>I also tried the following:</p>
  
  <pre><code>$em = $this-&gt;getDoctrine()-&gt;getManager();
  $query = $em-&gt;createQuery(
     'SELECT DISTINCT c.id
      FROM AppBundle:Booking b
      JOIN b.course c
      ORDER BY b.id DESC'
  );
  </code></pre>
  
  <blockquote>
    <p>SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause
    is not in SELECT list, references column ... which is not in SELECT
    list; this is incompatible with DISTINCT</p>
  </blockquote>
  
  <p>Now I have searched for solutions and basically found a lot of suggestions to disable ONLY_FULL_GROUP_BY. <a href="https://stackoverflow.com/questions/23921117/disable-only-full-group-by">Disable ONLY_FULL_GROUP_BY</a></p>
  
  <p>Some comments state there that it is not wise to do that in order to comply to sql standards. Then how is this possible in a way that does work?</p>