CODEWITHSUNDEEP
mongodbdoctrine-odm
chris
chris

Reputation: 1255

MongoDB ODM SELECT COUNT(*) equivalent

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.

Upvotes: 14

Views: 15168

Answers (3)

Hett
Hett

Reputation: 3825

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

Upvotes: 1

Diego Vidal
Diego Vidal

Reputation: 481

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

Upvotes: 48

Jamie Sutherland
Jamie Sutherland

Reputation: 2790

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

Upvotes: 22

Related Questions