0

I need to run a query in a collection('Model: Ag_escalaatendimento') containing and array of subdocuments and return only documents in a range date.

My problem is my query is returning documents with dates out of the range.

What am I doing wrong?

//Schemas

const agEscalaAtendimentosSchema = new mongoose.Schema({
  ativo:{type:Boolean},
  profissional:{type:mongoose.Schema.Types.ObjectId, ref:'Profissional'},
  horarios_gerados:[{date_atd:{di:{type:Date}, df:{type:Date}},situacao:{type:Number, default:1}}],
});

module.exports = mongoose.model('Ag_escalaatendimento', agEscalaAtendimentosSchema,'ag_escalaatendimentos' );

const profissionaisSchema = new mongoose.Schema({
  nome: {type: String,required:true},
  cpf: {type: String},
},{ toJSON: { virtuals: true } });
module.exports = mongoose.model('Profissional', profissionaisSchema,'profissionais' );

//query

  var qry={ profId: '593c838ad32a453b538b1a72',
  dataInicio: '2017-08-11T00:42:05.575Z',
  dataFim: '2017-08-26T00:42:05.575Z' }

  var qrySearch={"_id": new ObjectId(qry.profId)}
  var di=new Date(qry.dataInicio);
  var df=new Date(qry.dataFim);

  Profissional.find(qrySearch)
    .exec( (err,data) => {
        var p1;
        var p=[];
        for (var i = 0, len = data.length; i < len; i++) {
              var id=data[i]._id;
              p1=Ag_escalaatendimento.find({'profissional':id, 'horarios_gerados':{ $elemMatch:{'date_atd.di':{$gte:di},'date_atd.df':{$lte:df}} }}).exec();
              p.push(p1);
        }
        Promise.all(p).then((response)=>{
           var h1=response[0]; //array
           h1.forEach( (item)=>console.log(item.horarios_gerados.slice(0,3)))
        }, (error)=>{
           console.log(error);
          }
        )
    })

//Wrong Results

  [ { date_atd: { df: 2017-01-02T12:00:00.000Z, di: 2017-01-02T10:00:00.000Z },
    situacao: 1,
    _id: 598cf1b7b16a301abcb5661d },
  { date_atd: { df: 2017-01-02T18:00:00.000Z, di: 2017-01-02T16:00:00.000Z },
    situacao: 1,
    _id: 598cf1b7b16a301abcb5661c },
  { date_atd: { df: 2017-01-03T12:00:00.000Z, di: 2017-01-03T10:00:00.000Z },
    situacao: 1,
    _id: 598cf1b7b16a301abcb5661b } ]
Luiz Alves
  • 2,575
  • 4
  • 34
  • 75
  • Seems to be doing exactly what you ask. `$elemMatch` in a "query" only **matches the documents**. If you actually want to return "only the matching array entries" then you need to apply `$filter` for multiple matches or the positional `$` projection for **only one** per array. Also makes the `.slice()` redundant because you "should" be using the aggregation framework for that instead. – Neil Lunn Aug 11 '17 at 01:06
  • Thanks, Mr Lunn, I will try to use aggregate. I have used sql database all my life and with NOSQL I have no practice. – Luiz Alves Aug 11 '17 at 01:15
  • Does aggregate supports promises? – Luiz Alves Aug 11 '17 at 01:17
  • You **still** "should" use [`$elemMatch`](https://docs.mongodb.com/manual/reference/operator/query/elemMatch/) for the initial aggregation pipeline `$match` stage because that will only select "documents" which have array items matching the conditions ,and that's a "good thing". However the actual "array filtering" should be delegated to `$filter` as shown in answers. And [`$slice`](https://docs.mongodb.com/manual/reference/operator/aggregation/slice/) can in fact reduce the response from the "server" to the first 3 instead. And yes **all** of the API methods support promises. – Neil Lunn Aug 11 '17 at 01:19
  • I am trying: p1=Ag_escalaatendimento.aggregate([ { $match: {'profissional':id}}, { $project: { horarios_gerados: {$filter: { input: '$horarios_gerados', as: 'item', cond: {$gt: ['$$item.date_atd.di', di]} }} }} ]).exec(); Is this correct? – Luiz Alves Aug 11 '17 at 01:27

0 Answers0