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.

Anacrusis

Answer 1

$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.

Carpospore

Woa, retrieve all documents and count them ;-( – Desantis

Answer 2

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

Silicic

This is a valid argument. Using ->count()->getQuery()->execute(); is the way to go. – Jenna

From the way the docs are worded for db.collection.count() and 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. – Aiglet

This answer was linked from PR #833 on Doctrine MongoDB ODM and could use some clarification. Issuing a count through the query builder will utilize the count command (see: Query.php). Additionally, MongoCursor::count() and MongoCollection::count() both invoke the same command. – Data

(continued) See MongoCollection::count(), MongoCursor::count() 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). – Data

Answer 3

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();

Corollary