0

here i have create one query for the show count date wise in the chart. and i have used mongodb. and i have one table collection and in the table collection store multiple documents and in the documents one filed is Action. i want to show count date wise Action like Comment,like both action count want show.but i am create one query using group by but i am return all the document in the query not getting date wise filter data in the query. i don't know where is my mistake in the query any one know then please let me know. i want dates between start to end date.

here this is my documents =>

{
 "_id" : ObjectId("578fa05a7391bb0d34bd3c28"),
  "InId": ObjectId("595e3b2033961713940442cd")
  "Action" : "Comment",
  "datetime" : 1507099928000 // 4th oct 2017 convert date just for info here write
 },
 {
   "_id" : ObjectId("578fa05a7391bb0d34bd3c28"),
  "InId": ObjectId("595e3b2033961713940442cd")
   "Action" : "Comment",
   "datetime" : 1507099928000  // 4th oct 2017 convert date just for info here write
  },
  {
  "_id" : ObjectId("578fa05a7391bb0d34bd3c30"),
  "InId": ObjectId("595e3b2033961713940442cd")
  "Action" : "Comment",
  "datetime" : 1507186328000 // 5th oct 2017 convert date just for info here write
  },
  {
 "_id" : ObjectId("578fa05a7391bb0d34bd3c28"),
  "InId": ObjectId("595e3b2033961713940442cd")
 "Action" : "Comment",
 "datetime" : 1507193528000 // 5th oct 2017 convert date just for info here write
},
{
   "_id" : ObjectId("578fa05a7391bb0d34bd3c28"),
  "InId": ObjectId("595e3b2033961713940442cd")
   "Action" : "Comment",
   "datetime" : 1507279928000 // 6th oct 2017 convert date just for info here write
},
{
    "_id" : ObjectId("578fa05a7391bb0d34bd3c30"),
  "InId": ObjectId("595e3b2033961713940442cd")
    "Action" : "Comment",
    "datetime" : 1507020728000 // 3th oct 2017 convert date just for info here write
}
{
   "_id" : ObjectId("578fa05a7391bb0d34bd3c28"),
  "InId": ObjectId("595e3b2033961713940442cd")
   "Action" : "Comment",
   "datetime" : 1507279928000  // 6th oct 2017 convert date just for info here write
 },
 {
    "_id" : ObjectId("578fa05a7391bb0d34bd3c28"),
  "InId": ObjectId("595e3b2033961713940442cd")
    "Action" : "like",
    "datetime" : 1507279928000  // 6th oct 2017 convert date just for info here write
},
 {
    "_id" : ObjectId("578fa05a7391bb0d34bd3c28"),
  "InId": ObjectId("595e3b2033961713940442cd")
    "Action" : "like",
    "datetime" : 1504256404000 // 1th sep 2017 convert date just for info here write
 }

my current o/p =>

  { _id: '1507020728000', CommentCount: 1 ,Likecount:0},
  { _id: '1507099928000', CommentCount: 1 ,Likecount:0},
  { _id: '1507099928000', CommentCount: 1 ,Likecount:0},
  { _id: '1507186328000', CommentCount: 1,Likecount:0 },
  { _id: '1507186328000', CommentCount: 1,Likecount:0 },
  { _id: '1507279928000', CommentCount: 1,Liekcount:1},
  { _id: '1507279928000', CommentCount: 1,Liekcount:1},
  { _id: '1504256404000', CommentCount: 0,Liekcount:1}, 

my excepted o/p =>

  { _id: '1507020728000', CommentCount: 1 ,Likecount:0},
  { _id: '1507099928000', CommentCount: 2 ,Likecount:0},
  { _id: '1507186328000', CommentCount: 2,Likecount:0 },
  { _id: '1507279928000', CommentCount: 2,Liekcount:1}, 

This is my query =>

            ActivityHistory.aggregate([
          {
              "$match": {
                  "InId": ObjectId("595e3b2033961713940442cd"),                     
                  "datetime": {
                      "$lte": parseInt("1507280404000"), "$gte": parseInt("1507021204000")
                  },
                  "Action": {                          
                      $in: ["Comment", "Like"]
                  }
              }
          },      
          {              
              "$group": {
                  "_id": "$datetime",                               
                  "CommentCount": { $sum: { $cond: [{ $eq: ["$Action", "Comment"] }, 1, 0] } },
                  "Likecount": { $sum: { $cond: [{ $eq: ["$Action", "Like"] }, 1, 0] } },                
              },
                  },
                   { '$sort': { '_id': 1 } }
            ]).exec(function (err, data) {
                if (err) {
                    console.log(err);            
                }
                else {            
                    console.log("Final=>",data);            
                }
        });
Edit
  • 385
  • 4
  • 24
  • Why are your dates returned as string when they are numbers in database ? – s7vr Nov 01 '17 at 12:16
  • here "_id": "$datetime",? – Edit Nov 01 '17 at 12:17
  • No, it should be numbers. $group doesn't change types.Check your documents. Try creating a separate collection and insert documents that you have provided and run your query. – s7vr Nov 01 '17 at 12:19
  • `ld.setHours(0, 0, 0, 0); ` sets hours according to local time. You need `ld.setUTCHours(0, 0, 0, 0);` – s7vr Nov 01 '17 at 12:43
  • thank you so much for your replay and help – Edit Nov 01 '17 at 12:45
  • @Veeram i am asking new post please look that https://stackoverflow.com/questions/47068713/sorting-on-date-is-not-working-using-mongodb?noredirect=1#comment81085582_47068713 – Edit Nov 02 '17 at 06:31
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/158040/discussion-between-edit-and-veeram). – Edit Nov 02 '17 at 06:32

0 Answers0