我想从 poll 表中的行数或行数之和对它们进行排序 vote table在哪里 vote.is_current_vote = true .
投票表跟踪所有投票，但在投票中仅统计每个用户的最新投票，即标记为 is_current_vote .
我正在努力解决如何使用条令查询生成器或本机查询获得投票表的计数。
以下是数据库结构：

create table if not exists user
(
    id int auto_increment
        primary key,
    username varchar(45) not null,
    slug varchar(45) not null,
    created_at datetime not null,
    updated_at datetime null,
    constraint UNIQ_8D93D649989D9B62
        unique (slug),
    constraint UNIQ_8D93D649F85E0677
        unique (username)
)
collate=utf8_unicode_ci
;

create table if not exists poll
(
    id int auto_increment
        primary key,
    user_id int null,
    question varchar(140) not null,
    slug varchar(140) not null,
    is_active tinyint(1) not null,
    created_at datetime not null,
    updated_at datetime null,
    constraint UNIQ_84BCFA45989D9B62
        unique (slug),
    constraint FK_84BCFA45A76ED395
        foreign key (user_id) references user (id)
)
collate=utf8_unicode_ci
;

create index IDX_84BCFA45A76ED395
    on poll (user_id)
;

create table if not exists vote
(
    id int auto_increment
        primary key,
    poll_id int not null,
    user_id int not null,
    created_at datetime not null,
    is_current_vote tinyint(1) not null,
    constraint FK_5A1085643C947C0F
        foreign key (poll_id) references poll (id),
    constraint FK_5A108564A76ED395
        foreign key (user_id) references user (id),
)
collate=utf8_unicode_ci
;

create index IDX_5A1085643C947C0F
    on vote (poll_id)
;

create index IDX_5A108564A76ED395
    on vote (user_id)
;

我在mysql中使用了这个查询，它提供了我想要的数据：

select poll.id, poll.slug, poll.question, poll.created_at,
       u.id, u.username, u.slug, u.profile_picture,
       sum(case when v.is_current_vote = true then 1 else 0 end) as total_votes
from poll
       left join user u on poll.user_id = u.id
       left join vote v on poll.id = v.poll_id
group by poll.id
order by total_votes desc

数据应按票数排序，其中“v.is\u current\u vote=true”。
示例数据（省略上面的一些列以便更容易阅读）：

poll.question, u.username, total_votes  
Is Elvis Alive?, someone, 15  
Is the future bright?, someone_else, 10  
Is this all a dream?, another_user, 5

有没有可能在symfony/querybuilder语句中执行类似的操作，或者我必须使用本机sql？我也不知道该怎么做。我非常感谢你的指导。
这是我当前的原生sql尝试，我从 poll 但投票和用户都是 null :

/**
 * Class PollRepository
 * @package PollBundle\Repository
 */
class PollRepository extends EntityRepository
{

 /**
     * @return \Doctrine\ORM\NativeQuery
     */
    public function findPopular()
    {
        $rsm = new ResultSetMappingBuilder($this->_em);
        $rsm->addRootEntityFromClassMetadata('PollBundle\Entity\Poll', 'poll');
        $rsm->addJoinedEntityFromClassMetadata('PollBundle\Entity\User', 'u', 'poll', 'user', [
            'id' => 'user_id',
            'slug' => 'user_slug',
            'created_at' => 'user_created_at',
            'updated_at' => 'user_updated_at',
            'is_active' => 'user_is_active',
        ]);
        $rsm->addJoinedEntityFromClassMetadata('PollBundle\Entity\Vote', 'v', 'poll', 'votes', [
            'id' => 'vote_id',
            'user_id' => 'vote_user_id',
            'created_at' => 'vote_created_at',
            'updated_at' => 'vote_updated_at',
        ]);

        $sql = '
            SELECT poll.id, poll.slug, poll.question, poll.created_at,
                   u.id, u.username, u.slug, u.profile_picture,
                   sum(case when v.is_current_vote = true then 1 else 0 end) as total_votes
            from poll
                   left join user u on poll.user_id = u.id
                   left join vote v on poll.id = v.poll_id
            group by poll.id
            order by total_votes desc
        ';

        return $this->_em->createNativeQuery($sql, $rsm)->getResult();
    }
}