Doctrine2 Self Join，如何在没有关系列的情况下加入self？ [英] Doctrine2 Self Join, How to join on self without relationship column?

问题描述

mysql查询示例：

  SELECT 
 a1。* 
 FROM 
协议a1在a1.agreementType = a2.agreementType和a2.id>上的
 LEFT JOIN协议a2 a1.id 
其中
 a2.id为空
  

查询是获取返回类型的最后一个协议。类型很多，我只想列出每种类型的最新协议。我上面的示例查询按预期方式工作，但没有在DQL中运行。注意， agreementType也是另一个表的外键。

解决方案

弄清楚了。以为我会分享。

 从
选择
 a1 
我的模型\协议a1 
左加入我的模型\协议a2 
与a1.agreementType = a2.agreementType AND a2.id> a1.id 
 WHERE 
 a2.id IS NULL 
  

Example mysql query:

SELECT
a1.*
FROM
agreement a1
LEFT JOIN agreement a2 on a1.agreementType = a2.agreementType and a2.id > a1.id
WHERE
a2.id is null

The purpose of the query is to get the last agreement of the type returned. There are many types and I want only a listing of each latest agreement for each type. My example query above works as expected, but no go in DQL.

How would I do this in DQL given I do not have a column that refers to itself? Note that "agreementType" is also a foreign key to a different table as well.

解决方案

Figured it out. Thought I would share.

        SELECT
            a1
        FROM
            My\Model\Agreement a1
            LEFT JOIN My\Model\Agreement a2
                WITH a1.agreementType = a2.agreementType AND a2.id > a1.id
        WHERE
            a2.id IS NULL

这篇关于Doctrine2 Self Join，如何在没有关系列的情况下加入self？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！