Converting two simple SQL queries into Doctrine-Collection of common programming errors

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:

Originally posted 2013-11-27 11:51:43.