Mongoose: Virtual populate with `localField` and `foreignField` as arrays

[shruti8597]

Feature Request

I have two schemas

1. Link
2. Cheque

Link schema has two fields: entity1 and entity2
Cheque schema has two fields: from and to

I want to populate a virtual detail with local fields from and to and foreign fields entity1 and entity2.

Entity1 and Entity2 are not unique in the link table, but the combination of them is unique.

A same question on Stack Overflow

https://stackoverflow.com/questions/43551953/mongoose-populate-virtuals-with-multiple-local-foreign-key-pairs

@vkarpov15

[vkarpov15]

Re-posting the stack overflow example here for more detail:

var workerSchema = new Schema({
    name: String,
    locationCode: String,
    departmentCode: String
});

var deparmentSchema = new Schema({
    locationCode: String,    //Note: neither location nor code
    code: String,            //are unique, but the combination of them are
    manager: String,
    otherInfoAboutDepartment: String
});

workerSchema.virtual('department', {
    ref: "Department",
    localField: ["locationCode", "departmentCode"],
    foreignField: ["locationCode", "code"]
});

I'm not sure I understand how you imagine this virtual working. Would you imagine department would consist of all docs where locationCode = code or locationCode = locationCode or departmentCode = locationCode or departmentCode = code?

[shruti8597]

I want department to contain all docs where locationCode=locationCode and also department=code.

[vkarpov15]

Interesting, that makes sense. Will consider this for a future release, thanks for the suggestion.

[callumgare]

Hi just want to check up on this and also submit my use case for it (which is similar to @shruti8597 use case). I'm making an online forum thing and my schema is like this:

Board
which contains multiple:
Thread
which contains multiple:
Post

I would like to populate a virtual array in Thread with all the posts in that thread. But to do so I need to match not just one field but two (ie board id and thread id) since there could be multiple threads with the same id (but in different boards) so if I want all posts for a particular board then filtering posts by board id is not enough. I also need to filter by board id.

[thenengah]

I need to populate based on two references.

[veracl]

I am also interested in this. I would like to have an array virtual that includes all related documents from another collection, but to find the related documents I sadly need to match on two IDs (similar to what has been described above).

I want to suggest a different syntax than the array syntax from above. Why not use a match option as described here https://mongoosejs.com/docs/api.html#model_Model.populate and allow arbitrary join conditions on another collection (like $lookup in MongoDB using $let and $pipelne instead of $localField and $foreignField)?

[vkarpov15]

@veracl can you provide an example of what that syntax might look like?

[jloveridge]

@vkarpov15 I was just posting on your blog something that seems related so I can give an example of what might work.

const mongoose = require('mongoose');
mongoose.Promise = global.Promise;
mongoose.connect('mongodb://localhost:27017/test', { useMongoClient: true });

const CommentSchema = new mongoose.Schema({
  text: String,
  refId: mongoose.Schema.Types.ObjectId,
  refModel: String,
  authorId: mongoose.Schema.Types.ObjectId,
  createdAt: Date;
});

CommentSchema.virtual('author', {
  ref: 'User',
  localField: 'authorId',
  foreignField: '_id',
  justOne: true,
});

CommentSchema.virtual('refObj', {
  ref: (doc) => doc.refModel,
  localField: 'refId',
  foreignField: '_id',
  justOne: true,
});

const ProductSchema = new mongoose.Schema({
  name: String;
});

ProductSchema.virtual('comments', {
  ref: 'Comment',
  match: (doc) => {
    const sinceDate = new Date(Date.now() - 1000 * 60 * 60 * 24 * 30);
    return {refId: doc._id, refModel: 'Product', createdAt: {$gt: sinceDate}};
  },
});

const UserSchema = new mongoose.Schema({
  name: String,
});

UserSchema.virtual('comments', {
  ref: 'Comment',
  match: (doc) => {
    const sinceDate = new Date(Date.now() - 1000 * 60 * 60 * 24 * 30);
    return {refId: doc._id, refModel: 'User', createdAt: {$gt: sinceDate}};
  },
});

const Comment = mongoose.model('Comment', CommentSchema);
const Product = mongoose.model('Product', ProductSchema);
const User = mongoose.model('User', UserSchema);

This is admittedly a relatively simplistic version of what you might do. Limiting comments retrieved to those matching the _id of the referenced document, the referenced models name, and limiting to comments created in the past 30 days. More exotic things could be done with a match function such as using multiple fields as desired by some of the prior commenters. In that case the match function might look something like the following:

// User model is now augmented to contain references to their friends.
const UserSchema = new mongoose.Schema({
  name: String,
  friendIds: mongoose.Schema.Types.ObjectId[],
});

UserSchema.virtual('friendsComments', {
  ref: 'Comment',
  match: (doc) => {
    const sinceDate = new Date(Date.now() - 1000 * 60 * 60 * 24 * 30);
    return {refId: doc._id, refModel: 'User', authorId: {$in: doc.friendIds}, createdAt: {$gt: sinceDate}};
  },
});

Additionally it would be nice if it were possible to specify which properties we want selected from the populated documents by default. This could be accomplished by adding a select property when defining a virtual population field. In my previous examples I would have added the following if it were possible:

  select: 'text authorId createdAt'

[jloveridge]

Using $lookup syntax would result in something very different. Instead of doing the match as I detailed a virtual populate using aggregation syntax might look more like:

UserSchema.virtual('comments', {
  lookup: (doc) => {
    return {
        from: 'comments',
        let: {userId: '$_id', friends: '$friendIds'},
        pipeline: [
          {$match: {$expr: {$and: [
            {$eq: ['$refId', '$$userId']}, {$in: ['$authorId', '$$friendIds']},
          ]}}},
          {$project: {text: 1, authorId: 1, createdAt: 1}}
        ],
        as: 'comments',
    };
  },
});

While this is obviously very flexible it is also a royal pain to write so I would want to avoid any such syntax whenever possible.

[jloveridge]

For the very simple case that this ticket seems to have originated with I would prefer a syntax more like the following:

var workerSchema = new Schema({
    name: String,
    locationCode: String,
    departmentCode: String
});

var deparmentSchema = new Schema({
    locationCode: String,    //Note: neither location nor code
    code: String,            //are unique, but the combination of them are
    manager: String,
    otherInfoAboutDepartment: String
});

workerSchema.virtual('department', {
    ref: "Department",
    fieldMap: {
        locationCode: 'locationCode',
        departmentCode: 'code',
    },
});

Using this syntax the field map designates each localField as a key and each foreignField as the value. If fieldMap was allowed to be either an object or a function then if you need more dynamic mappings you could do:

workerSchema.virtual('department', {
    ref: "Department",
    fieldMap: (doc) => {
      const map = {};
      // inspect doc and set key value pairs as appropriate.
      return map;
    },
});

[vkarpov15]

@jloveridge if you're looking for using match as a function, we introduced support for match functions in Mongoose 5.5. Does that look like what you're looking for?

[jloveridge]

@vkarpov15 I knew we could use match as a function when calling Model.find().populate({path: 'somePath', match: (doc) => ...).exec(). What I wasn't aware of is that you could set options, including a match as part of the definition of a virtual populate, that is indeed pretty handy. It looks like when using match it will still end up retrieving more documents from the server than may be strictly necessary though since it looks like sift is used to process the match. What I am suggesting might be extremely useful is the ability to specify how the request to the server is actually made. Basically a way to add additional criteria to the find that will be executed to retrieve the documents that will be populated.

Another thing that I might have overlooked is it appears that it is possible to return an Array of strings for ref and mongoose will issue a find against multiple collections/models to retrieve the documents. At least if I understood a different post/comment correctly this appears possible. If that is the case it may make some of what I am suggesting here more difficult. For instance instead of a simple field map you might need a field mapping per referenced model.

[vkarpov15]

That's what match does: it adds additional criteria to the underlying find(). If you're populating multiple docs with a match function, we put all the return values from the match function into a $or. The reason why we use sift is to figure out what document to assign the returned values to.

[andreialecu]

A bunch of other ORMs have this, here's another use case for it:

category collection

cat_id     locale_id      title
A123       en             Category in English
A123       fr             Category in French

post collection

cat_id   locale_id   title
A123     en          This is a post just for the English category

category.posts should have posts just for its specific language.

TypeORM allows defining this relation like this:

[
    { name: "cat_id", referencedColumnName: "cat_id" },
    { name: "locale_id", referencedColumnName: "locale_id" }
]

There are other use cases where this sort of relation is necessary. Still on the roadmap?

[vkarpov15]

@andreialecu I believe this issue is unrelated. You can already use functions for localField and foreignField, like:

categorySchema.virtual('posts', {
  ref: 'OtherModel',
  localField: '_id',
  foreignField: doc => doc.referencedColumnName
});

[Ethan-G]

I also went looking for this functionality today, so consider this another vote for this feature.

[TadeoArmenta]

Well, I was looking for the same. another vote for this.

[vkarpov15]

@TadeoArmenta @Ethan-G current workaround is to use match functions.

For example, if you want to do this:

employeeSchema.virtual('department', {
  ref: 'Test',
  localField: ['locationId', 'departmentId'],
  foreignField: ['locationId', 'name'],
  justOne: true
});

You can instead do this in Mongoose >= 5.5.0:

employeeSchema.virtual('department', {
  ref: 'Test',
  localField: 'locationId',
  foreignField: 'locationId',
  match: doc => ({ name: doc.departmentId }),
  justOne: true
});

Slightly less readable, but still does the same thing.