Doctrine ODM / PHP / MongoDB - Can't query Referenced Elements

Question

I have an Event, that has a Privilege and this Privilege references a User. I'm trying to get all the events associated with a User, but I can't seem to get doctrine working as I'd like.

Here is my Mongo Schema

Array
(
    [_id] => 4e63903cbc3470a2cd000002
    [date] => 2011-10-09
    [name] => Event1
    [privileges] => Array
        (
            [0] => Array
                (
                    [user] => Array
                        (
                            [$ref] => users
                            [$id] => 4e63611cbc347053a2000001
                            [$db] => eventdb
                        )

                    [role] => admin
                )

        )

    [url] => Event1
)

The doctrine Event Entity:

class Event {

    /**
     * @Id
     */
    protected $id;

    /**
     * @String
     */
    protected $name;

    /**
     * @EmbedMany(targetDocument="\Event\Privilege")
     */
    protected $privileges = array();
}

The Privilege Entity:

class Privilege {

    /**
     * @ReferenceOne(targetDocument="\User", cascade={"persist"})
     */
    protected $user;

    /**
     * @String
     */
    protected $role;
}

And the User entity:

class User {

    /**
     * @Id
     */
    protected $id;

    /**
     * @String
     */
    private $firstname;

    /**
     * @String
     */
    private $lastname;

    /**
     * @String
     */
    protected $username;
}

I tried the following with no success

Example 1:
    $privilege_repository = $dm->getRepository('\Event\Privilege');
    $qb1 = $privilege_repository->createQueryBuilder('\Event\Privilege') ->field('user.$id')->equals('4e63611cbc347053a2000001');
    $query1 = $qb1->getQuery();
    $result1 = $query1->execute();
    $result1->count() :0
    Example 2:
    $privilege_repository = $dm->getRepository('\Event\Privilege');
    $qb2 = $privilege_repository->createQueryBuilder('\Event\Privilege') ->field('user.$id')->equals(new \MongoId('4e63611cbc347053a2000001'));
    $query2 = $qb2->getQuery();
    $result2 = $query2->execute();
    $result2->count() :0
    Example 3:
    $privilege_repository = $dm->getRepository('\Event\Privilege');
    $qb3 = $privilege_repository->createQueryBuilder('\Event\Privilege') ->field('user')->equals(new \MongoId('4e63611cbc347053a2000001'));
    $query3 = $qb3->getQuery();
    $result3 = $query2->execute();
    $result3->count() :0

I have also tried using the Event class but it doesn't get more success... I'm surely missing something but I have no clue what! Any help is welcome.

Thanks.

Just to be sure I'm clear I'm trying to get from a User ID the Events ID associated. — remi, Sep 04 '11 at 15:49

score 7 · Accepted Answer · answered Sep 05 '11 at 18:40

7

** One night later ** Finally got it working!

Example 2:
$privilege_repository = $dm->getRepository('\Event');
$qb2 = $privilege_repository->createQueryBuilder('\Event') ->field('privileges.user.$id')->equals(new \MongoId('4e63611cbc347053a2000001'));
$query2 = $qb2->getQuery();
$result2 = $query2->execute();
$result2->count() :3

You really need to use the new MongoId() otherwise it always returns empty!

answered Sep 05 '11 at 18:40

remi

121
1
7

1

Also, this one works too ->findBy(['privileges.user.$id' => new \MongoId('4e63611cbc347053a2000001')]); – sk8terboi87 ツ Jul 09 '13 at 10:56
This answer is the best one I found so far and it is the key to solve the problem – tom10271 Aug 04 '16 at 07:29

score 2 · Answer 2 · answered Mar 11 '14 at 21:15

you can try this:

$user = ... The User Document with _id 4e63611cbc347053a2000001 ...
$dm = $this->getDocumentManager();
$queryBuilder = $dm->createQueryBuilder('\Event')->field('privileges.user')->references($user)

strangely it's not documented here http://doctrine-mongodb-odm.readthedocs.org/en/latest/reference/query-builder-api.html, but worked for me.

score 0 · Answer 3 · answered Jul 12 '13 at 16:25

quick note while the method works you won't be using an index when using the .$id reference.

It is best to use the db reference.

$privilege_repository = $dm->getRepository('\Event');
$qb2 = $privilege_repository->createQueryBuilder('\Event') ->field('privileges.user')->equals( \MongoDBRef::create("user", new \MongoId('4e63611cbc347053a2000001'),'database_name') );
$query2 = $qb2->getQuery();
$result2 = $query2->execute();
 $result2->count() :3

I assumed user is the name of the collection above and database_name as database name. You can also create the index by :

db.collection.ensureIndex({'privileges.user':1};

When you have very large collections with multiple shards and replica sets the index will come in handy.

score 0 · Answer 4 · answered Sep 04 '11 at 16:26

0

What the result of user.id?

$qb2 = $privilege_repository->createQueryBuilder('\Event\Privilege') ->field('user.id')->equals(new \MongoId('4e63611cbc347053a2000001'));

answered Sep 04 '11 at 16:26

atma

875
7
10

Same result : `$privilege_repository = $dm->getRepository('\Event\Privilege'); $qb2 = $privilege_repository->createQueryBuilder('\Event\Privilege') ->field('user.id')->equals(new \MongoId('4e63611cbc347053a2000001')); $query2 = $qb2->getQuery(); $result2 = $query2->execute(); $result2->count() :0` – remi Sep 04 '11 at 16:50
Ah, sure. You trying to get all the **events** but using \Event\Privilege which does not have reference and id in events table. So you need something like $dm->createQueryBuilder('Event')->field('privileges.user.id ')->equals(new \MongoId('4e63611cbc347053a2000001')); – atma Sep 04 '11 at 17:52
And since you do not have an id of Privilege in the Event table, you need to add `@EmbedMany(targetDocument="\Event\Privilege", cascade={"persist"})` for complete reference of Privieges, right? – atma Sep 04 '11 at 18:02
It seems the cascade isn't active for the embed tags : `Message: Unknown property 'cascade' on annotation 'Doctrine\ODM\MongoDB\Mapping\Annotations\EmbedMany'` – remi Sep 04 '11 at 18:32
I tried your suggestion but it seems to try to reference to privileges in the privilege class : Message: No mapping found for field 'privileges' in class '\Event\Privilege'. - Even though the line is : `$qb2 = $privilege_repository->createQueryBuilder(\'\Event\')->field(\'privileges.user.id\')->equals(new \MongoId(\'4e63611cbc347053a2000001\'));` – remi Sep 04 '11 at 18:33
Sorry, for my bad explain, I don't speak English. The main idea is: add an id field to class Privilege, store new Event in db, check if Privilege has unique id. If true, you can `@EmbedMany(targetDocument="\Event\Privilege")` in the right way. Without unique identificator you can not reference to Privilege, which stores the User. – atma Sep 04 '11 at 19:12
I've tried playing around with @Id with no success, furthermore I noticed that Doctrine creates a new User each time I create a new event due to the persist on the User field. If I take the cascade off I don't have any $id inserted in Mongo... – remi Sep 05 '11 at 06:22

Doctrine ODM / PHP / MongoDB - Can't query Referenced Elements

4 Answers4