CODEWITHSUNDEEP
phpmysqlsymfonydoctrine-orm
J1v3
J1v3

Reputation: 84

Doctrine DQL subqueries give a zero result

I have three related entities : Group, GroupDefault, GroupDefaultTranslation on a Mysql server.

The relationships between the entities are types: - ManyToOne between Group and GroupDefault - OneToManu ent GroupsDefault and GroupDefaultTranslation

My goal is to display the list of groups of a user with the corresponding translation in his language parameter.

Here is my QueryBuilder with DQL query generated :

public function findByOwnerWithLanguage($languageCode, $ownerId)
{
        $qb = $this->createQueryBuilder('g')
            ->select('g.name')
            ->addSelect('gdt.content AS name')
            ->join('g.parent', 'gd')
            ->join('gd.translations', 'gdt')
            ->where('g.owner = :owner');

            //création de l'expression AND
            $orModule = $qb->expr()->andX();
            $orModule->add($qb->expr()->eq('g.parent', '0'));
            $orModule->add($qb->expr()->eq('gdt.locale', ':language'));
            $orModule->add($qb->expr()->eq('g.owner', ':owner'));


         //Ajout de l'expression à la requête
         $qb->orWhere($orModule)
            ->setParameter('language', $languageCode, \PDO::PARAM_STR)
            ->setParameter('owner', $ownerId)
            ->orderBy('g.isMyLightbox', 'DESC')
            ->getQuery()
            ->getResult();
    return $qb;

    // DQL Query :

    // SELECT g.name, gdt.content AS name
    // FROM Horyou\Bundle\CoreBundle\Entity\Group g
    // INNER JOIN g.parent gd
    // INNER JOIN gd.translations gdt
    // WHERE g.owner = :owner
    // OR (g.parent = 0 AND gdt.locale = :language AND g.owner = :owner)
    // ORDER BY g.isMyLightbox DESC

}

And here is the output of the object var_dump :

object(stdClass)[1368]
  public '__CLASS__' => string 'Doctrine\ORM\QueryBuilder' (length=25)
  public '_em' => 
    object(stdClass)[1362]
      public '__CLASS__' => string 'Doctrine\ORM\EntityManager' (length=26)
      public 'config' => string 'Doctrine\ORM\Configuration' (length=26)
      public 'conn' => string 'Doctrine\DBAL\Connection' (length=24)
      public 'metadataFactory' => string 'Doctrine\ORM\Mapping\ClassMetadataFactory' (length=41)
      public 'unitOfWork' => string 'Doctrine\ORM\UnitOfWork' (length=23)
      public 'eventManager' => string 'Symfony\Bridge\Doctrine\ContainerAwareEventManager' (length=50)
      public 'proxyFactory' => string 'Doctrine\ORM\Proxy\ProxyFactory' (length=31)
      public 'repositoryFactory' => string 'Doctrine\ORM\Repository\DefaultRepositoryFactory' (length=48)
      public 'expressionBuilder' => string 'Doctrine\ORM\Query\Expr' (length=23)
      public 'closed' => boolean false
      public 'filterCollection' => string 'Doctrine\ORM\Query\FilterCollection' (length=35)
  public '_dqlParts' => 
    array (size=9)
      'distinct' => boolean false
      'select' => string 'Array(2)' (length=8)
      'from' => string 'Array(1)' (length=8)
      'join' => string 'Array(1)' (length=8)
      'set' => string 'Array(0)' (length=8)
      'where' => string 'Doctrine\ORM\Query\Expr\Orx' (length=27)
      'groupBy' => string 'Array(0)' (length=8)
      'having' => null
      'orderBy' => string 'Array(1)' (length=8)
  public '_type' => int 0
  public '_state' => int 1
  public '_dql' => string 'SELECT g.name, gdt.content AS name FROM Horyou\Bundle\CoreBundle\Entity\Group g INNER JOIN g.parent gd INNER JOIN gd.translations gdt WHERE g.owner = :owner OR (g.parent = 0 AND gdt.locale = :language AND g.owner = :owner) ORDER BY g.isMyLightbox DESC' (length=251)
  public 'parameters' => 
    array (size=2)
      0 => string 'Doctrine\ORM\Query\Parameter' (length=28)
      1 => string 'Doctrine\ORM\Query\Parameter' (length=28)
  public '_firstResult' => null
  public '_maxResults' => null
  public 'joinRootAliases' => 
    array (size=2)
      'gd' => string 'g' (length=1)
      'gdt' => string 'g' (length=1)

My problem is that the result is zero. Does anyone see what is the error in my QueryBuilder?

Upvotes: 1

Views: 189

Answers (1)

Alex
Alex

Reputation: 1183

Actually it's not a result - it's just a dump of a query builder object. I think you have to do something like this:

...
$qb->orWhere($orModule)
        ->setParameter('language', $languageCode, \PDO::PARAM_STR)
        ->setParameter('owner', $ownerId)
        ->orderBy('g.isMyLightbox', 'DESC');
$result = $qb->getQuery()->getResult();

return $result;

Upvotes: 0

Related Questions