CODEWITHSUNDEEP
phpdoctrinedql
perrohunter
perrohunter

Reputation: 3526

nesting conditions with DQL

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 :)

Upvotes: 4

Views: 2125

Answers (2)

Scott M
Scott M

Reputation: 395

Not sure how recently this change was made, but for anyone like me coming to this question looking for answers long after this question was asked, the query builder now has ways to accomplish nested AND/OR logic: andX() / orX() functions.

Upvotes: 5

Thomas Schaub
Thomas Schaub

Reputation: 418

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

Upvotes: 1

Related Questions