Join Subquery with Doctrine 2 Dbal

Join subquery with doctrine 2 DBAL

I've found a solution by adapting this DQL example to DBAL. The trick is to get the raw SQL of the subquery, wrap it in brackets, and join it. Parameters used in the subquery must be set in the main query:

Important it's the createQueryBuilder of connection not the one of the entity manager.

$subSelect = $connection->createQueryBuilder()
    ->select(array('userSurveyID', 'MIN(timestamp) timestamp'))
    ->from('user_survey_status_entries')
    // Instead of setting the parameter in the main query below, it could be quoted here:
    // ->where('status = ' . $connection->quote(UserSurveyStatus::ACCESSED))
    ->where('status = :status')
    ->groupBy('userSurveyID');

$select = $connection->createQueryBuilder()
    ->select($selectColNames)
    ->from('user_surveys', 'us')
    // Get raw subquery SQL and wrap in brackets.
    ->leftJoin('us', sprintf('(%s)', $subSelect->getSQL()), 'firstAccess', 'us.userSurveyID = firstAccess.userSurveyID')
    // Parameter used in subquery must be set in main query.
    ->setParameter('status', UserSurveyStatus::ACCESSED)
    ->where('us.surveyID = :surveyID')->setParameter('surveyID', $surveyID);

Doctrine DBAL - SELECT join two tables, with a prefix in the keys of the result

I have just faced with the same problem and I resolved it in next way:

$result = $this->createQueryBuilder('comments')
        ->select([
            'comments.id as id',
            'comments.post_id as postId',
            'comments.userId as userId',
            'comments.userName as userName',
            'comments.userEmail as userEmail',
            'comments.parentId as parentId',
            'comments.postedAt as postedAt',
            'comments.status as status',
            'comments.comment as comment',
            'user.fullname as user_userName',
            'user.email as user_email',
       ])
       ->leftJoin(
              'Entity\User', 'user',
              Query\Expr\Join::WITH,
              'comments.userId = user.id'
      )
      ->where('comments.post_id=:postId')->setParameter('postId', $postId)
      ->getQuery()
      ->getResult(Query::HYDRATE_ARRAY)
;

So, to add prefixes to table data, just declare them into "select" statement as

table.var1 as prefix_var1, table.var2 as prefix_var2

and the result will be

[
    prefix_var1 => value,
    prefix_var2 => value,
]

one more thing that you can do, when you declare select as:

table1, table2.var1 as prefix_var1, table2.var2 as prefix_var2

you will get the next result

[
    0 => [ // it's come from table1
           var1 => value,  
           var2 => value,
    ],
    prefix_var1 => value, // it's come from table2
    prefix_var2 => value, // it's come from table2
]

Doing a WHERE .. IN subquery in Doctrine 2

This is how I would try it:

/** @var Doctrine\ORM\EntityManager $em */
$expr = $em->getExpressionBuilder();
$em->createQueryBuilder()
   ->select(array('DISTINCT i.id', 'i.name', 'o.name'))
   ->from('Item', 'i')
   ->join('i.order', 'o')
   ->where(
       $expr->in(
           'o.id',
           $em->createQueryBuilder()
               ->select('o2.id')
               ->from('Order', 'o2')
               ->join('Item', 
                      'i2', 
                      \Doctrine\ORM\Query\Expr\Join::WITH, 
                      $expr->andX(
                          $expr->eq('i2.order', 'o2'),
                          $expr->eq('i2.id', '?1')
                      )
               )
               ->getDQL()
       )
   )
   ->andWhere($expr->neq('i.id', '?2'))
   ->orderBy('o.orderdate', 'DESC')
   ->setParameter(1, 5)
   ->setParameter(2, 5)
   ;

I didn't test this of course, and made some assumptions about your models. Possible problems:

• Limit: this has been somewhat of a problem in Doctrine 2, it seems query builder is not very good at accepting limits. Do take a look here, here and here.
• The IN clause is usually used with an array, but I think it will work with a subquery.
• You probably can use the same parameter ?1, instead of two parameters (because they're the same value), but I'm not sure.

Concluding, this may not work first time, but will surely put you on the right track. Do tell us the final 100% correct answer afterwards.

---

Related Topics