使用 NAND、NOR、NOT、AND 运算符在 Doctrine 中进行多查询

问题描述

我正在尝试设计一个学说查询，我是学说的新手，但在我另一篇文章的帮助下，我提出了一个在我的 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.

我对 Doctrine 的尝试:参考链接:

教义查询很难理解，因为它在查询之间自动创建了 () 铜管，所以它总是给我一些错误的结果.请帮助我.

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.

推荐答案

当您使用 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 运算符在 Doctrine 中进行多查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！