Search code examples
mongodbphp-mongodbnosql

PHP MongoDB ordered distinct with skip and limit

Is there anyway to order a distinct query in MongoDB (PHP)? Would even be nicer if I could apply skip() and limit() on it. But I haven't found any ways to do this yet.

In SQL I would do something like this in the follow way:

SELECT distinct(fieldname) 
FROM tablename
ORDER BY fieldname
LIMIT 10
OFFSET 100
Solution

  • You can implement this sort of query using the new Aggregation Framework in MongoDB 2.2:

    db.collname.aggregate(
    { $group : { _id : "$fieldname" }},
    { $sort : { _id: 1 }},
    { $skip : 100 },
    { $limit : 10 }
);

    There are some limitations to be aware of, in particular:

    • Results are returned in-line so cannot exceed the maximum document size (16Mb for MongoDB 2.2)
    • If any single aggregation operation consumes more than 10 percent of system RAM the operation will produce an error