Query Builder API

Executing Queries

You can execute a query by getting a Query through the getQuery() method:

1
<?php

$qb = $dm->createQueryBuilder('User');
$query = $qb->getQuery();
2
3
4

Now you can execute() that query and it will return a cursor for you to iterate over the results:

1
<?php

$users = $query->execute();
2
3

Debugging Queries

While building not complicated queries is really simple sometimes it might be hard to wrap your head around more sophisticated queries that involves building separate expressions to work properly. If you are not sure if your the query constructed with Builder is in fact correct you may want to debug() it

1
<?php

$qb = $dm->createQueryBuilder('User');
$query = $qb->getQuery();
$debug = $query->debug();
2
3
4
5

At this point your query is prepared - that means ODM done all its job in renaming fields to match their database name, added discriminator fields, applied filters, created correct references and all other things you employ ODM to. The array returned by ->debug() is what is passed to the underlying driver for the query to be performed.

Eager Cursors

You can configure queries to return an eager cursor instead of a normal mongodb cursor using the Builder#eagerCursor() method:

1
<?php

$qb = $dm->createQueryBuilder('User')
    ->eagerCursor(true);
$query = $qb->getQuery();
$cursor = $query->execute(); // instanceof Doctrine\ODM\MongoDB\EagerCursor
2
3
4
5
6

Iterating over the $cursor will fetch all the data in a short and small cursor all at once and will hydrate one document at a time in to an object as you iterate:

1
<?php

foreach ($cursor as $user) { // queries for all users and data is held internally
    // each User object is hydrated from the data one at a time.
}
2
3
4
5

Getting Single Result

If you want to just get a single result you can use the Query#getSingleResult() method:

1
<?php

$user = $dm->createQueryBuilder('User')
    ->field('username')->equals('jwage')
    ->getQuery()
    ->getSingleResult();
2
3
4
5
6

Selecting Fields

You can limit the fields that are returned in the results by using the select() method:

1
<?php

$qb = $dm->createQueryBuilder('User')
    ->select('username', 'password');
$query = $qb->getQuery();
$users = $query->execute();
2
3
4
5
6

In the results only the data from the username and password will be returned.

Index hints

You can force MongoDB to use a specific index for a query with the hint() method (see hint)

1
<?php

$qb = $dm->createQueryBuilder('User')
    ->hint('user_pass_idx');
$query = $qb->getQuery();
$users = $query->execute();
2
3
4
5
6

Selecting Distinct Values

Sometimes you may want to get an array of distinct values in a collection. You can accomplish this using the distinct() method:

1
<?php

$ages = $dm->createQueryBuilder('User')
    ->distinct('age')
    ->getQuery()
    ->execute();
2
3
4
5
6

The above would give you an ArrayCollection of all the distinct user ages!

Refreshing Documents

When a query (e.g. geoNear, find) returns one or more hydrated documents whose identifiers are already in the identity map, ODM returns the managed document instances for those results. In this case, a managed document's data may differ from whatever was just returned by the database query.

The query builder's refresh() method may be used to instruct ODM to override the managed document with data from the query result. This is comparable to calling DocumentManager::refresh() for a managed document. The document's changeset will be reset in the process.

1
<?php

$user = $dm->createQueryBuilder('User')
    ->field('username')->equals('jwage')
    ->refresh()
    ->getQuery()
    ->getSingleResult();

// Jon's user will have the latest data, even if it was already managed
2
3
4
5
6
7
8
9

Refreshing is not applicable if hydration is disabled.

Fetching Documents as Read-Only

Similar to refresh(), readOnly() instructs ODM to not only hydrate the latest data but also to create new document's instance (i.e. if found document would be already managed by Doctrine, new instance will be returned) and not register it in UnitOfWork.

This technique can prove especially useful when using select() with no intent to update fetched documents.

1
<?php

$user = $dm->createQueryBuilder('User')
    ->field('username')->equals('malarzm')
    ->readOnly()
    ->getQuery()
    ->getSingleResult();

// Maciej's user will have the latest data, and will not be the same object
// as the one that was already managed (if it was)
2
3
4
5
6
7
8
9
10

Read-Only is not applicable if hydration is disabled.

Disabling Hydration

For find queries the results by default are hydrated and you get document objects back instead of arrays. You can disable this and get the raw results directly back from mongo by using the hydrate(false) method:

1
<?php

$users = $dm->createQueryBuilder('User')
    ->hydrate(false)
    ->getQuery()
    ->execute();

print_r($users);
2
3
4
5
6
7
8

Limiting Results

You can limit results similar to how you would in a relational database with a limit and offset by using the limit() and skip() method.

Here is an example where we get the third page of blog posts when we show twenty at a time:

1
<?php

$blogPosts = $dm->createQueryBuilder('BlogPost')
    ->limit(20)
    ->skip(40)
    ->getQuery()
    ->execute();
2
3
4
5
6
7

Sorting Results

You can sort the results by using the sort() method:

1
<?php

$qb = $dm->createQueryBuilder('Article')
    ->sort('createdAt', 'desc');
2
3
4

If you want to an additional sort you can call sort() again. The calls are stacked and ordered in the order you call the method:

1
<?php

$query->sort('featured', 'desc');
2
3

Map Reduce

You can also run map reduced find queries using the Query object:

1
<?php

$qb = $this->dm->createQueryBuilder('Event')
    ->field('type')->equals('sale')
    ->map('function() { emit(this.userId, 1); }')
    ->reduce("function(k, vals) {
        var sum = 0;
        for (var i in vals) {
            sum += vals[i]; 
        }
        return sum;
    }");
$query = $qb->getQuery();
$results = $query->execute();
2
3
4
5
6
7
8
9
10
11
12
13
14

If you just want to reduce the results using a javascript function you can just call the where() method:

1
<?php

$qb = $dm->createQueryBuilder('User')
    ->where("function() { return this.type == 'admin'; }");
2
3
4

You can read more about the $where operator in the Mongo docs.

Conditional Operators

The conditional operators in Mongo are available to limit the returned results through a easy to use API. Doctrine abstracts this to a fluent object oriented interface with a fluent API. Here is a list of all the conditional operation methods you can use on the Query\Builder object.

• where($javascript)
• in($values)
• notIn($values)
• equals($value)
• notEqual($value)
• gt($value)
• gte($value)
• lt($value)
• lte($value)
• range($start, $end)
• size($size)
• exists($bool)
• type($type)
• all($values)
• mod($mod)
• addOr($expr)
• references($document)
• includesReferenceTo($document)

Query for active administrator users:

1
<?php

$qb = $dm->createQueryBuilder('User')
    ->field('type')->equals('admin')
    ->field('active')->equals(true);
2
3
4
5

Query for articles that have some tags:

1
<?php

$qb = $dm->createQueryBuilder('Article')
    ->field('tags.name')->in(array('tag1', 'tag2'));
2
3
4

Read more about the $in operator in the Mongo docs

Query for articles that do not have some tags:

1
<?php

$qb = $dm->createQueryBuilder('Article')
    ->field('tags.name')->notIn(array('tag3'));
2
3
4

Read more about the $nin operator in the Mongo docs.

1
<?php

$qb = $dm->createQueryBuilder('User')
    ->field('type')->notEqual('admin');
2
3
4

Read more about the $ne operator in the Mongo docs.

Query for accounts with an amount due greater than 30:

1
<?php

$qb = $dm->createQueryBuilder('Account')
    ->field('amount_due')->gt(30);
2
3
4

Query for accounts with an amount due greater than or equal to 30:

1
<?php

$qb = $dm->createQueryBuilder('Account')
    ->field('amount_due')->gte(30);
2
3
4

Query for accounts with an amount due less than 30:

1
<?php

$qb = $dm->createQueryBuilder('Account')
    ->field('amount_due')->lt(30);
2
3
4

Query for accounts with an amount due less than or equal to 30:

1
<?php

$qb = $dm->createQueryBuilder('Account')
    ->field('amount_due')->lte(30);
2
3
4

Query for accounts with an amount due between 10 and 20:

1
<?php

$qb = $dm->createQueryBuilder('Account')
    ->field('amount_due')->range(10, 20);
2
3
4

Read more about conditional operators in the Mongo docs.

Query for articles with no comments:

1
<?php

$qb = $dm->createQueryBuilder('Article')
    ->field('comments')->size(0);
2
3
4

Read more about the $size operator in the Mongo docs.

Query for users that have a login field before it was renamed to username:

1
<?php

$qb = $dm->createQueryBuilder('User')
    ->field('login')->exists(true);
2
3
4

Read more about the $exists operator in the Mongo docs.

Query for users that have a type field that is of integer bson type:

1
<?php

$qb = $dm->createQueryBuilder('User')
    ->field('type')->type('integer');
2
3
4

Read more about the $type operator in the Mongo docs.

Query for users that are in all the specified Groups:

1
<?php

$qb = $dm->createQueryBuilder('User')
    ->field('groups')->all(array('Group 1', 'Group 2'));
2
3
4

Read more about the $all operator in the Mongo docs.

1
<?php

$qb = $dm->createQueryBuilder('Transaction')
    ->field('field')->mod('field', array(10, 1));
2
3
4

Read more about the $mod operator in the Mongo docs.

Query for users who have subscribed or are in a trial.

1
<?php

$qb = $dm->createQueryBuilder('User');
$qb->addOr($qb->expr()->field('subscriber')->equals(true));
$qb->addOr($qb->expr()->field('inTrial')->equals(true));
2
3
4
5

Read more about the $or operator in the Mongo docs.

The references() method may be used to query the owning side of a @ReferenceOne relationship. In the following example, we query for all articles written by a particular user.

1
<?php

// Suppose $user has already been fetched from the database
$qb = $dm->createQueryBuilder('Article')
    ->field('user')->references($user);
2
3
4
5

The includesReferenceTo() method may be used to query the owning side of a @ReferenceMany relationship. In the following example, we query for the user(s) that have access to a particular account.

1
<?php

// Suppose $account has already been fetched from the database
$qb = $dm->createQueryBuilder('User')
    ->field('accounts')->includesReferenceTo($account);
2
3
4
5

Text Search

You can use the $text operator to run a text search against a field with a text index. To do so, create a document with a text index:

1
<?php

/**
 * @Document
 * @Index(keys={"description"="text"})
 */
class Document
{
    /** @Id */
    public $id;

    /** @Field(type="string") */
    public $description;

    /** @Field(type="float", notSaved=true) */
    public $score;
}
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

You can then run queries using the text operator:

1
<?php

// Run a text search against the index
$qb = $dm->createQueryBuilder('Document')
    ->text('words you are looking for');
2
3
4
5

To fetch the calculated score for the text search, use the selectMeta() method:

1
<?php

// Run a text search against the index
$qb = $dm->createQueryBuilder('Document')
    ->selectMeta('score', 'textScore')
    ->text('words you are looking for');
2
3
4
5
6

You can also change the language used for stemming using the language() method:

1
<?php

// Run a text search against the index
$qb = $dm->createQueryBuilder('Document')
    ->text('parole che stai cercando')
    ->language('it');
2
3
4
5
6

Update Queries

Doctrine also supports executing atomic update queries using the Query\Builder object. You can use the conditional operations in combination with the ability to change document field values atomically. Additionally if you are modifying a field that is a reference you can pass managed document to the Builder and let ODM build DBRef object for you.

You have several modifier operations available to you that make it easy to update documents in Mongo:

• set($name, $value, $atomic = true)
• setNewObj($newObj)
• inc($name, $value)
• unsetField($field)
• push($field, $value)
• pushAll($field, array $valueArray)
• addToSet($field, $value)
• addManyToSet($field, array $values)
• popFirst($field)
• popLast($field)
• pull($field, $value)
• pullAll($field, array $valueArray)