Docs > Aggregate lookup

[michaelpeterlee]

Issue Description

Can we please have some docs to demonstrate aggregate(pipeline) in context of joining _User table.

No matter what I do, I always get an empty resultset.

Steps to reproduce

No results exist for aggregate with pipeline lookup stage, below.

I have tried every combination of values for the lookup, including 'objectId','User', referencing pointers and strings, to no-avail.

Schema:

_User
UserActivity [objectId,userObjectId,user]

I successfully apply group and project stages, just not lookup!

$query = new ParseQuery("UserActivity");

$pipeline =
[
'lookup' =>
[
'from' =>"_User",
'localField'=> "user",
'foreignField' => "_id",
'as' => "user"
],
];

$aArticle = $query->aggregate($pipeline);

print_r($aArticle);

Environment Details

• Your PHP Version: [ 7.2.24]
• Your Parse PHP SDK Version: [1.6]
• ParseServer: V3.2.3, NodeJS 10.15.1 @nodechef

[davimacedo]

It is tricky. If you look at your MongoDB database, you will see that your local field is actually _p_user and it does not have the plain id, but something like _User$objectId. So you will need something like this:

$query = new ParseQuery("UserActivity");

$pipeline =
[
  'project' =>
  [
    'user' =>
    [
      '$substr' =>
      [
        '$_p_user', 6, -1
      ]
    ]
  ],
  'lookup' =>
  [
    'from' =>"_User",
    'localField'=> "user",
    'foreignField' => "_id",
    'as' => "user"
  ],
];

$aArticle = $query->aggregate($pipeline);

print_r($aArticle);

It is for sure something that could be better documented.

[michaelpeterlee]

Thank you and I hope it helps someone else.

I tried that config to no-avail, however have now referred this for NodeChef to advise, as I can't peer into MongoDB easily.

[davimacedo]

Maybe I translated something wrong from "mongodb" to PHP, but I've just successfully run this query directly in MongoDB in the case it helps you:

db.AnotherClass.aggregate([{$project:{user:{$substr: [ "$_p_user", 6, -1 ]}}},{$lookup:{from:'_User',localField:'user',foreignField:'_id',as:'user'}}])

[michaelpeterlee]

MongoDB >= 3.2 is required. Without that I receive a blank response.

With that upgraded, 'lookup' stage is now working.

I had to engage a Parse string column for localField, rather than a Parse pointer for it to work.
The 'project' stage your supplied did not work for me, I just omitted for now.

$pipeline = [ 'lookup' => [ 'from' =>"_User", 'localField'=> "userObjectId", 'foreignField' => "_id", 'as' => "user" ], ];

Thanks very much for your help; This is a wonderful project suite to enable a turnkey, scalable API!