Multiple Query in Doctrine with NAND,NOR,NOT,AND Operators

Question

I am trying to design a doctrine query and I am new to doctrine but with the help of my other post I come up with a query which works when I run in my Mysql. But I want it to convert the query in Doctrine (2.3) can some one help me in this.

MySQL Query:

SELECT * FROM user WHERE 
(`user_name` like '%TOM%' OR `user_name` like '%AN%' and `login_datetime` BETWEEN '2013-01-01 00:00:00' and '2013-02-31 23:59:59') OR
NOT ( --NOR
   (`user_name` like '%PHP%' OR `user_name` like '%BA%' and `login_datetime` BETWEEN '2013-02-01 00:00:00' and '2013-03-31 23:59:59') OR
   (`user_name` like '%SUN%' OR `user_name` like '%MOON%' and `login_datetime` BETWEEN '2013-03-01 00:00:00' and '2013-04-31 23:59:59')
) OR
NOT ( --NAND
   (`user_name` like '%RAJ%' OR `user_name` like '%MUTH%' and `login_datetime` BETWEEN '2013-04-01 00:00:00' and '2013-06-31 23:59:59') AND
   (`user_name` like '%BAG%' OR `user_name` like '%LAP%' and `login_datetime` BETWEEN '2013-05-01 00:00:00' and '2013-07-31 23:59:59')
)

--Link Reference: for the above MySql Query.

My Try with Doctrine: Reference Link:

It is very difficult to understand the doctrine query because of the () brasses which it automatically created in between queries so it gives me some wrong results all the time. Kindly help me.

score 2 · Accepted Answer · answered Jun 11 '13 at 15:18

It is very difficult to understand the doctrine query because of the () brasses which it automatically created in between queries so it gives me some wrong results all the time.

When you use an expr it typically wraps the expression in (). I think thats where you are running into confusion. Something similar to the following should work (this isnt tested so you may need to adjust abit):

$qry = $this->manager()->createQueryBuilder()
        ->from($this->entity, 'e')
        ->select('e');


// (`user_name` like '%TOM%' OR `user_name` like '%AN%' and `login_datetime` BETWEEN '2013-01-01 00:00:00' and '2013-02-31 23:59:59')
$expr1 = $qry->expr()->andX(
    $qry->expr()->orX(
       $qry->expr()->like('e.user_name', '%TOM%'), 
       $qry->expr()->like('e.user_name', '%AN%')
    ),
    $qry->expr()->between('e.login_datetime', '2013-02-01 00:00:00', '2013-02-31 23:59:59')
);

//(`user_name` like '%PHP%' OR `user_name` like '%BA%' and `login_datetime` BETWEEN '2013-02-01 00:00:00' and '2013-03-31 23:59:59')

$expr2a = $qry->expr()->andX(
    $qry->expr()->orX(
       $qry->expr()->like('e.user_name', '%PHP%'), 
       $qry->expr()->like('e.user_name', '%BA%')
    ),
    $qry->expr()->between('e.login_datetime', ''2013-02-01 00:00:00'', '2013-03-31 23:59:59')
);

// (`user_name` like '%SUN%' OR `user_name` like '%MOON%' and `login_datetime` BETWEEN '2013-03-01 00:00:00' and '2013-04-31 23:59:59')
$expr2b = $qry->expr()->andX(
    $qry->expr()->orX(
       $qry->expr()->like('e.user_name', '%SUN%'), 
       $qry->expr()->like('e.user_name', '%MOON%')
    ),
    $qry->expr()->between('e.login_datetime', '2013-03-01 00:00:00', '2013-04-31 23:59:59')
);

// combine expr2a and expr2b with OR as $expr2

$expr2 = $qry->expr()->orX($expr2a, $expr2b);


// (`user_name` like '%RAJ%' OR `user_name` like '%MUTH%' and `login_datetime` BETWEEN '2013-04-01 00:00:00' and '2013-06-31 23:59:59')

$expr3a = $qry->expr()->andX(
    $qry->expr()->orX(
       $qry->expr()->like('e.user_name', '%RAJ%'), 
       $qry->expr()->like('e.user_name', '%MUTH%')
    ),
    $qry->expr()->between('e.login_datetime', ''2013-04-01 00:00:00'', '2013-06-31 23:59:59')
);

// (`user_name` like '%BAG%' OR `user_name` like '%LAP%' and `login_datetime` BETWEEN '2013-05-01 00:00:00' and '2013-07-31 23:59:59')
$expr3b = $qry->expr()->andX(
    $qry->expr()->orX(
       $qry->expr()->like('e.user_name', '%BAG%'), 
       $qry->expr()->like('e.user_name', '%LAP%')
    ),
    $qry->expr()->between('e.login_datetime', '2013-05-01 00:00:00', '2013-07-31 23:59:59')
);

// combine expr2a and expr2b with OR as $expr2

$expr3 = $qry->expr()->andX($expr3a, $expr3b);


// final query essentially WHERE expr1 OR NOT(expr2) OR NOT(expr3)
$qry->where($expr1)
    ->or($qry->expr()->not($expr2))
    ->or($qry->expr()->not($expr3));

your code rocks! It just gave me an hint.. with that i reduced my code from 3500 lines to 350 lines... :) — DonOfDen, Jun 12 '13 at 12:11
Thats a lot of lines.... id be interested to know why what you had was so verbose and how you trimmed it down. — prodigitalson, Jun 12 '13 at 13:33
Its an Search query generating Module where we have min 6 blocks of query input in each block we will have 40 fields.. and 40X6=240 fields all together so the combination was too long and I am not too good @ doctrine so I some how manager to write the query with the help of 3500 lines of code.. but with the help of your query I did it in 350 line. If you want I will share the one block code so that you can see how I generated the query. — DonOfDen, Jun 12 '13 at 15:45
http://chat.stackoverflow.com/rooms/31678/multiple-query-in-doctrine-with-nand-nor-not-and-operators — DonOfDen, Jun 12 '13 at 15:48
can you help me in this http://stackoverflow.com/questions/17115165/doctrine-inner-left-join-two-tables — DonOfDen, Jun 14 '13 at 18:39
can some one help me in this http://stackoverflow.com/questions/17211636/update-text-field-with-issue-in-doctrine — DonOfDen, Jun 20 '13 at 11:39

Multiple Query in Doctrine with NAND,NOR,NOT,AND Operators

1 Answers1

Linked