查询生成器/DQL 不适用于 INNER JOIN - 语法问题 [英] Query Builder / DQL not working with INNER JOIN - Syntax Issue

问题描述

我知道我在这里有语法问题，但是我无法弄清楚.我正在尝试对 5 个表进行 SELECT 和 INNER JOIN，但 Symfony 抱怨 JOIN 中的实体在定义之前就已使用.

I know I have a syntax isse here however I cant figure it out. I'm trying to do a SELECT and INNER JOIN of 5 tables but Symfony is complaining about the Entities in the JOIN are used before being defined.

实际错误如下:[Semantical Error] line 0, col 121 near 'I ON C.id = ': Error: Identification Variable MySiteBundle:Items used in join path expression but was not defined before.

这是 PHP 代码.

注意:我已将此查询缩短为两列、两个表和一个连接，以保持问题简单并表明我的观点.实际查询要长得多，并且会产生相同的错误.

Note: I have shortened this query to two columns, two tables, and one join to keep the question simple and show my point. The actual query is much longer and is producing the same error.

$em = $this->getDoctrine()->getEntityManager();
$query = $em->createQuery(
    'select C.name as CName, I.id as IId
    FROM MySiteBundle:Categories C
    INNER JOIN MySiteBundle:Items I ON C.id = I.category_id');
$result = $query->getResult();

<小时>

更新

正如建议的那样，我已经取消了 DQL 代码并正在使用查询生成器代码.我收到一个非常相似的错误，上面写着 'Categories c': Error: Class 'Categories' is not defined.我的QB代码如下.

As suggested I've done away with the DQL code and am using Query Builder code. I'm getting a very similiar error which says 'Categories c': Error: Class 'Categories' is not defined. My QB code is below.

$em = $this->getDoctrine()->getEntityManager();
$qb = $em->createQueryBuilder()
        ->select('c.name, i.id, i.image, i.name, i.description, m.id, m.quantity, m.value, m.qty_received, m.custom_image, m.custom_name, m.custom_description, u.user1fname, u.user1lname, u.user2fname, u.user2lname')
        ->from('Categories', 'c')
        ->innerJoin('Items', 'i', 'ON', 'c.id = i.category_id')
        ->innerJoin('MemberItems', 'm', 'ON', 'i.id = m.item_id')
        ->innerJoin('User', 'u', 'ON', 'm.memberinfo_id = u.id')
        ->where('u.id = ?', $slug)
        ->orderBy('c.id', 'ASC')
        ->getQuery();

$memberItems = $qb->getResult();

有什么建议吗?

推荐答案

Louis 在我打字的时候发帖.哦，好吧.

Louis posted while I was typing. Oh well.

DQL 会根据您的关联为您处理加入详细信息.一般情况下，只需要拼出FROM类名即可.比如:

DQL takes care of the join details for you based on your associations. In general, you only need to spell out the FROM class name. Something like:

'select C.name as CName, I.id as IId
FROM MySiteBundle:Categories C
INNER JOIN C.items');

并最终使用查询生成器.

And definitively use query builder.

===============================================================================

=============================================================================

这是在 Symfony 2 中使用查询生成器的示例.

Here is an example of using query builder in Symfony 2.

public function getAccounts($params = array())
{
    // Build query
    $em = $this->getEntityManager();
    $qb = $em->createQueryBuilder();

    $qb->addSelect('account');
    $qb->addSelect('accountPerson');
    $qb->addSelect('person');
    $qb->addSelect('registeredPerson');
    $qb->addSelect('projectPerson');

    $qb->from('ZaysoCoreBundle:Account','account');

    $qb->leftJoin('account.accountPersons',  'accountPerson');
    $qb->leftJoin('accountPerson.person',    'person');
    $qb->leftJoin('person.registeredPersons','registeredPerson');
    $qb->leftJoin('person.projects',         'projectPerson');
    $qb->leftJoin('projectPerson.project',   'project');

    if (isset($params['accountId']))
    {
        $qb->andWhere($qb->expr()->in('account.id',$params['accountId']));
    }
    if (isset($params['projectId']))
    {
        $qb->andWhere($qb->expr()->in('project.id',$params['projectId']));
    }
    if (isset($params['aysoid']))
    {
        $qb->andWhere($qb->expr()->eq('registeredPerson.regKey',$qb->expr()->literal($params['aysoid'])));
    }
    $query = $qb->getQuery();

  //die('DQL ' . $query->getSQL());
    return $query->getResult();
}

这篇关于查询生成器/DQL 不适用于 INNER JOIN - 语法问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！