date query help~

[Here21]

// tsconfig
{
  "compilerOptions": {
    "module": "commonjs",
    "moduleResolution": "node",
    "declaration": true,
    "removeComments": true,
    "emitDecoratorMetadata": true,
    "experimentalDecorators": true,
    "allowSyntheticDefaultImports": true,
    "target": "es2017",
    "sourceMap": true,
    "outDir": "./dist",
    "baseUrl": "./",
    "paths": {
      "@src/*": ["./src/*"],
      "@models/*": ["./src/models/*"],
      "@modules/*": ["./src/modules/*"],
      "@utils/*": ["./src/utils/*"]
    },
    "incremental": true,
    "skipLibCheck": true,
    "strictNullChecks": false,
    "noImplicitAny": false,
    "strictBindCallApply": false,
    "forceConsistentCasingInFileNames": false,
    "noFallthroughCasesInSwitch": false,
    "esModuleInterop": true
  },
  "include": ["src/**/*"],
  "exclude": ["node_modules", "dist"]
}

---

problem

this is Schema:

  const schema = new Schema({...},
  {
    timestamps: { createdAt: 'created_at', updatedAt: 'updated_at' },
    collection: 'training_log',
  })

now I would like to query date by created_ad, like below:

const filter = { user_id };
// not working
filter.created_at = {
  $gte: dayjs(props.date).startOf('date').toISOString(),
  $lte: dayjs(props.date).endOf('date').toISOString(),
};
// not working
filter.created_at = {
  $gte: new Date(2022, 0, 21),
  $lte: new Date(2022, 0, 24),
};

this.schema.find(filter, {user_id: 0 })
// mongoose debug: training_log.find  {"user_id":"61e403e4d3ec913651fcc51b","created_at":{"$gte":"2022-01-20T16:00:00.000Z","$lte":"2022-01-23T16:00:00.000Z"}}

but result is [];

so I copy the query to MongoDB Campass & use ISODate(): It works!

but why? I follow the guide of mongoose doc: https://mongoosejs.com/docs/guide.html#timestamps

Versions

"@nestjs/mongoose": "^9.0.2" "mongoose": "^6.1.6" "nodejs": "14.17"

[Uzlopak]

is user_id in your schema of Schema.Types.ObjectId?

[Here21]

@Uzlopak yep~it seems to be mongoose doesn't trans Date to ISODate...

[Here21]

@Uzlopak full filter like is:

const filter = {
      user_id: new Types.ObjectId(props.user_id),
      status: props.status,
      created_at: undefined,
    };
    if (props.date) {
      // filter.created_at = {
      //   $gte: dayjs(props.date).startOf('date').toISOString(),
      //   $lte: dayjs(props.date).endOf('date').toISOString(),
      // };
      filter.created_at = {
        $gte: new Date(2022, 0, 21),
        $lte: new Date(2022, 0, 24),
      };
    }

[Uzlopak]

What is you Schema?

[Here21]

@Uzlopak full schema here：

import { Schema, Types, HydratedDocument } from 'mongoose';
import { TrainingExerciseConfigSchema } from '../common/common.schema';
import { ExecuteStatus } from '../common/common.interface';
import { TrainingLog } from './tl.interface';
import { TrackDataSchema } from '@src/models/training/training.schema';

export const TrainingLogExerciseSchema = new Schema({
  exercise_id: {
    type: Types.ObjectId,
    ref: 'Exercise',
    required: false,
  },
  name: String,
  thumb: String,
  track_type: String,
  default_track_data: [TrackDataSchema],
  track_data: [TrackDataSchema],
  config: TrainingExerciseConfigSchema,
});

export const ModelName = 'TrainingLog';

export type TrainingLogDocument = HydratedDocument<TrainingLog> & Document;

export const TrainingLogSchema = new Schema<TrainingLog>(
  {
    user_id: { type: Types.ObjectId, ref: 'User' },
    routine_id: {
      type: Types.ObjectId,
      ref: 'Routine',
    },
    training_id: {
      type: Types.ObjectId,
      ref: 'Training',
      require: true,
    },
    name: { type: String, required: true },
    desc: String,
    exercises: {
      type: [TrainingLogExerciseSchema],
      default: [],
    },
    duration: {
      type: Number,
      default: 0,
    },
    comment: String,
    status: {
      type: String,
      enum: ExecuteStatus,
      default: ExecuteStatus.DOING,
    },
    del: { type: Boolean, default: false },
  },
  {
    timestamps: { createdAt: 'created_at', updatedAt: 'updated_at' },
    collection: 'training_log',
  },
);

[Uzlopak]

Try to replace Types.ObjectId with Schema.Types.ObjectId. I have the feeling, it gets converted to String and thats why you find nothing.

https://mongoosejs.com/docs/schematypes.html#what-is-a-schematype

[Here21]

@Uzlopak thanks for help, It doesn't seem to be caused by this problem, even I remove user_id on the filter field, still got nothing:

const filter = {
  status: props.status,
  created_at: undefined,
};
if (props.date) {
  // filter.created_at = {
  //   $gte: dayjs(props.date).startOf('date').toISOString(),
  //   $lte: dayjs(props.date).endOf('date').toISOString(),
  // };
  filter.created_at = {
    $gte: new Date(2022, 0, 21),
    $lte: new Date(2022, 0, 24),
  };
}
logger.debug('findAll:', JSON.stringify(filter));
return this.trainingLog.find(filter);

So, I think the problem at Schema.options.timestamps or mongoose date type, if I copy the query string at mongo shell or Campass, I just change 'date' to ISODate('date') it just work.

const filter = {
 "user_id":ObjectId("61e403e4d3ec913651fcc51b"),
  created_at: {
      $gte: ISODate('2022-01-20T16:00:00.000Z'),
      $lte: ISODate('2022-01-22T16:00:00.000Z')
  }
}

what did you think? I don't have a clue...😵‍💫

[Uzlopak]

This works for me:

const mongoose = require('mongoose');
const ReplSet = require('mongodb-memory-server').MongoMemoryReplSet;

async function main() {

    // Create new instance
    const replSet = new ReplSet({
        instanceOpts: [
            // Set the expiry job in MongoDB to run every second
            {
                port: 27017,
                args: ["--setParameter", "ttlMonitorSleepSecs=1"]
            },
        ],
        dbName: 'mongoose_test',
        replSet: {
            name: "rs0",
            count: 2,
            storageEngine: "wiredTiger",
        },
    });

    await replSet.start();
    await replSet.waitUntilRunning();
    const uri = replSet.getUri('mvce');
    await mongoose.connect(uri);
    const userSchema = mongoose.Schema(
        {
            email: String,
        },
        { timestamps: { createdAt: 'created_at', updatedAt: 'updated_at' } }
    );

    const User = mongoose.model("User", userSchema);

    const doc = await User.create({ email: "test@google.com" });

    console.log(doc.created_at instanceof Date); // true

    // empty result
    console.log(await User.find({created_at: {
        $gte: new Date(2022, 0, 21),
        $lte: new Date(2022, 0, 23),
      }}));

    // should find one
    console.log(await User.find({created_at: {
        $gte: new Date(2022, 0, 21),
        $lte: new Date(2022, 0, 25),
      }}));
    process.exit();
}

main();

Probably you dont use model to create a mongoose model or so...

[Here21]

@Uzlopak Thank you very much, I found the crux of the problem... It was my fault...

 const filter = {
      user_id: new Types.ObjectId(props.user_id),
      status: props.status, // ---> **undefined** It is the root of the problem😂
      created_at: {},
    };
if (props.date) {
  filter.created_at = {
    $gte: dayjs(props.date).startOf('date'),
    $lte: dayjs(props.date).endOf('date'),
  };
}

// ---> final filter

filter = {
  user_id: new ObjectId("61e403e4d3ec913651fcc51b"),
  status: undefined, // oops
  created_at: {....}
}

Why I didn't find it before?

1. I used logger.debug('findAll:', JSON.stringify(filter)); So, the JSON.stringify() will ignore key of undefined...
2. I thought mongoose query would ignore nulls of filter, but it didn't

Thank you again, thank you for helping me solve the problem