Converting two simple SQL queries into Doctrine

Question

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.

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.

I've converted the data access layer for the users table in my database to Doctrine. It's very unremarkable (simply getters and setters), except for a few fine details:

• I need to have a custom repository for some specific queries and
• the User object has a default ArrayCollection instantiated in the constructor

---

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->contacts = new \Doctrine\Common\Collections\ArrayCollection();
    }

    /* snip getters and setters */  
}

---

In my old schema, I had two custom queries that selected a subset of the users table.

They are:

public function search( $term = null ){
    if( !$term ){
        $sql = "SELECT *
                FROM
                " . $this->tableName . "
                ORDER BY
                    lname ASC,
                    fname ASC";
        $res = $this->db->q($sql);
    }
    else{
        $sql = "SELECT *
                FROM
                " . $this->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->db->qwv( $sql, $values );
    }

    return $this->wrap( $res );
}

and:

public function getAllWithRestrictions(){
    $sql = "SELECT *
            FROM
            " . $this->tableName . "
            WHERE
                userid IN
                (
                    SELECT
                        userid
                    FROM
                        " . $this->uiPre . "authentications
                    WHERE
                        resetPassword = 1
                        OR disabled = 1
                )";
    $res = $this->db->q( $sql );

    return $this->wrap($res);
}

where $this->db is a thin PHP PDO wrapper and $this->wrap does magic with zero/single/multiple rows returned and converting them into data objects.

Now, I figured this would be very easy to convert to Doctrine. In the case of getAllWithRestrictions it's simply a ->where, ->orWhere set, right? I don't know anymore.

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:

SQL Multiple sorting and grouping
Order by multiple columns with Doctrine
Doctrine: Multiple (whereIn OR whereIn) query?
Doctrine - or where?

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:

<?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
                    ->_em
                    ->createQuery('SELECT u FROM model\entities\User u')
                    ->addOrderBy( 'u.lname' )
                    ->addOrderBy( 'u.fname' )
                    ->getResult();
        }
        else{
            $qb     = $this->_em->createQueryBuilder();

            $qb ->select(array('u'))
                ->from('model\entities\User', 'u')
                ->where( $qb->expr()->like( 'u.lname', '?1' ) )
                ->orWhere( $qb->expr()->like( 'u.fname', '?2' ) )
                ->orWhere( $qb->expr()->like( 'CONCAT(u.fname, \' \', u.lname)', '?3' ) )
                ->addOrderBy( 'u.lname' )
                ->addOrderBy( 'u.fname' )
                ->setParameters(
                    array(
                        1 => $term,
                        2 => $term,
                        3 => $term
                    )
                );

            $query = $qb->getQuery();
            return $query->getResult();
        }
    }

    public function getAllWithRestrictions(){
        $qb     = $this->_em->createQueryBuilder();

        $qb ->select(array('u'))
            ->from('model\entities\User', 'u')
            ->add('where', $qb->expr()->orx(
               $qb->expr()->eq('u.disabled', '1'),
               $qb->expr()->eq('u.resetPassword', '1')
           ));

        $query = $qb->getQuery();
        return $query->getResult();
    }
} 

EDIT: I just realized that I'm doing the getAllWithRestrictions query on the wrong table (it should be authentications.) 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 $qb->expr()->eq('u.Authentication.disabled = '1') and I have no idea how DQL really works.

The particular error I'm getting right now is

• 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'

followed by

• Doctrine\ORM\Query\QueryException: [Syntax Error] line 0, col 99: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got ','

But I've had a slew of different issues depending on how I construct the DQL/Query Builder.

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. Does anyone know how to construct elegant Doctrine queries?

Answer 1

According to the CONCAT function parser in doctrine, it only takes 2 parameters separated by a comma,

//Doctrine/ORM/Query/AST/Functions/ConcatFunction.php
$parser->match(Lexer::T_IDENTIFIER);
$parser->match(Lexer::T_OPEN_PARENTHESIS);

$this->firstStringPrimary = $parser->StringPrimary();
$parser->match(Lexer::T_COMMA);
$this->secondStringPrimary = $parser->StringPrimary();

$parser->match(Lexer::T_CLOSE_PARENTHESIS);

So this

->orWhere( $qb->expr()->like( 'CONCAT(u.fname, \' \', u.lname)', '?3' ) )

should be

->orWhere( $qb->expr()->like( 'CONCAT(u.fname, u.lname)', '?3' ) )

This will not throw any errors, but may be not what you actually need. In that case, you will have to roll your own custom function for CONCAT :)

Answer 2

In addition to @Broncha's answer above (which helped solve the error by adding a nested CONCAT like 'CONCAT(u.fname, CONCAT(\' \', u.lname))'), the other function can be fixed by joining on the entity required, then adding WHERE clauses on the join like so:

$qb ->select(array('u'))
    ->from('model\entities\User', 'u')
    ->leftJoin('u.authentication', 'a')
    ->add('where', $qb->expr()->orx(
        $qb->expr()->eq('a.disabled', '1'),
        $qb->expr()->eq('a.resetPassword', '1')
    ));