Orm: Criteria::create()->orWhere() transform to And in Mysql

[Dawen18]

Bug Report

| Q | A
|------------ | ------
| BC Break | no
| Version | 2.5.11

name     : symfony/orm-pack
descrip. : A pack for the Doctrine ORM
keywords : 
versions : * v1.0.5
type     : symfony-pack
license  : MIT License (MIT) (OSI approved) https://spdx.org/licenses/MIT.html#licenseText
source   : [git] https://github.com/symfony/orm-pack.git 1b58f752cd917a08c9c8df020781d9c46a2275b1
dist     : [zip] https://api.github.com/repos/symfony/orm-pack/zipball/1b58f752cd917a08c9c8df020781d9c46a2275b1 1b58f752cd917a08c9c8df020781d9c46a2275b1
names    : symfony/orm-pack

requires
doctrine/doctrine-bundle ^1.6.10
doctrine/doctrine-migrations-bundle ^1.3
doctrine/orm ^2.5.11
php ^7.0

Summary

In my entity when i use matching of ArrayCollection with a criteria how contains a or, the executed query is a and.

Current behavior

Sql And executed instead of Or

How to reproduce

Entity:

class MyClass {

    /**
     * @var Collection
     *
     * @ORM\ManyToMany(targetEntity="App\Entity\PhoneNumber", inversedBy="thirdParties", cascade={"all"})
     */
    private $phoneNumbers;

    /**
     * @return Collection | PhoneNumber[]
     */
    public function getFixesPhoneNumbers(): Collection
    {
        $criteria = Criteria::create()
            ->where(Criteria::expr()->eq('type', PhoneNumberType::FIXED_LINE))
            ->orWhere(Criteria::expr()->eq('type', PhoneNumberType::VOIP));

        return $this->phoneNumbers->matching($criteria);
    }
}

Xdebug info:

Executed Mysql query:

SELECT * FROM telephones te JOIN third_party_phone_number t ON t.phone_number_id = te.id WHERE t.third_party_id = 229 AND te.type = 0 AND te.type = 6;

Expected behavior

The Mysql Query should be

SELECT * FROM telephones te JOIN third_party_phone_number t ON t.phone_number_id = te.id WHERE t.third_party_id = 229 AND (te.type = 0 OR te.type = 6);

I also tried to use in

Criteria::expr()->in('type', [PhoneNumberType::FIXED_LINE, PhoneNumberType::VOIP])

But i have an Exception (see below) and if i transform array to string php says that the parameter must be an array.

Thanks,
David

[Ocramius]

@Dawen18 can you please try an upgrade first, and then see if the problem persists? 2.5.x is not maintained except for security issues.

[Dawen18]

@Ocramius I am really sorry but I made a mistake, I have the latest version of doctrine installed (2.6.3), as you can see below. I watched the dependencies of the package symfony/orm-pack instead of the version actually installed.

[Ocramius]

I've marked this for fixing in the next patch release, as it looks quite bad.

[lcobucci]

@Ocramius @Dawen18 I've managed to reproduce this, it seems like the cause is here: https://github.com/doctrine/doctrine2/blob/6e93f5bb72cae985e2bd69445c584d14b1489777/lib/Doctrine/ORM/Persisters/Collection/ManyToManyPersister.php#L261-L279

I'll create a PR with the failing test for this.

[lcobucci]

@Ocramius and the plot thickens: that seems to not work since its creation (Jan 2015)

[Dawen18]

Hi @lcobucci @Ocramius ,

Now i have time to fix this, how can i help?

[ns3777k]

I took a quick look at this.

loadCriteria uses SqlValueVisitor in expandCriteriaParameters that returns params missing OR statement.

SqlExpressionVisitor should be used to walk through complex conditions. there is a sample right in count function.

$conditions = [];

if ($criteria && ($expression = $criteria->getWhereExpression()) !== null) {
    $persister = $this->uow->getEntityPersister($targetClass->name);
    $visitor = new SqlExpressionVisitor($persister, $targetClass);
    $conditions[] = $visitor->dispatch($criteria->getWhereExpression());
}

it generates (t0.type = ? OR t0.type = ?) in this case. the only problem i did not solve here, is how to use the right table alias.

not sure if this is a right way, but hope this helps anyway.

@Ocramius @lcobucci appreciate a hint on this 🙏

[lcobucci]

@Dawen18 @ns3777k we're working to finalise the 2.6.x series with only the really urgent things. Since you're able to achieve the same result with DQL or filtering the collection (not necessarily ideal since you'll be hydrating everything there to be able to filter), I'll move this to 2.7.1 :+1:

[xScorp1oNx]

The bug still exists in version 2.7.4. When is the bug fix planned?

[gjdanis]

I think there's a similar problem with IN expressions:

$criteria = Criteria::create()
    ->where(Criteria::expr()->in('username', $usernames));

 return $this->users->matching($criteria);

An exception occurred while executing 'SELECT ... FROM ... te JOIN ... t ON t... = te... WHERE t... = ? AND te.UserName IN ?' with params [.., [..., ...]]:

Array to string conversion

Here it looks like the SQL generated is incorrect. After failing to get this to work I tried to switch my query to use OR conditions instead but encountered this issue.