CODEWITHSUNDEEP
phpmongodbaggregation-framework
Sreenath
Sreenath

Reputation: 510

Is it possible to combine two Mongodb queries?

Actually I have two date ranges, one is actual date range and the other is comparison date range. For each date range I'm executing two different queries.

I selected aggregation method. Code I wrote for each date range is ..

$resultsetpre = $db_1->command(array(
    'aggregate' => 'ACCOUNT_PERFORMANCE',
    'pipeline'  => array(
        array(
            '$match' => array('Date' => array('$gte'=>$actualstartdate,'$lte'=>$actualenddate))
        ),
        array('$group'  => array(
            '_id'       => "$Id",
            'Clicks'  => array('$sum' => '$Clicks'),
            'Impressions' => array('$sum' => '$Impressions'),
            'Cost' => array('$sum' => '$Cost'),
            'Conversions' => array('$sum' => '$Conversions'),
        ))
    ),
));

My doubt is can I write a query, so that gets data for both date ranges in diff columns. Since I had too many of records it is taking long time.

I'm very thankful If any one help me out from this. Thanks In Advance.

Upvotes: 0

Views: 629

Answers (2)

Asya Kamsky
Asya Kamsky

Reputation: 42342

Sounds like you want to compare the number of clicks (or some other sum) for two different date ranges. Think of it as doing an aggregation by days. Instead of days you will translate your date into range1, range2 or other and the group by those values.

Here's an example to get you started that compares two date ranges, start1-end1, start2-end2:

project = { "$project" : { 
             "clicks" : 1,
             "range"  : { "$cond" : [ { "$and" : [ 
                                                  { "$gt":["$Date",start1] },
                                                  { "$lt":["$Date",end1] }
                                               ]
                                      },
                                      "range1",
                                      { "$cond" : [ { "$and" : [
                                                          { "$gt":["$Date",start2] },
                                                          { "$lt":["$Date",end2] }
                                                               ]
                                                     },
                                                     "range2",
                                                     null
                                                  ]
                                      }
                                    ]
                        }
             }
};

group = { "$group" : { "_id" : "$range",
                       "clicks" : { "$sum" : "$clicks" }
        }
};

Now running db.collection.aggregate( project, group ) will give you sum of clicks for each of the two date ranges. You can $match as the middle step to remove {range:null} to get rid of documents that didn't match your two ranges.

You mentioned that you have an "Id" field in the original documents you want to aggregate by, so all you need to do is add "Id":1, to the $project step and then change the grouping clause in the $group as follows:

group = { "$group" : { "_id" : {"Id" : "$Id", "range" : "$range"},
                       "clicks" : { "$sum" : "$clicks" }
        }
};

Instead of grouping by date ranges across the whole collection, this will group by Id,range so that for each Id you get a sum for range1 and a sum for range2.

Upvotes: 1

Salvador Dali
Salvador Dali

Reputation: 222461

May be I am missing something, but what is the problem with combining them in the following way:

'$match' => array(
   'Date' => array('$gte'=>$actualstartdate,'$lte'=>$actualenddate),
    // here is your another query
)

If I am missing the point, please post both queries you are performing.

If you need to combine two the same queries, you can do this with $or

$match' => array(
   '$or' => array(
      'Date' => array('$gte'=>$actualstartdate1,'$lte'=>$actualenddate1),
      'Date' => array('$gte'=>$actualstartdate2,'$lte'=>$actualenddate2),
   )
)

Upvotes: 0

Related Questions