MongoDB ODM SELECT COUNT(*) equivalent

Question

I wonder if there is an equivalent to the MySQL-Query:

   SELECT COUNT(*) FROM users

in MongoDB ODM?

This might work:

$qb = $this->dm->createQueryBuilder('Documents\Functional\Users');
$qb->select('id');   
$query   = $qb->getQuery();
$results = $query->execute();
echo $query->count();

But aren't then all IDs returned and how does this affect performance if there are more complex documents in database. I don't want too send to much data around just to get a count.

score 47 · Answer 1 · answered Dec 14 '13 at 22:45

47

A small contribution:

if you run the count this way:

$count = $this->dm->createQueryBuilder('Documents\Functional\Users')
         ->getQuery()->execute()->count();

Doctrine runs this query:

db.collection.find();

however, if the code is as follows:

$count = $this->dm->createQueryBuilder('Documents\Functional\Users')
         ->count()->getQuery()->execute();

Doctrine in this case run this query:

db.collection.count();

I do not know if there is improvement in performance, but I think most optimal

I hope that is helpful

answered Dec 14 '13 at 22:45

Diego Vidal

471
1
4
2

1

This is a valid argument. Using ->count()->getQuery()->execute(); is the way to go. – epicwhale Mar 01 '14 at 13:06
From the way the docs are worded for [`db.collection.count()`](http://docs.mongodb.org/manual/reference/method/db.collection.count/) and [`cursor.count()`](http://docs.mongodb.org/manual/reference/method/cursor.count/) it sounds like it makes no difference. For both, "returns the count of documents that would match a `find()` query.", implying either one will call `find()` ultimately. – Quolonel Questions Mar 31 '14 at 01:52
1

This answer was linked from [PR #833](https://github.com/doctrine/mongodb-odm/issues/833) on Doctrine MongoDB ODM and could use some clarification. Issuing a count through the query builder will utilize the [count command](http://docs.mongodb.org/manual/reference/command/count/) (see: [Query.php](https://github.com/doctrine/mongodb/blob/master/lib/Doctrine/MongoDB/Query/Query.php#L267)). Additionally, MongoCursor::count() and MongoCollection::count() both invoke the same command. – jmikola Mar 31 '14 at 21:09
1

(continued) See [MongoCollection::count()](https://github.com/mongodb/mongo-php-driver/blob/2c2f497b0c0def9db74281eb743db1bc11a8035f/collection.c#L2027), [MongoCursor::count()](https://github.com/mongodb/mongo-php-driver/blob/2c2f497b0c0def9db74281eb743db1bc11a8035f/cursor.c#L1140) implementations for context. There is minimal overhead in creating a MongoCursor objects if you do not begin iteration (the driver will not communicate with the server, and the cursor merely holds its constructor arguments and options). – jmikola Mar 31 '14 at 21:10

score 22 · Accepted Answer · answered Jun 13 '12 at 20:09

$count = $this->dm->createQueryBuilder('Documents\Functional\Users')
             ->getQuery()->execute()->count();

The above will give you the number of documents inside a collection of Users. The query in question doesn't return all of the documents and then count them. It generates a cursor to the collection and from there it knows the count. Only once you start to iterate over the cursor does the driver start pulling data from the database.

A handy operator for performance is the eagerCursor(true) which will retrieve all the data in the query before hydration and close the cursor. Use this if you know the data you want to get and you'll be finished with it after the query.

Eager Cursor

If you have references that you know you will be iterating over. Use the prime(true) method on them.

Prime

If you want to return all the elements raw data, you can use hydrate(false) method in the query to disable the hydration system.

Woa, retrieve all documents and count them ;-( – Thomas Decaux May 26 '15 at 15:08 — Thomas Decaux, May 26 '15 at 15:08

score 1 · Answer 3 · answered Dec 19 '19 at 06:37

For Doctrine ODM 2 you can switch query type to count before call getQuery:

    return $this->createQueryBuilder()
        ->field('storage')->equals($storage)
        ->field('priority')->in($priorities)
        ->count()
        ->getQuery()
        ->execute();

MongoDB ODM SELECT COUNT(*) equivalent

3 Answers3