symfony2教义加入 [英] symfony2 doctrine join

问题描述

好吧，我有一个查询，我已经研究和研究了如何让这个工作，对我的生活我不能！或许我只是这样做错误，最小的信息发现..

我有一个名为timeclock setup ..的表，它有一个字段：noteBy_id，它是记录所属用户的id。 。

我现在需要做的是为系统中的事物管理一方。我预计有超过1家公司使用这个时钟系统，因此我需要根据公司ID筛选结果。在用户表中，我有一个名为 parentcompany_id

所以，看看是否可以用单词表达我需要做的事情。

我需要从timeclock中选择*，然后加入user.parentcompany_id其中timeclock.daydate < ：start和where u.parentcompany =：pid

其中start是：`date（'Ymd 00:00:00'）;

我设置了这个查询：

  $ em = $ this-> getDoctrine（） > getEntityManager（）; 
 $ start = date（'Y-m-d 00:00:00'）; 
 $ qb = $ em-> getRepository（'EcsCrmBundle：TimeClock'）; 
 $ qb = $ qb-> createQueryBuilder（'t'）; 
 
 $ query = $ qb-> select（'t，u.parentcompany_id'）
  - > from（'timeclock'，'t'）
  - > leftJoin（'Ecs\AgentManagerBundle\Entity\User'，'u'，'ON''u.id = t.noteBy_id AND u.parentcompany_id =：pid'）
  - >其中（'t 
  - > andWhere（t.noteBy_id！='）
  - > setParameter（'start'，$ start）
  - > setParameter（'pid'，$ user-> getParentcompany（））
  - > getQuery（）; 
 
 $ entities = $ query-> getArrayResult（）; 
  

我已经看了看，找不到我得到的错误的解决方案：

 在渲染模板期间抛出异常（[语义错误]行0，col 112靠近'u ON u.id = t.noteBy_id'：错误：标识变量Ecs\AgentManagerBundle\Entity\User在连接路径表达式中使用但未在之前定义。）在EcsCrmBundle中：TimeClock：manager.html.twig在第5行
  

，获取输出的查询是：

  SELECT t，u.parentcompany_id FROM Ecs\CrmBundle\Entity\TimeClock t LEFT JOIN Ecs\AgentManagerBundle\Entity\User u ON u.id = t。 noteBy_id AND u.parentcompany_id =：pid，timeclock t LEFT JOIN Ecs\AgentManagerBundle\Entity\User u ON u.id = t.noteBy_id AND u.parentcompany_id =：pid WHERE t.daydate< ：start AND t.noteBy_id！=''
  

正常情况下，在这里，它只是没有...任何想法？

解决方案

我最近不得不这样做。我猜这是你的noteBy是用户表中的一个ManyToOne，你想要让当前登录到你的系统的管理员的公司筛选结果。

所以，调整一个连接我不得不自己写这个任务是很容易的。我个人喜欢使用QueryBuilder，所以这将在查询生成器中完成。

您的查询中的第一个错误是 - > from （'timeclock'，'t'）行。因为您以前使用 $ qb = $ em-> getRepository（'EcsCrmBundle：TimeClock'）创建了对象; $ qb = $ qb-> createQueryBuilder（'t'）; 您不需要查询构建器中的，因为它将为您生成。

下一个问题是，leftJoin我将解释为什么我向您展示了一个工作版本。

最后一个问题，阻止这种方式工作，你想要的是一个缺少的和子句。所以，让我们来看看一个工作的查询。

  $ query = $ qb-> select（'t，u' ）
  - > leftJoin（'t.noteBy'，'u'，'WITH'，'u.id = t.noteBy'）
  - > where（'t.daydate<开始'）
  - > andWhere（'u.parentcompany =：pid'）
  - > setParameter（'start'，$ start）
  - > setParameter（'pid' $ user-> getParentcompany（））
  - > getQuery（）; 
  

所以因为我们已经使用 $ qb = $创建了对象qb-> createQueryBuilder（'t'）我们只需选择 t 和 u / p>

对于连接，我们通过 noteBy 列加入timeclock表，该列是来自用户表。所以，第一个参数是从别名。所以，由于我们已经用t代替了timeclock表，所以我们使用 t.noteBy 。在左边的下一个参数是第二个表的别名，在这种情况下，它是 u ，但可以是任何东西..左边的第三个参数 - 无论如何你加入它..一个 WITH 或 ON 将在这里工作。和第四个参数，是你希望它的匹配..在这种情况下 u.id必须等于t.noteBy

你会看到我摆脱了一个和之间的一个，我这样做是因为结构合理的查询，你不应该需要它。我确实添加了和为 u.parentcompany ，因为这是你最想要过滤的内容应该在WHERE中，而不是连接本身的匹配。

文档在这方面非常有限，花了我一段时间才能把它全部归结你无疑像我一样，用手来写你的疑问来使用原则。所以，因为你似乎只是从Symfony开始（我现在是大约2个月的时间），你仍然在手工编码的心态。但随着时间的推移，您将开始了解DQL的生活方式。尝试这个查询，看看会发生什么。

Okay, so i've got a query that i've researched and researched how to get this to work and for the life of me i cant!... perhaps i'm just doing this incorrectly and the minimal information ive found..

I've got a table named timeclock setup.. which has a field: noteBy_id in it which is an id to the user the record belongs to...

What I need to do now, is for the management side of things in the system.. I anticipate more than 1 company using this timeclock system, and as such I need to filter the results based on the company id.. In the user table, i have a field named parentcompany_id

So, lets see if I can express in words what I need to do..

I need to Select * from timeclock and left join user.parentcompany_id where timeclock.daydate < :start and where u.parentcompany = :pid

where start is: `date('Y-m-d 00:00:00');

I've setup this query:

$em = $this->getDoctrine()->getEntityManager();
$start = date('Y-m-d 00:00:00');
$qb = $em->getRepository('EcsCrmBundle:TimeClock');
$qb = $qb->createQueryBuilder('t');

$query = $qb->select('t, u.parentcompany_id')
->from('timeclock', 't')
->leftJoin('Ecs\AgentManagerBundle\Entity\User', 'u', 'ON' 'u.id = t.noteBy_id AND u.parentcompany_id = :pid')
->where('t.daydate < :start')
->andWhere("t.noteBy_id != ''")
->setParameter('start', $start)
->setParameter('pid', $user->getParentcompany())
->getQuery();

$entities = $query->getArrayResult();

I've looked and looked and can't find a solution to the error that I get which is:

An exception has been thrown during the rendering of a template ("[Semantical Error] line 0, col 112 near 'u ON u.id = t.noteBy_id': Error: Identification Variable Ecs\AgentManagerBundle\Entity\User used in join path expression but was not defined before.") in EcsCrmBundle:TimeClock:manager.html.twig at line 5.

and the query that gets output is:

SELECT t, u.parentcompany_id FROM Ecs\CrmBundle\Entity\TimeClock t LEFT JOIN Ecs\AgentManagerBundle\Entity\User u ON u.id = t.noteBy_id AND u.parentcompany_id = :pid, timeclock t LEFT JOIN Ecs\AgentManagerBundle\Entity\User u ON u.id = t.noteBy_id AND u.parentcompany_id = :pid WHERE t.daydate < :start AND t.noteBy_id != ''

which under normal circumstances would work perfectly... but in this, it just doesn't... Any ideas?

解决方案

I've recently had to do it like this.. I'm guessing in this that your noteBy is a ManyToOne in the user table and you are wanting to have it filter the results by the company of the admin that is currently logged into your system..

So, adapting a join I had to write myself for such a task is easy enough. I personally like to use the QueryBuilder so this will be done in query builder..

Your first mistake in your query is the ->from('timeclock', 't') line. Because you have previously created your object with $qb = $em->getRepository('EcsCrmBundle:TimeClock'); $qb = $qb->createQueryBuilder('t'); you don't need the from in the query builder, as it will be generated for you.

The next issue, is the leftJoin and I'll explain why when I've shown you a working version.

And the last issue, preventing this from working how you want it - is a missing andWhere clause. So, lets take a look at a working query.

$query = $qb->select('t, u')
     ->leftJoin('t.noteBy', 'u', 'WITH', 'u.id = t.noteBy')
     ->where('t.daydate < :start')
     ->andWhere('u.parentcompany = :pid')
     ->setParameter('start', $start)
     ->setParameter('pid', $user->getParentcompany()) 
     ->getQuery();

So because we've already created the object by using $qb = $qb->createQueryBuilder('t') we just select t and u

For the join, we're joining the timeclock table by the noteBy column, which is the user id from the user table. So, the first argument being the "from" alias. So, since we've aliased the timeclock table with t we use t.noteBy. The next argument in the leftjoin is the alias of the 2nd table, which is u in this case but can be anything.. The third argument for a leftJoin anyway - is the way you join it.. either a WITH or ON will work here. and the 4th argument, is the match you wish it to have.. in this case u.id must equal t.noteBy

You will see that I got rid of one of the andWhere, I did this because with the properly structured query you shouldn't need it. I did however add in the andWhere for the u.parentcompany since that is afterall what you are looking to filter by you should have it in a WHERE instead of as a match in the join itself.

The documentation is very limited in this, and it took me a while to figure it all out as well.. You, undoubtedly - like me, came to using doctrine from writing your queries by hand. And So since you seem to be just starting with Symfony (i am myself as well about 2 months in now), you're still in the hand-coding mindset. But with further time, you'll start understanding the DQL way of life. Try this query out and see what happens.

这篇关于symfony2教义加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！