CODEWITHSUNDEEP
phpmongodb
Petr Dvořák
Petr Dvořák

Reputation: 760

MongoDB $lookup with _id as a foreignField in PHP

I am beating my head on the table for too long with this one...

I have two MongoDB collections: "chatroom" and "users". The "chatroom" collection has "user_id" key pointing to a specific single user in "users" collection.

I am trying to fetch the chatroom with a user using the $lookup aggregate query, what I currently have is this one:

$this->mongo->chatroom->aggregate(
    array('$lookup' => array(
        'from' => 'users',
        'localField' => 'user_id',
        'foreignField' => '_id',
        'as' => 'user'
    ))
);

However, this returns an empty "user" field in the collection. The weird thing is that if I try to replace the "_id" with custom "uid" set to the value of _id.$id, it works as expected:

$this->mongo->chatroom->aggregate(
    array('$lookup' => array(
        'from' => 'users',
        'localField' => 'user_id',
        'foreignField' => 'uid', // uid = _id.$id
        'as' => 'user'
    ))
);

I figured out the problem is that "_id" is ObjectId while "user_id" is a String. But I don't know how to deal with the problem nicely...

Upvotes: 3

Views: 3462

Answers (1)

Petr Dvořák
Petr Dvořák

Reputation: 760

To answer my own question, I went around the problem by making "user_id" an instance of a "MongoId" class instead of a plain string. Basically, I store "user_id" as:

$mongoObject["user_id"] = new MongoId($this->user_id);

Another solution would probably be decorating the objects with "uid" field with a value equal to "_id.$id".

Upvotes: 3

Related Questions