doctrine / mongodb-odm

The Official PHP MongoDB ORM/ODM
https://www.doctrine-project.org/projects/doctrine-mongodb-odm/en/latest/
MIT License
1.09k stars 504 forks source link

$elemMatch not working like standard MongoDB one #2251

Closed Dauflo closed 3 years ago

Dauflo commented 3 years ago

Hi,

I'm trying to implement a MongoDB find query using an $elemMatch.

The query i want to recreate the following query:

{
   $nor:[
      {
         "children":{
            $elemMatch:{
               $nin:[
                  ObjectId("5fae9a775ef4492e3c72b3f3"),
                  ObjectId("5fae9a775ef4492e3c72b3f4")
               ]
            }
         }
      }
   ]
}

Right now i have the following query using a queryBuilder:

$query = $this->dm->createQueryBuilder(Target::class);

$query->addNor(
    $query->field('children')->elemMatch(
        $query->expr()->notIn([new ObjectId('5fae9a775ef4492e3c72b3f3'), new ObjectId('5fae9a775ef4492e3c72b3f4')])
    )
);

But when i try to run it, i'm getting the following error:

ErrorException: Notice: Array to string conversion[0] (caught throwable) at /var/www/app/vendor/doctrine/mongodb-odm/lib/Doctrine/ODM/MongoDB/Types/IdType.php line 22.

I cant manage to find any solution or work around.

alcaeus commented 3 years ago

In your elemMatch, you correctly use $query->expr() to generate a new expression object. In the addNor call however, you don't. This causes issues with the query generation. Changing $query->field('children') to $query->expr()->field('children') should fix the issue.

To confirm, I've run the following test. Note that I've used the User class from the test directory, with groups being a ReferenceMany field:

$qb = $this->dm->createQueryBuilder(\Documents\User::class);
$query = $qb
    ->addNor(
        $qb->expr()
            ->field('groups')->elemMatch(
                $qb->expr()
                    ->notIn([
                        new ObjectId('5fae9a775ef4492e3c72b3f3'),
                        new ObjectId('5fae9a775ef4492e3c72b3f4'),
                    ])
            )
    )
->getQuery();

self::assertEquals(
    ['$nor' => [[
        'groups' => [
            '$elemMatch' => ['$nin' => [
                new ObjectId('5fae9a775ef4492e3c72b3f3'),
                new ObjectId('5fae9a775ef4492e3c72b3f4'),
            ]],
        ]
    ]]],
    $query->debug('query')
);
Dauflo commented 3 years ago

Thans for your quick response, i've fix the missing expr.

I found that in the my document, my ReferenceMany was like this:

@MongoDB\ReferenceMany(targetDocument="App\Document\Target", storeAs="id")

As soon as i removed the storeAs="id", the query could execute. Can you explain why this happened?

PS: i want to keep the storeAs="id" to have a proper in the database

alcaeus commented 3 years ago

The difference is in how ODM stores references. When you store the reference as an ID, the document will look like this:

{
    "children": [
        <objectId>,
        <objectId>
    ]
}

When you remove storeAs="id", it defaults to storeAs="ref", which looks like this:

{
    "children": [
        { "ref": <objectId> },
        { "ref": <objectId> }
    ]
}

If you want to store references as identifiers, your query portion does not need an $elemMatch operator but can use $nin directly:


$qb = $this->dm->createQueryBuilder(\Documents\User::class);
$query = $qb
    ->addNor(
        $qb->expr()
            ->field('groups')->notIn([
                new ObjectId('5fae9a775ef4492e3c72b3f3'),
                new ObjectId('5fae9a775ef4492e3c72b3f4'),
            ])
    )
->getQuery();

self::assertEquals(
    ['$nor' => [[
        'groups' => ['$nin' => [
            new ObjectId('5fae9a775ef4492e3c72b3f3'),
            new ObjectId('5fae9a775ef4492e3c72b3f4'),
        ]],
    ]]],
    $query->debug('query')
);
Dauflo commented 3 years ago

Just tried your solution without the elemMatch, but i'm now getting data that i shouldn't get.

I do need to use the elemMatch to ensure the fact that all the elements in my field children are present in the array inside the $nin.

My problem now is that i'm getting Array to string conversion when i try to execute the query.

My work around was to create the database with storeAs='id", and then remove it to run the query. In this case everythings working.

It might maybe due to this check in the file Doctrine\ODM\MongoDB\Persisters line 1108 to 1111

// No further preparation unless we're dealing with a simple reference
if (empty($mapping['reference']) || $mapping['storeAs'] !== ClassMetadata::REFERENCE_STORE_AS_ID || empty((array) $value)) {
    return [[$fieldName, $value]];
}

When i'm changing the condition to $mapping['storeAs'] === ClassMetadata::REFERENCE_STORE_AS_ID my query can run with the elemMatch and i'm getting the good results

I've implemented the query in javascript to test it, this is my results:

Data in database

[
  {
    _id: 5fae9a775ef4492e3c72b3f2,
    children: [ 5fae9a775ef4492e3c72b3f3, 5fae9a775ef4492e3c72b3f4 ]
  },
  {
    _id: 5fc0d3ab793cf33292164f19,
    children: [ 5fae9a775ef4492e3c72b3f3, 5fae9a775ef4492e3c72b3f4, 'test' ]
  }
]

Now i just want to get the object where all of his children are in my array

With the eleMatch, i have the following query:

$nor: [{
    'children': {
        $elemMatch: {
            $nin: [
                ObjectId('5fae9a775ef4492e3c72b3f3'),
                ObjectId('5fae9a775ef4492e3c72b3f4')
            ]
        }
    }
}],
'children': {
    $exists: true,
    $not: {
        $size: 0
    }
}

And i'm getting only the first object, if i add test in the $nin i'm getting both of my objects

Without the elemMatch, i have a query like this:

$nor: [{
    'children': {
        $nin: [
            ObjectId('5fae9a775ef4492e3c72b3f3'),
            ObjectId('5fae9a775ef4492e3c72b3f4'),
        ]
    }
}],
'children': {
    $exists: true,
    $not: {
        $size: 0
    }
}

And with this query, i'm getting both of my objects, which i dont want

Dauflo commented 3 years ago

After a closer look at the code, i might have found something.

In the file Doctrine\ODM\MongoDB\Persisters at the line 1277 there is a condition, and i think $elemMatch is missing in it.

if (in_array($k, ['$exists', '$type', '$mod', '$size'])) {
    continue;
}

Should in my opinion be like this

if (in_array($k, ['$exists', '$type', '$mod', '$size', '$elemMatch'])) {
    continue;
}
alcaeus commented 3 years ago

The comment above this line reads Ignore query operators whose arguments need no type conversion, so I'm not sure that will fix the issue. If you want to try to fix it, tests should confirm your suspicions.

Dauflo commented 3 years ago

By adding this new item in the array i'm not getting the error anymore.

stale[bot] commented 3 years ago

This issue has been automatically marked as stale because it has not had any recent activity. It will be closed in a week if no further activity occurs. Thank you for your contributions.

alcaeus commented 3 years ago

I've created #2298 to fix this. I'll follow up with a more comprehensive rework of how query preparation is done, as there are a few other issues with Expr::elemMatch I want to address.