Mongoose - Sort a find depends on boolean property

Question

I have a mongoose find request, and I have a first mongoose schema who contains this (the client) :

client: { type: ObjectId, ref: 'Client' },

And I have the client schema :

new mongoose.Schema({
  firstName: String,
  lastName: String,
  company: String,
  companyAsPrimaryName: Boolean,
})

I want to do a find request in the first schema depends on the client property companyAsPrimaryName boolean. If it's false : I want to sort on firstName + lastName, and if it's true, I want to sort on company.

I have no idea how to do this, I already checked an answer and I found nothing.

Thanks for your help

Do these answers help you?: [_Mongo sort on a calculated condition_](https://stackoverflow.com/q/32958402/996081) and [_Mongodb conditional sort_](https://stackoverflow.com/questions/55159417/mongodb-conditional-sort) — cbr, Feb 27 '20 at 00:10

score 2 · Accepted Answer · answered Feb 27 '20 at 01:01

What you could do is use aggregate with $addFields and $cond to create a field with the name you want to sort by, then sort by that field. The following snippet will get every Foo and sort them according to either the company name or first name + last name if companyAsPrimaryName is false.

  const data = await Foo.aggregate([
  {
    // basically .populate('client') in our case
    $lookup: {
      from: 'clients',
      localField: 'client',
      foreignField: '_id',
      as: 'client'
    }
  },
  {
    // deconstruct the client array into one object
    $unwind: '$client'
  },
  {
    // add a computed field for sorting
    $addFields: {
      sortName: {
        $cond: {
          if: {
            $eq: ['$client.companyAsPrimaryName', true]
          },
          then: '$client.company',
          else: {
            $concat: ['$client.firstName', '$client.lastName']
          }
        }
      }
    }
  },
  {
    $sort: {
      sortName: 1
    }
  }
])
console.log(data)

Here Foo is the model containing the client as a field. Note that this will however include that field in the result. If you want to omit some fields, you can add a $project stage to pick the fields.

Here's a snippet I used to test this out locally:

const mongoose = require('mongoose')

const Client = mongoose.model('Client', {
  firstName: String,
  lastName: String,
  company: String,
  companyAsPrimaryName: Boolean
})

const Foo = mongoose.model('Foo', {
  name: String,
  client: { type: mongoose.Types.ObjectId, ref: 'Client' }
})

// Call this in `start()` to populate some sample data
const create = async () => {
  const client1 = await new Client({
    firstName: 'Alpha',
    lastName: 'Alphaname',
    company: 'Alphabet',
    companyAsPrimaryName: true
  }).save()
  const client2 = await new Client({
    firstName: 'John',
    lastName: 'Wayne',
    company: 'Google',
    companyAsPrimaryName: true
  }).save()
  const client3 = await new Client({
    firstName: 'Bravo',
    lastName: 'Bretty',
    company: 'Zulu Solutions',
    companyAsPrimaryName: false
  }).save()

  await new Foo({
    name: 'Toaster',
    client: client1
  }).save()
  await new Foo({
    name: 'Lunchbox',
    client: client1
  }).save()
  await new Foo({
    name: 'Treadmill',
    client: client1
  }).save()

  await new Foo({
    name: 'Tapas',
    client: client2
  }).save()
  await new Foo({
    name: 'Ananas',
    client: client2
  }).save()
  await new Foo({
    name: 'Zapiers',
    client: client2
  }).save()

  await new Foo({
    name: 'Brets',
    client: client3
  }).save()
  await new Foo({
    name: 'Xrats',
    client: client3
  }).save()
  await new Foo({
    name: 'Abins',
    client: client3
  }).save()
}

const start = async () => {
  await mongoose.connect('mongodb+srv://CONNECTION STRING HERE', {
    useNewUrlParser: true,
    useUnifiedTopology: true
  })

  const data = await Foo.aggregate([
    {
      // basically .populate('client') in our case
      $lookup: {
        from: 'clients',
        localField: 'client',
        foreignField: '_id',
        as: 'client'
      }
    },
    {
      // deconstruct the client array into one object
      $unwind: '$client'
    },
    {
      // add a computed field for sorting
      $addFields: {
        sortName: {
          $cond: {
            if: {
              $eq: ['$client.companyAsPrimaryName', true]
            },
            then: '$client.company',
            else: {
              $concat: ['$client.firstName', '$client.lastName']
            }
          }
        }
      }
    },
    {
      $sort: {
        sortName: 1
      }
    }
  ])
  console.log(data)
}

start().then(() => {
  console.log('ready')
  mongoose.disconnect()
})

Mongoose - Sort a find depends on boolean property

1 Answers1