CODEWITHSUNDEEP
symfonydoctrine-ormdqldoctrine-query
Javad
Javad

Reputation: 4397

doctrine dql exception: Illegal offset type in /var/www/Symfony/vendor/doctrine/orm/lib/Doctrine/ORM/Query/SqlWalker.php line 601

I want to produce a DQL for following MySQL query:

SELECT * FROM `folders` AS `t` WHERE `t`.`Library` = @myLib AND AND `t`.`Id` NOT IN (
   SELECT DISTINCT(`f`.`Id`) FROM `folders` AS `f` JOIN `folders` AS `ff` ON (`f`.`Position` LIKE CONCAT(`ff`.`Position`,  '%')) WHERE `ff`.`Active` = 1 AND `ff`.`Library` = @myLib AND `f`.`Library` = @myLib
)
ORDER BY  `t`.`Position` ASC

The query works fine in mySQL and returns correct records.
To generate DQL I've tried both below options:
1.

$query = $em->createQuery("SELECT F FROM MyBundle:Folders T WHERE T.Library = :libid AND T.id NOT IN (
         SELECT DISTINCT(F.id) FROM MyBundle:Folders F JOIN MyBundle:Folders FF WITH F.Position LIKE CONCAT(FF.Position, '%') AND F.Library = :libid AND FF.Library = :libid AND FF.Active = true
      ) ORDER BY T.Position ASC")
      ->setParameter('libid', $library);
$result = $query->getResult();

2.

$q1 = $this->createQueryBuilder('F')
      ->select('DISTINCT(F.id)');
$q1->join('\MyBundle\Entity\Folders', 'FF', 'WITH', $q1->expr()->like('F.Position', $q1->expr()->literal('CONCAT(FF.Position, \'%\')')))
    ->where('FF.Active = true')
    ->andWhere("FF.Library = '$library'")
    ->andWhere("F.Library = '$library'");

$q2 = $this->createQueryBuilder('T');
$q2->where('T.Library = :libid')
    ->andWhere($q2->expr()->notIn('T.id', $q1->getDQL()))
    ->setParameter('libid', $library)
    ->orderBy('T.Position', 'ASC');
$result = $q2->getQuery()->getResult();

In my perspective it seems OK but I don't know why in both ways it produce following exception:

ContextErrorException: Warning: Illegal offset type in
/var/www/Symfony/vendor/doctrine/orm/lib/Doctrine/ORM/Query/SqlWalker.php line 601

Any help will be appreciated.

Upvotes: 1

Views: 719

Answers (2)

Olivier D
Olivier D

Reputation: 23

I've encountered the same error and it seems that this has been fixed in latest trunk of Doctrine/ORM.
Using "2.5.*@dev" as version in your composer.json for doctrine/orm should fix this bug and will let you do what you want in a single query.

Upvotes: 1

Javad
Javad

Reputation: 4397

It seems no one has an answer for this. I found a temporary solution as below (I call it temporary because I'm changing my unique query to two separate queries and it seems the issue is in core of doctrine).

$qb = $this->createQueryBuilder('F')
    ->select('DISTINCT(F.id)');
$qb->join('\MyBundle\Entity\Folders', 'FF', 'WITH', 'F.Position LIKE CONCAT(FF.Position, \'%\')')
    ->where('FF.Active = true')
    ->andWhere("FF.Library = :library")
    ->andWhere("F.Library = :library")
    ->setParameter('library', $library);

$included_folders = $qb->getQuery()->getArrayResult();

$query = $this->createQueryBuilder('F')
    ->where('F.Active = false')
    ->andWhere('F.Library = :library')
    ->setParameter('library', $library)
    ->orderBy('F.Position', 'ASC');
if (!empty($included_folders)) {
    if (count($included_folders) > 1)
    {
        foreach ($included_folders as $index => $value)
        {
            if (is_array($value)) 
            {
                $included_folders[$index] = !empty($value['id']) ? $value['id'] : $value[1];
            }
        }
        $query->andWhere($query->expr()->notIn('F.id', $included_folders));
    }
    else {
        $query->andWhere('F.id != :folder ')
            ->setParameter('folder', $included_folders[0]);
    }
}
$result = $query->getQuery()->getResult();

As you see instead of getting the dql from my first query and putting it inside my second dql in notIn section which will lead to the warning message, I execute the first query and get the results then put the results inside notIn if amount of returned values are more than one, otherwise it should be in regular !=. This solved my problem for now, but as you see amount of transactions are now increased
If anyone has a better solution or any fix for the warning I will be thankful.

Upvotes: 1

Related Questions