Symfony 2:与doctrine query builder的非相关表的内部连接。

I'm trying to build a query with the doctrine query builder which joins a non related table like this:

我正在尝试使用doctrine query builder来构建一个查询，该查询构建器连接到这样一个不相关的表:

$query = $this->createQueryBuilder('gpr')
        ->select('gpr, p')
        ->innerJoin('TPost', 'p')
        ->where('gpr.contentId = p.contentId')

But this doesn't work. I still get an error:

但这是行不通的。我仍然得到一个错误:

Error: Identification Variable TPost used in join path expression but was not defined before.

错误:在连接路径表达式中使用的标识变量TPost，但之前没有定义。

I searched for this error message and everybody answered to use the table alias + attribute like p.someAttribute. But the table I want to join isn't related in the table I start my select from.

我搜索了这个错误消息，每个人都使用表别名+属性，如p.someAttribute。但是我想要连接的表与我开始选择的表没有关联。

As a normal mysql query i would write it like this:

作为一个普通的mysql查询，我这样写:

SELECT * FROM t_group_publication_rel gpr 
INNER JOIN t_post p 
WHERE gpr.content_id = p.content_id

Any ideas what i'm doing wrong?

你知道我做错了什么吗?

4 个解决方案

#1

Today I was working on similar task and remembered that I opened this issue. I don't know since which doctrine version it's working but right now you can easily join the child classes in inheritance mapping. So a query like this is working without any problem:

今天我也在做同样的工作，我记得我打开了这个问题。我不知道它从哪个学说版本开始起作用，但是现在您可以在继承映射中轻松地加入子类。像这样的查询没有任何问题:

$query = $this->createQueryBuilder('c')
        ->select('c')
        ->leftJoin('MyBundleName:ChildOne', 'co', 'WITH', 'co.id = c.id')
        ->leftJoin('MyBundleName:ChildTwo', 'ct', 'WITH', 'ct.id = c.id')
        ->orderBy('c.createdAt', 'DESC')
        ->where('co.group = :group OR ct.group = :group')
        ->setParameter('group', $group)
        ->setMaxResults(20);

I start the query in my parent class which is using inheritance mapping. In my previous post it was a different starting point but the same issue if I remember right.

我在父类中启动查询，父类使用继承映射。在我之前的文章中，这是一个不同的起点，但如果我没记错的话，也是同样的问题。

Because it was a big problem when I started this issue I think it could be also interesting for other people which don't know about it.

因为这是一个大问题当我开始这个问题的时候，我认为它可能对其他不了解它的人也很有趣。

#2

Joins between entities without associations were not possible until version 2.4, where you can generate an arbitrary join with the following syntax:

在2.4版之前，没有关联的实体之间的连接是不可能的，在2.4版中，您可以使用以下语法生成任意的连接:

$query = $em->createQuery('SELECT u FROM User u JOIN Blacklist b WITH u.email = b.email');

Reference: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html

参考:http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html

#3

$dql = "SELECT 
    a, md.fisrtName , md.LastName, mj
    FROM MembersBundle:Memberdata md
        INNER JOIN MembersBundle:Address a WITH md = a.empID
        INNER JOIN MembersBundle:Memberjob mj WITH md = mj.memberData
            ...
    WHERE
        a.dateOfChange IS NULL
    AND WHERE
        md.someField = 'SomeValue'";

return $em->createQuery( $dql )->getResult();

#4

A DQL join only works if an association is defined in your mapping. In your case, I'd say it's much easier to do a native query and use ResultSetMapping to populate your objects.

只有在映射中定义了关联时，DQL连接才有效。在您的例子中，我认为使用本机查询和使用ResultSetMapping来填充对象要容易得多。

#1

$query = $this->createQueryBuilder('c')
        ->select('c')
        ->leftJoin('MyBundleName:ChildOne', 'co', 'WITH', 'co.id = c.id')
        ->leftJoin('MyBundleName:ChildTwo', 'ct', 'WITH', 'ct.id = c.id')
        ->orderBy('c.createdAt', 'DESC')
        ->where('co.group = :group OR ct.group = :group')
        ->setParameter('group', $group)
        ->setMaxResults(20);