Mongoose/Mongodb basic trello like scheme problem with rendering in vue

Question

I'm creating a very basic functionality kanban board.

My board has 4 models so far:

User model

var userSchema = new Schema({
  name: {
    type: String,
    required: true
  }
})

module.exports = mongoose.model('User', userSchema)

Board model

var boardSchema = new Schema({
  title: {
    type: String,
    required: true
  },
  lists: [ listSchema ]
  members: [
    {
      type: Schema.Types.ObjectId,
      ref: 'user'
    }
  ]
});

module.exports = mongoose.model('Board', boardSchema)

List schema

let listSchema = new Schema({
  title: {
    type: String,
    required: true
  },
  userCreated: {
    type: Schema.Types.ObjectId,
    required: true,
    ref: 'user'
  },
  boardId: {
    type: Schema.Types.ObjectId,
    required: true,
    ref: 'board'
  },
  sort: {
    type: Number,
    decimal: true,
    required: true
  }
})

module.exports = mongoose.model('List', listSchema)

Card schema

var cardSchema = new Schema({
  title: {
    type: String,
    required: true
  },
  description: {
    type: String
  },
  boardId: {
    type: Schema.Types.ObjectId,
    required: true,
    ref: 'board'
  },
  listId: {
    type: Schema.Types.ObjectId,
    required: true,
    ref: 'list'
  },
  members: [
    {
      type: Schema.Types.ObjectId,
      ref: 'user'
    }
  ],
  sort: {
    type: Number,
    decimal: true,
    required: true
  }
})

module.exports = mongoose.model('Card', cardSchema)

What am I looking for?

My front-end is made with Vue.js and sortable.js drag and drop lib.

I want to find the best way to render board with lists (columns) and cards in them.

From what I understand, I should get my board first, by the users id in members array.

Then I have my board which has lists embedded already.

On second api request, I get all the cards by boardId.

My question is - how do I correctly put/render all the cards into each owns lists?

So in the end I want to have something like:

{
  title: 'My board',
  lists: [
    {
      title: 'My list',
      _id: '35jj3j532jj'
      cards: [
       {
        title: 'my card',
        listId: '35jj3j532jj'
       }
      ]
    },
    {
      title: 'My list 2',
      _id: '4gfdg5454dfg'
      cards: [
       {
        title: 'my card 22',
        listId: '4gfdg5454dfg'
       },
       {
        title: 'my card 22',
        listId: '4gfdg5454dfg'
       }
      ]
    }
  ]
  members: [
    'df76g7gh7gf86889gf989fdg'
  ]
}

What I've tried?

I've came up with only one thing so far, that is:

1. Two api calls in mounted hook - one to get the board with lists, second to get all cards.
2. Then I loop trough lists and loop trough cards and push each card into the list by id?

But this way it seems that my lists would need to have an empty array called cards: [] just for the front-end card-to-list sorting by id, seems somehow wrong.

Is this a good way? Or should I redesign my models and schemas and go with some other way? Any help would be appreciated!

Answer 1

The schema you've defined is pretty good, just one modifications though.

No need to have 'lists' in Board model, since it's already available in lists and also if you keep it in boards, then everytime a new list is added, you'll need to edit the board as well.

Here's how the flow would be.

Initially, when a user signs in, you'll need to show them the list of boards. This should be easy since you'll just do a find query with the user_id on the board collection.

Board.find({members: user_id}) // where user_id is the ID of the user

Now when a user clicks on a particular board, you can get the lists with the board_id, similar to the above query.

List.find({boardId: board_id}) // where board_id is the ID of the board

Similarly, you can get cards with the help of list_id and board_id.

Card.find({boardId: board_id, listId: list_id}) // where board_id is the ID of the board and listId is the Id of the list

Now, let's look at cases wherein you might need data from 2 or more collection at the same time. For example, when a user clicks on board, you not only need the lists in the board but also the cards in that board. In that case, you'll need to write an aggregation as such,

        Board.aggregate([
        // get boards which match a particular user_id
        {
            $match: {members: user_id}
        },
        // get lists that match the board_id
        {
            $lookup:
            {
                from: 'list',
                localField: '_id',
                foreignField: 'boardId',
                as: 'lists'
            }
        }
    ])

This will return the boards, and in each board, there'll be an array of lists associated with that board. If a particular board doesn't have a list, then it'll have an empty array.

Similarly, if you want to add cards to the list and board, the aggregation query will be a bot more complex, as such,

        Board.aggregate([
        // get boards which match a particular user_id
        {
            $match: {members: user_id}
        },
        // get lists that match the board_id
        {
            $lookup:
            {
                from: 'list',
                localField: '_id',
                foreignField: 'boardId',
                as: 'lists'
            }
        },
        // get cards that match the board_id
        {
            $lookup:
            {
                from: 'card',
                localField: '_id',
                foreignField: 'boardId',
                as: 'cards'
            }
        }
    ])

This will add an array of cards as well to the mix. Similarly, you can get cards of the lists as well.

Answer 2

A bit late to the answer but I think it'll help someone nevertheless. The problem you have could be solved using aggregation framework. While the other answer mentions a pretty good way, it still doesn't have the cards data embedded into it.

MongoDB docs show a way for nested aggregation queries. Nested Lookup

A similar approach could be used for your question.

Board.aggregate([
  {
    $match: { _id: mongoose.Types.ObjectId(boardId) },
  },
  {
    $lookup: {
      from: 'lists',
      let: { boardId: '$_id' },
      pipeline: [
        { $match: { $expr: { $eq: ['$boardId', '$$boardId'] } } },
        {
          $lookup: {
            from: 'cards',
            let: { listId: '$_id' },
            pipeline: [{ $match: { $expr: { $eq: ['$listId', '$$listId'] } } }],
            as: 'cards',
          },
        },
      ],
      as: 'lists',
    },
  },
]);

This will include the cards as an array inside of every list.