想要使用Doctrine在子SELECT上运行COUNT

时间:2023-01-09 06:43:55

I want to count rows returned by subselect with Doctrine. The subselect I have is:

我想用Doctrine计算subselect返回的行数。我的子选择是:

SELECT e FROM \comments\Entities\Event e 
WHERE e.type = 'addComment'
HAVING TIMESTAMPDIFF(MINUTE, e.commentedOnPublished, e.created) < 60)

In SQL I'd achieve the desired result with:

在SQL中,我将通过以下方式获得所需的结果:

SELECT COUNT(*) FROM (
    SELECT e.* FROM square_events as e 
    WHERE e.type = 'addComment' 
    HAVING TIMESTAMPDIFF(minute, e.commentedOnPublished, e.created) < 60
) temp

Does anyone know how this could be achieved with Doctrine?

有谁知道如何用Doctrine实现这一目标?

2 个解决方案

#1


2  

I'm sure I'd already tried this and a thousand other solutions, anyway this is what I ended up with after one of our systems guys pointed out I was being an idiot!

我敢肯定我已经尝试了这个和其他一千个解决方案,无论如何这是我最终得到的结果,我们的一个系统人员指出我是个白痴!

SELECT COUNT(e) as eventCount FROM comments\Entities\Event e 
WHERE e.type = 'addComment'
AND TIMESTAMPDIFF(MINUTE, e.commentedOnPublished, e.created) < 60

One of those days...

那些日子中的一天...

#2


1  

I don't see the reason for wanting a subselect. The following should work just fine:

我没有看到想要一个子选择的原因。以下应该可以正常工作:

$query = $em->createQuery("
    SELECT COUNT(e.id) FROM \comments\Entities\Event e 
    WHERE e.type = 'addComment'
    HAVING TIMESTAMPDIFF(MINUTE, e.commentedOnPublished, e.created) < 60)"
);

$count = $query->getSingleScalarResult();

#1


2  

I'm sure I'd already tried this and a thousand other solutions, anyway this is what I ended up with after one of our systems guys pointed out I was being an idiot!

我敢肯定我已经尝试了这个和其他一千个解决方案,无论如何这是我最终得到的结果,我们的一个系统人员指出我是个白痴!

SELECT COUNT(e) as eventCount FROM comments\Entities\Event e 
WHERE e.type = 'addComment'
AND TIMESTAMPDIFF(MINUTE, e.commentedOnPublished, e.created) < 60

One of those days...

那些日子中的一天...

#2


1  

I don't see the reason for wanting a subselect. The following should work just fine:

我没有看到想要一个子选择的原因。以下应该可以正常工作:

$query = $em->createQuery("
    SELECT COUNT(e.id) FROM \comments\Entities\Event e 
    WHERE e.type = 'addComment'
    HAVING TIMESTAMPDIFF(MINUTE, e.commentedOnPublished, e.created) < 60)"
);

$count = $query->getSingleScalarResult();