title: Converting two simple SQL queries into Doctrine
body: <p>First of all, I want to apologize for the length of this question; I didn't know how to properly ask my question without a lot of background.  Please bear with me.</p>
  
  <p>I'm converting a simple application that I use to hone my skills from my own custom database access schema to Doctrine.  I chose Doctrine for a number of reasons, not the least of which is that I use it at my day job regularly.  I also like how Doctrine is generally a pretty thin (appearing) layer that stays out of the way while still adding a lot of features.</p>
  
  <p>I've converted the data access layer for the <code>users</code> table in my database to Doctrine.
  It's very unremarkable (simply getters and setters), except for a few fine details:  </p>
  
  <ul>
  <li>I need to have a custom repository for some specific queries and  </li>
  <li>the <code>User</code> object has a default <code>ArrayCollection</code> instantiated in the constructor  </li>
  </ul>
  
  <hr>
  
  <pre><code>namespace model\entities;
  
  /**
   * @Entity(repositoryClass="model\repositories\UserRepository")
   * @Table(name="users")
   */
  class User{
      /* snip variables */
  
      /**
       * @OneToOne(targetEntity="Authentication", mappedBy="user", cascade="persist")
       */
      private $authentication;
  
      /**
       * @OneToMany(targetEntity="Contact", mappedBy="user", cascade="persist")
       */
      private $contacts;
  
      public function __construct() {
          $this-&gt;contacts = new \Doctrine\Common\Collections\ArrayCollection();
      }
  
      /* snip getters and setters */  
  }
  </code></pre>
  
  <hr>
  
  <p>In my old schema, I had two custom queries that selected a subset of the <code>users</code> table.</p>
  
  <p>They are:</p>
  
  <pre><code>public function search( $term = null ){
      if( !$term ){
          $sql = "SELECT *
                  FROM
                  " . $this-&gt;tableName . "
                  ORDER BY
                      lname ASC,
                      fname ASC";
          $res = $this-&gt;db-&gt;q($sql);
      }
      else{
          $sql = "SELECT *
                  FROM
                  " . $this-&gt;tableName . "
                  WHERE
                      lname LIKE ?
                      OR fname LIKE ?
                      OR CONCAT(fname, ' ', lname) LIKE ?
                  ORDER BY
                      lname ASC,
                      fname ASC";
          $values = array( '%' . $term . '%', '%' . $term . '%', '%' . $term . '%' );
          $res = $this-&gt;db-&gt;qwv( $sql, $values );
      }
  
      return $this-&gt;wrap( $res );
  }
  </code></pre>
  
  <p>and:</p>
  
  <pre><code>public function getAllWithRestrictions(){
      $sql = "SELECT *
              FROM
              " . $this-&gt;tableName . "
              WHERE
                  userid IN
                  (
                      SELECT
                          userid
                      FROM
                          " . $this-&gt;uiPre . "authentications
                      WHERE
                          resetPassword = 1
                          OR disabled = 1
                  )";
      $res = $this-&gt;db-&gt;q( $sql );
  
      return $this-&gt;wrap($res);
  }
  </code></pre>
  
  <p>where <code>$this-&gt;db</code> is a thin <a href="http://php.net/manual/en/book.pdo.php" rel="nofollow noreferrer">PHP PDO</a> <a href="https://github.com/rockerest/nox/blob/master/classes/backbone/Database.php" rel="nofollow noreferrer">wrapper</a> and <a href="https://github.com/rockerest/nox/blob/master/classes/model/access/UserAccess.php" rel="nofollow noreferrer"><code>$this-&gt;wrap</code> does magic</a> with zero/single/multiple rows returned and converting them into <a href="https://github.com/rockerest/nox/blob/master/classes/model/objects/User.php" rel="nofollow noreferrer">data objects</a>.</p>
  
  <p>Now, I figured this would be very easy to convert to Doctrine.  In the case of <code>getAllWithRestrictions</code> it's simply a <code>-&gt;where</code>, <code>-&gt;orWhere</code> set, right?  I don't know anymore.</p>
  
  <p>I found these Stackoverflow questions that I used to try to construct my queries, but I'm running into error after error, and I'm not sure how far down the rabbit hole I need to go:</p>
  
  <p><a href="https://stackoverflow.com/questions/10826299/sql-multiple-sorting-and-grouping">SQL Multiple sorting and grouping</a><br>
  <a href="https://stackoverflow.com/questions/11575325/order-by-multiple-columns-with-doctrine">Order by multiple columns with Doctrine</a><br>
  <a href="https://stackoverflow.com/questions/2343767/doctrine-multiple-wherein-or-wherein-query">Doctrine: Multiple (whereIn OR whereIn) query?</a><br>
  <a href="https://stackoverflow.com/questions/6878448/doctrine-or-where">Doctrine - or where?</a>  </p>
  
  <p>My custom repository currently looks like this, but I can't say it's even close to correct as I've been fiddling with it for a long time, and it's just a hodge-podge of what I thought might work:</p>
  
  <pre><code>&lt;?php
  namespace model\repositories;
  use Doctrine\ORM\EntityRepository;
  use Doctrine\ORM\Query\Expr;
  
  class UserRepository extends EntityRepository{
      public function search( $term ){
          if( !$term ){
              return $this
                      -&gt;_em
                      -&gt;createQuery('SELECT u FROM model\entities\User u')
                      -&gt;addOrderBy( 'u.lname' )
                      -&gt;addOrderBy( 'u.fname' )
                      -&gt;getResult();
          }
          else{
              $qb     = $this-&gt;_em-&gt;createQueryBuilder();
  
              $qb -&gt;select(array('u'))
                  -&gt;from('model\entities\User', 'u')
                  -&gt;where( $qb-&gt;expr()-&gt;like( 'u.lname', '?1' ) )
                  -&gt;orWhere( $qb-&gt;expr()-&gt;like( 'u.fname', '?2' ) )
                  -&gt;orWhere( $qb-&gt;expr()-&gt;like( 'CONCAT(u.fname, \' \', u.lname)', '?3' ) )
                  -&gt;addOrderBy( 'u.lname' )
                  -&gt;addOrderBy( 'u.fname' )
                  -&gt;setParameters(
                      array(
                          1 =&gt; $term,
                          2 =&gt; $term,
                          3 =&gt; $term
                      )
                  );
  
              $query = $qb-&gt;getQuery();
              return $query-&gt;getResult();
          }
      }
  
      public function getAllWithRestrictions(){
          $qb     = $this-&gt;_em-&gt;createQueryBuilder();
  
          $qb -&gt;select(array('u'))
              -&gt;from('model\entities\User', 'u')
              -&gt;add('where', $qb-&gt;expr()-&gt;orx(
                 $qb-&gt;expr()-&gt;eq('u.disabled', '1'),
                 $qb-&gt;expr()-&gt;eq('u.resetPassword', '1')
             ));
  
          $query = $qb-&gt;getQuery();
          return $query-&gt;getResult();
      }
  } 
  </code></pre>
  
  <p><em>EDIT: I just realized that I'm doing the <code>getAllWithRestrictions</code> query on the wrong table (it should be <code>authentications</code>.) In any case, it's the search method causing my issues right now.  However, I will also need to know how to do something like <code>$qb-&gt;expr()-&gt;eq('u.Authentication.disabled = '1')</code> and I have no idea how DQL really works.</em>  </p>
  
  <p>The particular error I'm getting right now is  </p>
  
  <ul>
  <li><code>Fatal error: Uncaught exception 'Doctrine\ORM\Query\QueryException' with message 'SELECT u FROM model\entities\User u WHERE u.lname LIKE ?1 OR u.fname LIKE ?2 OR CONCAT(u.fname, ' ', u.lname) LIKE ?3 ORDER BY u.lname ASC, u.fname ASC'</code>  </li>
  </ul>
  
  <p>followed by  </p>
  
  <ul>
  <li><code>Doctrine\ORM\Query\QueryException: [Syntax Error] line 0, col 99: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got ','</code></li>
  </ul>
  
  <p>But I've had a slew of different issues depending on how I construct the DQL/Query Builder.</p>
  
  <p>Again, I expected these two SQL queries to be simple to convert to Doctrine / DQL.  I'd like to do this without resorting to raw SQL through Doctrine (as many of the accepted answers I linked suggest).  This MUST be easy.  <strong>Does anyone know how to construct elegant Doctrine queries?</strong></p>