nesting conditions with DQL

Question

Hey there :)

Now that I've read all the DQL docs I still have some doubts, I'm trying to do some nested condictions in my DQL however playing around with DQL I can't seem to be able to archive them

To make myself more clear:

I have this DQL query

    $q = Doctrine_Query::create()
        ->select('c.nombre,c.webpage')
        ->addSelect('COUNT(m.marca_id) as total_marcas')
        ->from('Corporativos c')
        ->leftJoin('c.Marcas m')
        ->groupBy('c.corporativo_id')
        ->where('ISNULL(c.deleted_at)')
        ->orwhere('c.nombre LIKE :nombre', array(':nombre'=>'%'.$srch))
        ->orWhere('c.nombre LIKE :nombre', array(':nombre'=>'%'.$srch.'%'))
        ->orWhere('c.nombre LIKE :nombre', array(':nombre'=>$srch.'%'))
        ->orderBy('c.nombre ASC')
        ->limit(0,20);

now this generates the following MySQL query:

SELECT c.corporativo_id AS c__corporativo_id, c.nombre AS c__nombre,
c.webpage AS c__webpage, COUNT(m.marca_id) AS m__0 FROM corporativos c
LEFT JOIN marcas m ON c.corporativo_id = m.corporativo_id WHERE
(ISNULL(c.deleted_at) OR c.nombre LIKE :nombre OR c.nombre
LIKE :nombre OR c.nombre LIKE :nombre) GROUP BY c.corporativo_id ORDER
BY c.nombre ASC

However I'm getting a set of results where either deleted_at is null or the other conditions is completed, I'd like to make the isnull(deleted_at) obligatory, if we were talking in terms of SQL the query would look like this:

SELECT c.corporativo_id AS c__corporativo_id, c.nombre AS c__nombre,
c.webpage AS c__webpage, COUNT(m.marca_id) AS m__0 FROM corporativos c
LEFT JOIN marcas m ON c.corporativo_id = m.corporativo_id WHERE
(ISNULL(c.deleted_at) AND (c.nombre LIKE :nombre OR c.nombre
LIKE :nombre OR c.nombre LIKE :nombre)) GROUP BY c.corporativo_id
ORDER BY c.nombre ASC

you can see that I just changed the first OR statement for an AND and added a couple of parenthesis to group the LIKE conditions.

Is it posible to archivie this in DQL using the same ->where() notation avoiding writing down the whole condition ?

thanks :)

Answer 1

You can use Doctrine_Query::andWhere, but i think this will factor your statement the wrong way, so you could build your query like

->where('c.nombre LIKE :nombre', array(':nombre'=>'%'.$srch))
->andWhere('ISNULL(c.deleted_at)')
->orWhere('c.nombre LIKE :nombre', array(':nombre'=>'%'.$srch.'%'))
->andWhere('ISNULL(c.deleted_at)')
->orWhere('c.nombre LIKE :nombre', array(':nombre'=>$srch.'%'))
->andWhere('ISNULL(c.deleted_at)')

which is kind of creepy.

Here is another solution with modifies Doctrine to be capable of custom bracketing: http://danielfamily.com/techblog/?p=37