Populating all items from ref model

Question

I have a database structured like this:

I want to get all listItems, of all lists from a user with a x ID. What's the correct way of doing that?

I'm using node with mongoose, and I tried the following:

await User.findById(user._id).populate('list');

But realized that I can't populate all ListItem's from that. Meaning, I can't do this:

await User.findById(user._id).populate('list').populate('listItem');

How can I get all listItems, of all lists from a user with a x ID?

@NeilLunn except none of the links you provided have anything to do with this.... — Matt Oestreich, Sep 28 '19 at 15:41
@MattOestreich The duplicates are an exact match for "joining though a join" which is what the question actually asks. Most questions on the joining topic have naturally been asked already, and correctly answered. — Neil Lunn, Sep 28 '19 at 19:53

score 1 · Accepted Answer · answered Sep 28 '19 at 00:38

Assuming User, List, and ListItem are Collections, you should be able to accomplish this using $lookup..

Here is a live demo of the following query..

Query:

db.users.aggregate([
  {
    $match: {
      uniqueId: 1
    }
  },
  {
    $lookup: {
      from: "lists",
      localField: "uniqueId",
      foreignField: "userId",
      as: "lists"
    }
  },
  {
    $lookup: {
      from: "listItems",
      localField: "uniqueId",
      foreignField: "userId",
      as: "listItems"
    }
  }
])

Dataset:

db={ // Simulates a DB  ********
  "users": [ // Simulates a Collection ********
    {
      "firstname": "John",
      "lastname": "Smith",
      "email": "jsmith@gmail.com",
      "password": "password123",
      "uniqueId": 1
    },
    {
      "firstname": "Jane",
      "lastname": "Doe",
      "email": "doe@yahoo.com",
      "password": "123password",
      "uniqueId": 2
    }
  ],
  "lists": [ // Simulates a Collection ********
    {
      "userId": 1,
      "name": "Johns List 1",
      "items": [
        11,
        12,
        13
      ]
    },
    {
      "userId": 2,
      "name": "Groceries",
      "items": [
        21,
        22,
        23
      ]
    }
  ],
  "listItems": [ // Simulates a Collection ********
    {
      "userId": 2,
      "itemId": 21,
      "title": "Apple",
      "notes": []
    },
    {
      "userId": 2,
      "itemId": 22,
      "title": "Banana",
      "notes": []
    },
    {
      "userId": 2,
      "itemId": 23,
      "title": "Strawberry",
      "notes": []
    },
    {
      "userId": 1,
      "itemId": 11,
      "title": "Oil Change",
      "notes": []
    },
    {
      "userId": 1,
      "itemId": 12,
      "title": "Pick Up Grandma",
      "notes": []
    },
    {
      "userId": 1,
      "itemId": 13,
      "title": "Go For Run",
      "notes": []
    }
  ]
}

Result:

[
  {
    "_id": ObjectId("5a934e000102030405000008"),
    "email": "jsmith@gmail.com",
    "firstname": "John",
    "lastname": "Smith",
    "listItems": [
      {
        "_id": ObjectId("5a934e000102030405000003"),
        "itemId": 11,
        "notes": [],
        "title": "Oil Change",
        "userId": 1
      },
      {
        "_id": ObjectId("5a934e000102030405000004"),
        "itemId": 12,
        "notes": [],
        "title": "Pick Up Grandma",
        "userId": 1
      },
      {
        "_id": ObjectId("5a934e000102030405000005"),
        "itemId": 13,
        "notes": [],
        "title": "Go For Run",
        "userId": 1
      }
    ],
    "lists": [
      {
        "_id": ObjectId("5a934e000102030405000006"),
        "items": [
          11,
          12,
          13
        ],
        "name": "Johns List 1",
        "userId": 1
      }
    ],
    "password": "password123",
    "uniqueId": 1
  }
]

Thanks! I will try that shortly. Side question, is this a good way to design/create a database structure? — Jessica, Sep 28 '19 at 00:54
I think it depends what your definition of 'good' is.. If we are talking about speed, I am unsure the performance hit when structuring documents in separate collections versus nesting documents.. That is, if you define 'good' in terms of performance/speed. To me, your "schema" makes sense and seems scalable, so I would say it is good. You could use the built in ObjectID to reference documents in separate collections, but as long as you keep a unique reference you should be fine.. [See this for more info](https://www.mongodb.com/blog/post/6-rules-of-thumb-for-mongodb-schema-design-part-1) — Matt Oestreich, Sep 28 '19 at 01:09
Using the `populate` method in Mongoose relies heavily on your defined Mongoose schema. It is possible to accomplish using the code you have provided in your question, if the schema is properly defined. [You can read more here](https://mongoosejs.com/docs/populate.html) on Mongoose schema/populate. I can test this out tomorrow and get back to you. — Matt Oestreich, Sep 29 '19 at 05:22

Populating all items from ref model

1 Answers1