skip to Main Content

I’d like filter all records with populated attributes in a query. This is TypeScript code:

router.get("/slice", async (req, res) => {
    if (req.query.first && req.query.rowcount) {
        const first: number = parseInt(req.query.first as string);
        const rowcount: number = parseInt(req.query.rowcount as string);
        const question: string = req.query.question as string;
        if (question) {
            let result = await QuestionAnswer.QuestionAnswer.find().populate('question_id').exec();
            let filtered: any[] = [];
            result.forEach((record) => {
                let q = record.get("question_id.question");
                if (q.includes(question))
                    filtered.push(record);
            });
            filtered = filtered.slice(first, first + rowcount);
            res.send(filtered);
        } else {
            let result = await QuestionAnswer.QuestionAnswer.find().skip(first).limit(rowcount).populate('question_id');
            res.send(result);
        }
    } else {
        res.status(404).send();
    }
});

Schema is the following:

const questionSchema = new Schema({
    question: {
        type: String,
        required: true,
    },
    topic_id: {
        type: Types.ObjectId,
        required: true,
        ref: 'topic',
    },
    explanation: {
        type: String,
        required: true,
    },
});

const questionAnswerSchema = new Schema({
    question_id: {
        type: Types.ObjectId,
        required: true,
        ref: 'question',
    },
    answer: {
        type: String,
        required: true,
    },
    isCorrect: {
        type: Boolean,
        required: true,
    },
});

How can I do it in a query instead array filtering?
I found a solution with aggregation and lookup. Is there any way with find?

2

Answers


  1. Chosen as BEST ANSWER

    With aggregation I can filter the result with the following code:

        const query = {
                'question_id.question': { $regex: question, $options: 'i' },
        };
        QuestionAnswer.QuestionAnswer.aggregate([
            {
                $lookup: {
                    from: 'questions',
                    localField: 'question_id',
                    foreignField: '_id',
                    as: 'question',
                },
            },
            {
                $project: {
                    _id: 1,
                    answer: 1,
                    isCorrect: 1,
                    question_id: { $arrayElemAt: ['$question', 0] },
                },
            }
        ]).match(query).exec().then((result) => {
            res.send(result);
        });
    

  2. Something like this may work using regex:

    router.get("/slice", async (req, res) => {
      if (req.query.first && req.query.rowcount) {
        const first: number = parseInt(req.query.first as string);
        const rowcount: number = parseInt(req.query.rowcount as string);
        const question: string = req.query.question as string;
    
        if (question) {
          const filtered = await QuestionAnswer.QuestionAnswer.find({
            'question_id.question': { $regex: question, $options: 'i' }
          }).populate('question_id').skip(first).limit(rowcount).exec();
    
          res.send(filtered);
        } else {
          const result = await QuestionAnswer.QuestionAnswer.find()
            .skip(first)
            .limit(rowcount)
            .populate('question_id')
            .exec();
    
          res.send(result);
        }
      } else {
        res.status(404).send();
      }
    });
    

    Specifying "i" in options is for case insensitive filtering.

    Haven’t tested it, but should work as expected.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search