The QueryBuilder

The QueryBuilder provides an API that is designed for programmatically constructing ODM query objects.

Creating a query builder instance

You can create instances of QueryBuilder in one of two ways, either via the DocumentManager or via a DocumentRepository.

Via the document manager

You can create the QueryBuilder with the DocumentManager using the createQueryBuilder method.

<?php
$qb = $documentManager->createQueryBuilder();

The following example gets all documents where the name property is equal to daniel and orders the results by username in ascending order.

<?php

$qb->where($qb->expr()->eq('name', 'daniel'))
   ->orderBy('username', 'ASC');

$query = $qb->getQuery();
$users = $query->execute();

Note

Unlike the ORM it is not nescessary to specify a source to select from, the above example will find any class of document matching the criteria.

Via a document repository

You can also create a QueryBuilder from a DocumentRepsitory instance, doing so will automatically select only those records which are associated with the DocumentRepository.

<?php

$postsRepository = $dm->getRepository('MyVendor/Blog/Document/Post');
$qb = $postsRepository->createQueryBuilder();
$posts = $qb->getQuery()->execute();

The above code block will select all documents in the document tree of class Post. This feature is especially useful within a document repository class.

Example showing the use of the query builder in a DocumentRepository:

<?php

namespace MyVendor\Blog\Repository;
use Doctrine\ODM\PHPCR\DocumentRepository;

class Post extends DocumentRepository
{
    public function getPostsByAuthor($authorName)
    {
        $qb = $this->createQueryBuilder();
        $qb->where(
            $qb->expr('author', 'dtl')
        );

        return $qb->getQuery()->execute();
    }
}

Working with the QueryBuilder

Selecting specific properties - select

<?php
$qb->select('username')
   ->addSelect('firstname')
   ->addSelect('lastname');

Note

Select is currently only usefull with PHPCR hydration, ODM hydration will hydrate all fields regardless of the column selection.

Limiting the number of results

You can specify a maximum number of results and the index of the first result (the offset).

<?php
// select a maximum of 10 records.
$qb->from('MyVendor/Blog/Document/User')
   ->setMaxResults(10);

// select a maximum of 10 records from the position of the 20th record.
$qb->from('MyVendor/Blog/Document/User')
   ->setMaxResults(10)
   ->setFirstResult(20);

Restrict query to document class or node type

You can restrict either the document class or the node type. Attempting to specify both will result in an Exception because by setting the document class you are implicitly setting the node type.

<?php

$dm->getQueryBuilder()
   ->from('MyVendor/Blog/Document/User'); // select only from user documents

// or

$dm->getQueryBuilder()
   ->nodeType('nt:mynodetype'); // select only documents with node type nt:mynodetype.

// but not

$dm->getQueryBuilder()
   ->nodeType('nt:mynodetype')
   ->from('MyVendor/Blog/Document/User')
   ->getQuery(); // this will throw an Exception.

Specifying selection criteria

You can specify selection criteria, or Expressions, with the where method. You can add additional Expressions with andWhere and orWhere.

<?php

// where name is "daniel"
$qb->where($qb->expr()->eq('name', 'daniel'));

// where username is "dtl" AND name is "daniel"
$qb->where($qb->expr()->eq('username', 'dtl'));
   ->andWhere($qb->expr()->eq('name', 'daniel'));

// which is equivalent to
$qb->where($qb->expr()->andX(
    $qb->expr()->eq('username', 'dtl'),
    $qb->expr()->eq('name', 'daniel')
));

// where username is "dtl" OR name is "daniel"
$qb->where($qb->expr()->eq('username', 'dtl'))
   ->orWhere($qb->expr()->eq('name', 'daniel'));

// which is equivalent to
$qb->where($qb->expr()->orX(
    $qb->expr()->eq('username', 'dtl'),
    $qb->expr()->eq('name', 'daniel')
));

Ordering results

You can specify the property or properties by which to order the queries results with the orderBy method. You can specify additional orderings with addOrderBy, or you can pass an array of property names to orderBy.

The ordering direction is specified as either ASC (ascending order, e.g. a-z, 0-9) or DESC (descending order, e.g. z-a, 9-0). The default is ASC.

Add a single ordering:

<?php

$qb->orderBy('username', 'ASC'); // username assending

Descending:

<?php

$qb->orderBy('username', 'DESC'); // username descending

Add two orderings:

<?php

$qb->orderBy('username');
$qb->addOrderBy('name'); // username then name ascending (ORDER BY username, name ASC)

Add two orderings by passing an array to orderBy:

<?php

$qb->orderBy(array('username', 'name'), 'ASC'); // same as previous example

The Expression Builder

The ExpressionBuilder is a class which allows you to programatically construct selection criteria. It is created through the factory method expr() of the query builder. The return value is accepted by where.

andX (and eXpression)

Join two or more expressions with an AND constraint.

<?php

$qb->expr()->andX(
    $qb->expr()->eq('tag', 'dogs'),
    $qb->expr()->eq('owner', 'daniel')
);

orX (or eXpression)

Join two or more expressions with an OR constraint.

<?php

$qb->expr()->andX(
    $qb->expr()->eq('tag', 'dogs'),
    $qb->expr()->eq('tag', 'cats')
);

eq (equal)

Specify that the value of the given field name on candidate documents must be equal to the given value.

<?php

$qb->expr()->eq('tag', 'dogs');

neq (not equal)

Specify that the value of the given field name on candidate documents must not be equal to the given value.

<?php

$qb->expr()->neq('tag', 'cats');

gt (greater than)

Specify that the value of the given field name on candidate documents must be greater than the given value.

<?php

$qb->expr()->gt('number_of_logins', 50);

gte (greater than or equal)

Specify that the value of the given field name on candidate documents must be greater than or equal to the given value.

<?php

$qb->expr()->gte('number_of_logins', 50);

lt (less than)

Specify that the value of the given field name on candidate documents must be less than the given value.

<?php

$qb->expr()->lt('number_of_logins', 50);

lte (less than or equal)

Specify that the value of the given field name on candidate documents must be less than or equal to the given value.

<?php

$qb->expr()->lte('number_of_logins', 50);

like

Specify that the value of the given field name on the candidate document must match the given pattern. “%” is a wildcard.

<?php

$qb->expr()->like('name', 'cAtS'); // case insesitive will match "CATS" and "cats"
$qb->expr()->like('name', '%og'); // will match "dog" but not "doggy"
$qb->expr()->like('name', '%og%'); // will match "dog" and "dogs"
$qb->expr()->like('name', 'dog%'); // will match "dog" and "dogs" but not "the dog"

descendant

Specify that candidate documents must be descendants of the ancestor at the given path.

<?php

$qb->expr()->descendant('/blog/posts');

textSearch

Perform a text search - perform a full text search on the specified field.

See the JCR reference for more information about query syntax.

Search on all document types where body fields are equal to dog:

<?php

$qb = // new query builder
$qb->expr()->textSearch('body', 'dog');

Search on all document types where any field contains the word “computer”:

<?php

$qb = // new query builder
$qb->expr()->textSearch(null, '*computer*');

The PHPCR QueryBuilder

The PHPCR QueryBuilder is a lower level and more verbose query builder available in the PHPCR Utils package and is not part of the ODM package, as such we will not document it extensively here and it is recommended that you use the ODM query builder.

This query builder does not know about the ODM layer, which means that it produces PHPCR queries and not ODM queries. To hydrate Documents from the results of a PHPCR query you need to use the getDocumentsByPhpcrQuery method of the document manager.

See the PHPCR Documentation for more information.

Examples

This query is equivalent to the JCR-SQL2 query SELECT * FROM nt:unstructured WHERE name NOT IS NULL

<?php

/** @var $qb QueryBuilder */
$qb = $dm->getPhpcrQueryBuilder();
$factory = $qb->getQOMFactory();
$qb->from($factory->selector('nt:unstructured'))
    ->where($factory->propertyExistence('name'))
    ->execute();

$result = $documentManager->getDocumentsByPhpcrQuery($qb->getQuery());
foreach ($result as $document) {
    echo $document->getId();
}

The maximum number of results (limit) can be set with the setMaxResults method. Furthermore the position of the first result to be retrieved (offset) can be set with setFirstResult

<?php

/** @var $qb QueryBuilder */
$factory = $qb->getQOMFactory();
$qb->from($factory->selector('nt:unstructured'))
    ->where($factory->propertyExistence('name'))
    ->setFirstResult(5)
    ->setMaxResults(10)
    ->execute();

Getting all descendant nodes of /dms is as simple as adding a descendant node constraint:

<?php

/** @var $qb QueryBuilder */
$factory = $qb->getQOMFactory();
$qb->from($factory->selector('nt:unstructured'))
    ->where($factory->descendantNode('/dms'))
    ->execute();

Note that if you just need the direct children of a document, you should use the @Children annotation on the document.

If you want to know the SQL2 statement generated call getStatement() on the query object.

<?php
//Prepare the query builder with the desired statement.
//..
echo $qb->getQuery()->getStatement();