Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Converting Mongodb query to PHP

Posted on 2017-04-28
13
Medium Priority
?
146 Views
Last Modified: 2017-05-06
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.
0
Comment
Question by:Rick Pratt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
  • +1
13 Comments
 
LVL 59

Expert Comment

by:HainKurt
ID: 42113136
first tell us what data do you have and what do you want to get...

forget about the mango thingy...
0
 

Author Comment

by:Rick Pratt
ID: 42113152
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
 

Author Comment

by:Rick Pratt
ID: 42113153
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 59

Assisted Solution

by:Julian Hansen
Julian Hansen earned 2000 total points
ID: 42113186
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
 

Author Comment

by:Rick Pratt
ID: 42113346
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
 
LVL 59

Expert Comment

by:Julian Hansen
ID: 42113366
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 42113380
0
 
LVL 59

Expert Comment

by:Julian Hansen
ID: 42113404
@Ray I think he has that in his OP - the thing is how to implement the group with PHP,
0
 
LVL 59

Expert Comment

by:HainKurt
ID: 42113428
@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
 
LVL 59

Expert Comment

by:Julian Hansen
ID: 42113429
mango
It's MongoDB - a noSQL document based database as in the MEAN stack (MongoDB, Express, Angular and Node)
1
 

Accepted Solution

by:
Rick Pratt earned 0 total points
ID: 42114817
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
 
LVL 59

Expert Comment

by:Julian Hansen
ID: 42114832
You are welcome - glad you got sorted out.
0
 

Author Closing Comment

by:Rick Pratt
ID: 42123384
I had to source the answer from elsewhere and posted the results for others to reference in the future.
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question