Wrong casting in `$lookup` relations

[Siebov]

• Laravel-mongodb Version: 3.8.5
• PHP Version: 8.0
• Database Driver & Version: 4.2.3

Description:

I'm using raw query to get data from DB. There is a few $lookup I use. One for rounds collection another one for the users.

'$lookup' => [
                'from' => 'users',
                'localField' => 'user_mongo_id',
                'foreignField' => '_id',
                'as' => 'user'            
]

...

'$lookup' => [
                'from' => 'rounds',
                'localField' => 'round_mongo_id',
                'foreignField' => '_id',
                'as' => 'proposal_round'            
]

Parent model has 1 round and 1 user. (1 to 1 relation) For some reason user casts differently from round:

• Round relation is an object with string _id and stringified dates.
• User is an array of 1 single object, with { _id: $oid: '.....'} and dates like

"created_at": { "$date": { "$numberLong": "1659863891000" } }

User:

Round:

Expected behaviour

I expect user to be an object (not array of objects) with stringified properties _id and dates (not displayed as an objects).

[alcaeus]

$lookup always returns an array of documents that matched; if you are expecting a single document, you need to use $first in an $addFields stage to unwrap the array:

[
  '$lookup' => [
                  'from' => 'users',
                  'localField' => 'user_mongo_id',
                  'foreignField' => '_id',
                  'as' => 'user'            
  ],
  '$addFields' => [
    'user' => ['$first' => '$user'],
  ],
  // ...
]

[Siebov]

@alcaeus okay, that's not a big issue, I'm fixing it with $unwind anyway. The biggest problem is with this _ids and dates shown as objects.

[alcaeus]

@Siebov do you call the aggregation pipeline from a model? If so, what does that model have defined as relations for the user and proposal_round fields?

[Siebov]

@alcaeus this is how I build my query

$aggregateQuery = [];

// Add user relation. **applyRelation is a helper for $lookup
$aggregateQuery[] = $this->applyRelation(
    'users',
    'user_mongo_id',
    '_id',
    'user'
);
  
// Add round relation
$aggregateQuery[] = $this->applyRelation(
     'proposal_rounds',
     'proposal_round_mongo_id',
      '_id',
      'proposal_round'
);


// add some filtering
// add some order by
// add projection

$proposals = Proposal::query()->raw(function ($collection) use ($aggregateQuery) {
    return $collection->aggregate($aggregateQuery);
});

return response()->json($proposals->toArray());

[alcaeus]

@Siebov does the Proposal model define a relationship for user? Looking at the result you posted above, it would look like the model defines a relationship for proposal_round and is able to convert MongoDB types appropriately, but doesn't do so for the user relationship. For aggregation pipeline results, both should be added as am embedsOne relationship.

[Siebov]

@alcaeus this is Proposal model

    public function user()
    {
        return $this->belongsTo(User::class);
    }

    public function proposalRound()
    {
        return $this->belongsTo(ProposalRound::class);
    }

[alcaeus]

I'm not familiar enough with the Laravel relationship types to figure out how belongsTo behaves when it's already getting data. Strictly speaking, the result of your pipeline would suggest the properties be mapped as embedsOne. However, considering that one of these relationship works as expected and the other doesn't, I'm at a bit of a loss what's happening here. I'll have to investigate some more and familiarise myself with the relationships before I can point to a concrete source of the problem.

If both your $lookup stages are followed by appropriate $unwind stages, I'd expect the model to unserialise as expected. Your first example seemed to be missing the $unwind after looking up users, can you confirm that the result of the pipeline is now a single document for each user and proposal_round property, but the results still differ?

[Siebov]

@alcaeus exactly. I have two exact same relations on proposal model: user and round. Both relates to proposal as 1-to-1. Round is retrieved as single doc with correct _id and dates casting, but user is an embedded single object in object (fixing that with $unwind) but casted wrong (with _id and dates as objects not strings).

[alcaeus]

@Siebov can you show the inverse side of the relationships, i.e. the mapping for Proposal in the User and ProposalRound classes? I'll test this to figure out whether it's supposed to work and what needs to be done to make it work.

[TranLong183]

applyRelation

@alcaeus okay, that's not a big issue, I'm fixing it with $unwind anyway. The biggest problem is with this _ids and dates shown as objects.

does you fixing _ids and dates shown as object problem yet