使用Doctrine 2查询生成器在where中执行子查询

Question

我正在尝试(但没有成功(在Doctrine2查询生成器中转换此SQL查询。我应该如何将子选择包含在Where with Querybuilder中？

我正在尝试用querybuilder:实现SQL查询

SELECT * FROM `message` A 
WHERE A.id =
(SELECT B.id FROM `message` B 
WHERE (A.user_id = B.user_id AND A.receiver_id = B.receiver_id) OR (A.user_id = B.receiver_id AND A.receiver_id = B.user_id)
ORDER BY creationdate DESC 
LIMIT 1)

到目前为止，我在消息存储库中尝试了这样的东西，但我想我离实现它的好方法还很远：

public function getConversations()
{
$qb = $this
->createQueryBuilder('A')
->Where('A.id IN 
(SELECT B.id FROM Message B 
WHERE (A.user_id = B.user_id AND A.receiver_id = B.receiver_id) OR (A.user_id = B.receiver_id AND A.receiver_id = B.user_id)
ORDER BY creationdate DESC
LIMIT 1')
;
return $qb
->getQuery()
->getResult()
;
}
}

此触发错误消息：

错误：未定义类"Message"。

Answer 1

对于感兴趣的人，我终于找到了解决方案。我做了这样的查询(如果有人有另一个解决方案，我很感兴趣(：

public function getConversations()
{
$rawSql = "SELECT * FROM `message` A 
WHERE A.id =
(SELECT B.id FROM `message` B 
WHERE (A.user_id = B.user_id AND A.receiver_id = B.receiver_id) OR (A.user_id = B.receiver_id AND A.receiver_id = B.user_id)
ORDER BY creationdate DESC 
LIMIT 1)
";
$stmt = $this->getEntityManager()->getConnection()->prepare($rawSql);
$stmt->execute([]);
return $stmt->fetchAll();
}