Doctrine INNER/LEFT JOIN 两张表 [英] Doctrine INNER/LEFT JOIN two tables
问题描述
以下是我的问题.我也在寻求上一个问题的帮助.感谢那些帮助我解决问题的人.
Below is my Questions. I am looking for help in my previous question too.. Thanks for those who helped me in solving the issues.
我在 Doctrine 2.3 中创建一个大查询，操作对我来说并不熟悉.但是我在很多人的帮助下学会了.目前，我正面临 3 个表之间的内部连接问题.
I am creating a big Query in Doctrine 2.3, The operations are not familiar to me.But I learnt with the help of many persons. Currently I am facing issue with Inner Joint between 3 tables.
我的关节:
SELECT *
FROM user AS u
LEFT JOIN source AS s ON u.user_source_fk=s.source_id
LEFT JOIN area AS a ON s.source_node_fk = a.area_id;
上面的查询是我试图在 Doctrine 中转换的.
The above Query is what I am trying to convert in Doctrine.
我试图像这样给出查询，但它不起作用:
I tried to give the query as like this but it is not working:
SELECT a FROM IpfModelUser u LEFT JOIN IpfModelSources ON u.user_source_fk=s.source_id LEFT JOIN IpfModelArea aa ON s.source_node_fk = aa.area_id;
我的原始查询将如何:
SELECT *
FROM user AS u
LEFT JOIN source AS s ON u.user_source_fk=s.source_id
LEFT JOIN area AS a ON s.source_node_fk = a.area_id;
+
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')
)
我想用上面的查询对查询进行内连接，即与运算符的内连接 参考:使用 NAND、NOR、NOT、AND 运算符的 Doctrine 中的多个查询
I want to do Inner Joint to the Query with the above Query that is Inner Joint with Operators Refer:Multiple Query in Doctrine with NAND,NOR,NOT,AND Operators
如何加入表并执行查询?
How to Join the tables and execute the Query?
$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));
如何通过JOIN操作修改实现上述查询?
How can I Modify to Achieve the Above Query with JOIN Operation?
谁能帮我解决我的问题.它在教义上对我来说是一场噩梦......
Can some one help me to solve my issue. Its nightmare for me in doctrine....
推荐答案
你查询的复杂部分实际上是 WHERE 所以你应该能够只添加连接而没有什么问题.
The complex part of you query is actually the WHERE so you should be able to just add the joins with little issue.
$qry = $this->manager()->createQueryBuilder()
->select(array('e', 's', 'a'))
->from($this->entity, 'e')
->leftJoin('e.sources', 's')
->leftJoin('s.node', 'a');
然后您将按原样执行其余的查询逻辑.
And then you would do the rest of your query logic as is pretty much.
需要提到的一件事是，在 DQL 中，您处理的是实体及其属性，而不是表和列.
The one thing that needs to be mentioned is that in DQL you are dealing with Entities and their properties as opposed to tables and columns.
所以在我的示例中，e.sources 需要是 User 实体上的 UserSource 实体/集合的映射属性名称.同样，s.node 需要是 UserSource 上 Area 实体/集合的映射属性名称.
So in my example e.sources needs to be the mapped property name for the UserSource entity/collection on your User entity. Likewise, s.node needs to be the mapped property name of the Area entity/collection on UserSource.
这篇关于Doctrine INNER/LEFT JOIN 两张表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
