DQL placement conditions

Question

Now that I have read all the DQL documents, I still have some doubts, I'm trying to make some nested conditions in my DQL, but playing around with DQL I can not archive them

To make yourself clearer:

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

this now 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 get a result set where either deleted_at is null or the other conditions are complete, I would like to make isnull (deleted_at) necessary, if we speak in terms of SQL, the query will 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 AND and added a couple of brackets to group the LIKE conditions.

Is it possible to archive this in DQL using the same → where () notation does not allow to write the whole condition?

thanks:)

Answer 1

You can use Doctrine_Query::andWhere , but I think this will affect your statement in the wrong way, so you can build your query, for example

 ->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 pretty creepy.

Here is another Doctrine modified custom bracketing solution: http://danielfamily.com/techblog/?p=37

Answer 2

I don’t know how recently this change was made, but for those who, like me, approach this question, looking for answers after this question has been asked, the query builder now has ways to execute the nested AND / OR logic: andX() / orX() functions .