将SQL转换为Doctrin2 QueryBuilder

Question

我需要帮助来转换一个有效的SQL查询为createQueryBuilder。我的问题是我不知道如何在createQueryBuilder中的SELECT上使用LEFT JOIN。

SELECT username, count(c.user_owner_id) as num_contact, a_g.name as 
       group_name 
       FROM `oro_user` as u 
       INNER JOIN `oro_user_access_group` as u_g on u.id=u_g.user_id    
       INNER JOIN `oro_access_group` as a_g on u_g.group_id=a_g.id 
       LEFT JOIN
      (SELECT cc.user_owner_id 
              FROM `orocrm_contact` as cc 
              INNER JOIN`orocrm_contact_to_contact_grp` as cc_g on cc_g.contact_id=cc.id 
              INNER JOIN `orocrm_contact_group` 
                    as c_g on cc_g.contact_group_id=c_g.id  
              WHERE  c_g.label='New One' and cc.semester_contacted='2017A') 
      as c on u.id=c.user_owner_id 
      WHERE a_g.name='Full-timer' and u.enabled = 1 and u.gender='male' 
      GROUP BY u.id 
      ORDER BY num_contact

我有两个问题下面，我希望用户从联系人LEFT JOIN的结果

 $user = $this->em->getRepository('OroUserBundle:User')->createQueryBuilder('u')
        ->select('u.username')
        ->innerJoin('u.groups','g')
        ->andWhere('g.name = :group')
        ->setParameter('group', 'Full-timer')
        ->getQuery();

 $contacts = $this->em->getRepository('OroContactBundle:Contact')->createQueryBuilder('c')
        ->select('c')
        ->innerJoin('c.groups','g')
        ->andWhere('g.label = :group')
        ->andWhere('c.semester_contacted = :sem')
        ->setParameter('group', 'New One')
        ->setParameter('sem', '2017A')
        ->setMaxResults(1)
        ->getQuery();

Answer 1

这是一个相当复杂的查询，由于ORM更靠近实体而不是数据库，我不确定您是否可以直接将QueryBuilder“转储”到->join()中。关于工作的DBAL QueryBuilder，好消息是：

$dbalQueryBuilder
    ->from('user_table as u')
    ...
    ->join('u', '('.$otherDbalQueryBuilder->getSQL().')', 'c')

这是从记忆中得到的，所以它可能有一点不同，但是类似的东西。

这样，您就可以获得所需的所有字段，但不会获得任何实体。幸运的是Doctrine provides ways to build entities from Native SQL using ResultSetMapping。

$userWithContacts = $entityManager->createNativeQuery(
    $dbalQueryBuilder->getSQL(),
    $yourResultSetMapping
);

我知道这需要更多的代码，可能没有仅仅使用ORM QueryBuilder那么好，但我发现它往往是处理现有查询的最好方法，这些查询需要以某种方式移植到ORM。