以NAND，NOR，NOT和AND运算符为主的多重查询 [英] Multiple Query in Doctrine with NAND,NOR,NOT,AND Operators

问题描述

我正在设计一个教条查询，而且我是新手，但是在我的另一篇文章的帮助下，我提出了一个在我的Mysql中运行的查询。但是我想要它在Doctrine（2.3）中转换查询可以帮助我。

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查询：

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

- 链接参考：上述MySql查询。

--Link Reference: for the above MySql Query.

我的尝试与原则：参考链接：

由于在查询之间自动创建的（）黄铜，所以它总是给我一些错误的结果，很难理解教义查询。请帮助我。

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.

推荐答案

由于（）黄铜，它自动创建在查询之间，所以它总是给我一些错误的结果。

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.

当你使用 expr 它通常将表达式包裹在（）中。我认为你在哪里遇到混乱。类似于以下内容应该可以工作（这是没有测试，所以你可能需要调整abit）：

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

这篇关于以NAND，NOR，NOT和AND运算符为主的多重查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！