Converting Mongodb query to PHP

Hi there,

I have a mongo aggregation query that works fine in the mongo shell (and robomongo) but I can not work out how to translate this into a PHP query.

I am using PHP 5.6 with the latest mongo class (MongoDB\Driver\Query).  The mongo query looks like this:

db.products.aggregate(
   [
     {$match: {
             vendor_name : "vendor8",
             distributor_id : 8
         }
     },    
     { $sort: { 
         cw_product_code: 1, download_Date: 1 
         } 
     },
     { $group:
         {
           _id: "$cw_product_id",
           lastDownloadDate: { $last: "$download_Date" },
         }
     }        
   ],
     {allowDiskUse: true}     
)

Open in new window


Any help to point me in the right direction would be appreciated.
Rick PrattAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HainKurtSr. System AnalystCommented:
first tell us what data do you have and what do you want to get...

forget about the mango thingy...
0
Rick PrattAuthor Commented:
I have a collection of documents in Mongo that describe a number of products that we sell.  I need to get a list of the last inserted document for each product. The Mongo code does exactly what I want it to do and now I need to translate it to PHP code for use in our scripts.
0
Rick PrattAuthor Commented:
I have a collection of documents in Mongo that describe a number of products that we sell.  I need to get a list of the last inserted document for each product. The Mongo code does exactly what I want it to do and now I need to translate it to PHP code for use in our scripts.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Julian HansenCommented:
forget about the mango thingy...
This is not helpful - the OP is asking how to query a MongoDB database from PHP - the data is not relevant - he wants the method.

Take a look at this link http://php.net/manual/en/mongodb-driver-query.construct.php

You need to create your filter parameter (an array of field => values or object) you wish to search for in your case it would look something like this
$filter = array (
  "vendor_name" =>"vendor8",
  "distributor_id" => 8,
);

Open in new window

Your queryOptions array will probably look something like this
$queryOptions = array(
  "sort" => array("cw_product_code" => 1, "download_Date" => 1  ),
)

Open in new window


More info here https://docs.mongodb.com/manual/tutorial/query-documents/

The group option is confusing - some references to aggregate but not in Driver. Must admit the PHP support for Mongo seems a bit of a mess.
0
Rick PrattAuthor Commented:
Thanks Julian,

Your comments are very helpful and I've been able to get output from the Mongo server however as you anticipated, the group option seems to be the tricky bit and the driver is ignoring it when I run the following:

$filter = [
        'vendor_name' => "vendor8",
        'distributor_id' => 8
    ];    
  $options = [
    'sort' => array ('cw_product_code' => 1, 'download_Date' => 1 ),
    'group' => array ( '_id:' =>  '$cw_product_id', 'lastDownloadDate' => array('$last' => '$download_Date' )),
    'allowDiskUse' => true
];

There are six documents that match the criteria of vendor8 and distributor_id of 8 and the group option should return 3 of them for the latest date but it returns all six.

It looks like I might have to try and find a contact for the authors of the new driver unless you have any other suggestions.

Thanks again,
0
Julian HansenCommented:
I seem to remember seeing something about MongDB\Client having an aggregate - did not have time to dig deeper or test and with no PHP / MongoDB experience I am guessing here.
0
Ray PaseurCommented:
0
Julian HansenCommented:
@Ray I think he has that in his OP - the thing is how to implement the group with PHP,
0
HainKurtSr. System AnalystCommented:
@Julian Hansen

This is not helpful - the OP is asking how to query a MongoDB database from PHP - the data is not relevant - he wants the method.

sorry, I got it wrong...

I thought he wants a query to get the data from some other db and he gave us some code from mango...
Now I see he wants to get data from mango and wants to know how to implement it in php...
0
Julian HansenCommented:
mango
It's MongoDB - a noSQL document based database as in the MEAN stack (MongoDB, Express, Angular and Node)
1
Rick PrattAuthor Commented:
I managed to resolve this. The trick was to use a command rather than a query. The following code gave me the correct output as per the mongo shell.

$command = new MongoDB\Driver\Command([
    'aggregate' => 'products',
    'pipeline' => [
        ['$match' => [ 'vendor_name' => 'vendor8', 'distributor_id' => 8 ]],
        ['$sort'  => [ 'cw_product_code' => 1, 'download_Date' => 1 ]],
        ['$group' => [ '_id' =>  '$cw_product_id', 'lastDownloadDate' => ['$last' => '$download_Date' ]]],
    ],
    'allowDiskUse' => true,
]);
$cursor = $mng->executeCommand('inventory', $command);  
$documents = $cursor->toArray();

Thanks Julian for pointing me in the right direction.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Julian HansenCommented:
You are welcome - glad you got sorted out.
0
Rick PrattAuthor Commented:
I had to source the answer from elsewhere and posted the results for others to reference in the future.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.