DQL左联接-SQL示例 [英] DQL LEFT JOIN - sql example

问题描述

运行良好的SQL是:

SELECT ro.id_role
       ,rr.id_role_resource
       ,re.id_resource
    FROM resource re
    LEFT JOIN role_resource rr
        ON rr.resource_id = re.id_resource
    LEFT JOIN role ro
        ON ro.id_role = rr.role_id

现在我必须写DQL-这是表shema:

now I must write DQL- this is table shema:

角色: id_role，名称

角色资源: id_role_resource，role_id，resource_id，权限

资源: id_resource，控制器，操作...

在表role_resource中没有相应的记录.这就是为什么我需要在DQL中保留此左联接查询的原因.

in the last table are no corresponding records in the table role_resource. That's why i need this left Join Query in DQL.

推荐答案

通常，如果您有一个带有 $ roles 属性和正确批注的 Resource 实体( OneToMany ， ManyToMany )，那么您的联接表( role_resource )将不需要或不需要其auto_increment ID.

Normally if you would have an Resource entity with a $roles property and the correct annotations (OneToMany, ManyToMany) then your join table (role_resource) would not have or need and auto_increment id of its own.

这是因为Doctrine处理关联信息并基于它们知道何时需要连接表.教义还知道在基于相同关联信息加入两个实体时要使用什么条件.

This is because Doctrine handles association information and knows when a join table is needed based on them. Doctrine also knows what conditions to use when joining two entities based on the same association information.

因此，如果SQL查询通过使用2个连接子句通过第三个表将两个表连接起来，则仅需要告知DQL查询关联名称，并且它将具有所有连接表"和"加入条件"信息可用.

So if an SQL query would join two tables via a third table by using 2 join clauses, a DQL query would only need to be told the association name and it would have all "join table" & "join conditions" information available.

一个简单的查询如下:

SELECT
        /* i assume $idRole is the name of the property
         * mapped to the id_role column
         */
        ro.idRole,

        /* i assume $idResource is the name of the property
         * mapped to the id_resource column
         */
        re.idResource

FROM YourNamespace\Resource re
JOIN re.roles

使用等效的queryBuilder语法:

With equivalent queryBuilder syntax:

$this->getEntityManager()->createQueryBuilder()
    ->select(array('ro.idRole', 're.idResource'))
    ->from('YourNamespace\Resource', 're')
    ->join('re.roles');

但是 JOIN 默认为 INNER JOIN ，因此我们希望通过将查询重写为以下方式来解决此问题:

But JOIN defaults to an INNER JOIN so we would want to fix this by rewriting the query as:

SELECT  ro.idRole,
        re.idResource
FROM YourNamespace\Resource re
LEFT JOIN re.roles

使用等效的queryBuilder语法:

With equivalent queryBuilder syntax:

$this->getEntityManager()->createQueryBuilder()
    ->select(array('ro.idRole', 're.idResource'))
    ->from('YourNamespace\Resource', 're')
    ->leftJoin('re.roles');

但是，如果您想要 role_resource 表具有其自身的auto_increment ID，并且可以在Doctrine查询中进行访问，则Doctrine需要了解该表-需要映射到单独的实体，并且需要明确说明您正在加入资源，RoleResources和Roles.

If however you want the role_resource table to have an auto_increment id of its own and be accessible in Doctrine queries, then Doctrine needs to know about that table -- it needs to be mapped to a separate entity and you need to explicitate the fact that you're joining Resources, RoleResources and Roles.

这篇关于DQL左联接-SQL示例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！