php - symfony createQueryBuilder Sql 查询

Question

我有表实体 - 开发人员和用户。在用户中，我有三个角色 ROLE_DEVELOPER ROLE_FREELANCER 和 ROLE_COMPANY。每个起始用户的所有角色，如果这是用户中的开发人员，则具有多对一开发人员。在开发人员中，如果公司的开发人员(ROLE_DEVELOPER)他有团队，如果没有团队，那么这是开发人员单例并且有 ROLE_FREELANCER。每个团队都有经理，该经理是实体用户，在实体用户中有团队。 user.team.id = Developer.team.id - 该管理器的所有开发人员。我需要所有拥有这些技能但没有这些标签的开发人员。如果这是 ROLE_DEVELOPER 我需要找到他的经理，如果不是就是这样。我尝试使用 sql 但我需要使用 createQueryBuilder 来完成如何创建 for $db = $this->getEntityManager()->createQueryBuilder();这个查询？SQL查询:

    SELECT u.email , if(u.role='ROLE_DEVELOPER', m.email, u.email) as send_email, m.unsubscribe_date as uns_date
FROM users as u
left join developers as d on d.id=u.developer_id
left join users as m on m.teams_id=d.team_id
WHERE u.unsubscribe_date <= now()
and d.skills like '%php%'
and d.skills like '%java%'
and u.role in ('ROLE_FREELANCER', 'ROLE_DEVELOPER')
and d.tags not like '%india%'
and d.tags not like '%black_list%'
and (m.unsubscribe_date <= now() OR m.unsubscribe_date is null)
group by send_email

它可以工作，但没有通过 send_email 进行分组

$em = $this->getEntityManager();

    $q = $em->createQuery(

    'SELECT u.email as u_eamil, m.email, u.roles, m.unsubscribeDate as uns_date
    FROM ArtelProfileBundle:Users as u
    left join ArtelProfileBundle:Developer as d WITH d.id=u.developer
    left join ArtelProfileBundle:Users as m WITH m.teams=d.teams
    WHERE u.unsubscribeDate <= :today
    and d.skills like \'%php%\'
    and d.skills like \'%java%\'
    and u.roles in (\'ROLE_FREELANCER\', \'ROLE_DEVELOPER\')
    and d.tags not like \'%india%\'
    and d.tags not like \'%black_list%\'
    and (m.unsubscribeDate <= :today OR m.unsubscribeDate is null)
    GROUP BY send_email
    '
    )
    ->setParameter('today', new \DateTime())
    ;
    $entities = $q->getArrayResult();

这不起作用，不明白为什么:

        $em = $this->getEntityManager();

    $qb = $em->createQueryBuilder();

    $qb
        ->from('ArtelProfileBundle:Users', 'u')
        ->select('u.email as u_eamil, m.email, u.roles, m.unsubscribeDate as uns_date')
        ->leftJoin('u.developer', 'd') //this works assuming the doctrine mappings are correct on the $developer property in the ArtelProfileBundle:Users' entity
        ->leftJoin('d.teams', 'm')
        ->where('u.unsubscribeDate <= :today')
        ->andWhere($qb->expr()->like('d.skills', '%php%'))
        ->andWhere($qb->expr()->like('d.skills', '%java%'))
        ->andWhere($qb->expr()->in('ROLE_FREELANCER', 'ROLE_DEVELOPER'))
        ->andWhere($qb->expr()->notLike('d.tags', '%india%'))
        ->andWhere($qb->expr()->notLike('d.tags', '%black_list%'))
        ->andWhere($qb->expr()->orX('m.unsubscribeDate <= :today', $qb->expr()->isNull('m.unsubscribeDate')))
        ->setParameter('today', new \DateTime());
    $entities = $qb->getQuery()->getArrayResult();

[Syntax Error] line 0, col 203: Error: Expected StateFieldPathExpression | string | InputParameter | FunctionsReturningStrings | AggregateExpression, got '%'

已解决如果用户 ROLE_DEVELOPER 为开发人员团队获取电子邮件管理器，我需要。为此，我创建自定义 dql，在配置中添加此 dql

        dql:
        string_functions:
            IF: Artel\ProfileBundle\Doctrine\ORM\Query\AST\Functions\IfFunction

并创建

class IfFunction extends FunctionNode
{
private $expr = array();

public function parse(\Doctrine\ORM\Query\Parser $parser)
{
    $parser->match(Lexer::T_IDENTIFIER);
    $parser->match(Lexer::T_OPEN_PARENTHESIS);
    $this->expr[] = $parser->ConditionalExpression();

    for ($i = 0; $i < 2; $i++)
    {
        $parser->match(Lexer::T_COMMA);
        $this->expr[] = $parser->ArithmeticExpression();
    }

    $parser->match(Lexer::T_CLOSE_PARENTHESIS);
}

public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
{
    return sprintf('IF(%s, %s, %s)',
        $sqlWalker->walkConditionalExpression($this->expr[0]),
        $sqlWalker->walkArithmeticPrimary($this->expr[1]),
        $sqlWalker->walkArithmeticPrimary($this->expr[2]));
}
}

和我的查询生成器

     public function notificationProject($paramFetcher)
     {
     $em = $this->getEntityManager();
     $qb = $em->createQueryBuilder();

    $qb
        ->from('ArtelProfileBundle:Users', 'u')
        ->select('u.id, u.email as u_email, m.email, d.skills, u.roles, m.unsubscribeDate as uns_date')
        ->addSelect("IF(u.roles = 'ROLE_DEVELOPER', m.email, u.email) as send_email")

        ->leftJoin('u.developer', 'd') //this works assuming the doctrine mappings are correct on the $developer property in the ArtelProfileBundle:Users' entity
        ->leftJoin('d.teams', 't')
        ->leftJoin('t.users', 'm')
        ->where('u.unsubscribeDate <= :today');
    foreach($paramFetcher[1] as $skill){
        if ($skill)
        $qb
            ->andWhere($qb->expr()->like('d.skills', $qb->expr()->literal('%"' . $skill . '"%')));
    }
    $qb
        ->andWhere($qb->expr()->in('u.roles', array('ROLE_FREELANCER', 'ROLE_DEVELOPER')));

    foreach($paramFetcher[0] as $tag){
        if ($tag)
        $qb
            ->andWhere($qb->expr()->notlike('d.tags', $qb->expr()->literal('%"' . $tag . '"%')));
    }
    $qb
        ->andWhere($qb->expr()->orX('m.unsubscribeDate <= :today', $qb->expr()->isNull('m.unsubscribeDate')))
        ->groupBy('send_email')
        ->setParameter('today', new \DateTime());
    $entities = $qb->getQuery()->getArrayResult();
}

Answer 1

在第二个查询中，您没有选择相同的内容。 (第二个查询中的 send_email 是谁？)

如果您确实想释放查询构建器的强大功能，您可以编写如下内容:

$em = $this->getEntityManager('ArtelProfileBundle:Users'); 

$qb = $em->createQueryBuilder('u');

$qb->select('u.email as u_eamil, m.email as send_email, u.roles, m.unsubscribeDate as uns_date')
   ->leftJoin('u.developer', 'd') //this works assuming the doctrine mappings are correct on the $developer property in the ArtelProfileBundle:Users' entity
   ->leftJoin('d.teams', 'm')     
   ->where('u.unsubscribeDate <= :today')
   ->andWhere($qb->expr()->like('d.skills', '%php%'))
   ->andWhere($qb->expr()->like('d.skills', '%java%'))
   ->andWhere($qb->expr()->in('u.role',array('ROLE_FREELANCER', 'ROLE_DEVELOPER')))
   ->andWhere($qb->expr()->notLike('d.tags', '%india%'))
   ->andWhere($qb->expr()->notLike('d.tags', '%black_list%'))
   ->andWhere($qb->expr()->orX('m.unsubscribeDate <= :today', $qb->expr()->isNull('m.unsubscribeDate')))
   ->groupBy('send_email')
   ->setParameter('today', new \DateTime());
$entities = $qb->getQuery()->getArrayResult();