Advanced Queries

The Criteria API and NQL are your tools for formulating database queries based on entities and their properties. They offer you the same possibilities as plain SQL, which is why you can normally do without native SQL queries.

Criteria API and CriteriaItem​

With the Criteria API, comparisons (e.g. in the WHERE, HAVING or ON section) are described via n2n\persistence\orm\criteria\compare\CriteriaComparator. You get an instance of this type, for example, as the return value of Criteria::where() or Criteria::join().

Comparison methods like CriteriaComparator::match() expect the name of the property as the first parameter and the comparison operator as the second parameter. If you use CriteriaComparator to describe a WHERE and HAVING section, CriteriaComparator::match() expects the value to compare the property with as the last parameter. If, on the other hand, you describe an ON section, it expects the name of the property to be compared with the first property as the last parameter.

$criteria = $this->em->createCriteria();

$criteria->select('a')
    ->from(Article::getClass(), 'a')
    ->where()->match('a.title', '=', 'Holeradio');

To compare two properties with each other in a WHERE or HAVING section, you must specify the parameter types more precisely. You achieve this by passing objects of type n2n\persistence\orm\criteria\item\CriteriaItem to CriteriaComparator::match(). You can generate these via n2n\persistence\orm\criteria\item\CrIt.

The following CriteriaItem types exist:

If we want to compare the properties Article::$title and Article::$text with each other in the WHERE section using CriteriaComparator::match(), we must generate a CriteriaItem at least for the third parameter.

$criteria->select('a')
    ->from(Article::getClass(), 'a')
    ->where()->match('a.title', '=', CrIt::p('a.text'));

You can also pass a function expression to CriteriaComparator::match(), instead of a property name. The same applies to Criteria::select().

$criteria->select('COUNT(a)')
    ->from(Article::getClass(), 'a');

To describe a Criteria consistently, you can of course simply use CriteriaItem objects for all parameters. By the way, CriteriaComparator::endClause() allows you to end the WHERE section and continue with describing the Criteria.

$criteria->select(CrIt::f('COUNT', 'a'))
    ->from(Article::getClass(), 'a')
    ->where()->match(CrIt::p('a.title'), '=', CrIt::c('holeradio'))->endClause()
    ->order(CrIt::f('RAND'));

This Criteria can be described using NQL as follows:

$criteria = $this->em->createNqlCriteria(
    'SELECT COUNT(a) FROM Article a WHERE a.title = :title ORDER BY RAND()',
    array('title' => 'holeradio')
);

Joins​

Criteria::join() allows you to link entities with each other. This method expects the entity to be linked as the first parameter and its alias as the second. Via the third parameter you can optionally define the corresponding join type (INNER, LEFT, RIGHT). Criteria::join() returns a CriteriaComparator object, through which you can describe the associated ON section. With CriteriaComparator::endClause() you can end the ON section and continue with describing the Criteria.

$criteria = $em->createCriteria();

$criteria->select('ba')
    ->from(BlogArticle::getClass(), 'ba')
    ->join(Category::getClass(), 'cat')
        ->match('ba.title', '=', 'cat.name')
        ->endClause()
    ->order('ba.title', 'ASC');

The same query can be described using NQL as follows:

NQL
Criteria API

The Criteria API and NQL are your tools for formulating database queries based on entities and their properties. They offer you the same possibilities as plain SQL, which is why you can normally do without native SQL queries.

Criteria API and CriteriaItem​

With the Criteria API, comparisons (e.g. in the WHERE, HAVING or ON section) are described via n2n\persistence\orm\criteria\compare\CriteriaComparator. You get an instance of this type, for example, as the return value of Criteria::where() or Criteria::join().

Comparison methods like CriteriaComparator::match() expect the name of the property as the first parameter and the comparison operator as the second parameter. If you use CriteriaComparator to describe a WHERE and HAVING section, CriteriaComparator::match() expects the value to compare the property with as the last parameter. If, on the other hand, you describe an ON section (Joins), it expects the name of the property to be compared with the first property as the last parameter.

$criteria = $this->em->createCriteria();
$criteria->select('a')
    ->from(Article::getClass(), 'a')
    ->where()->match('a.title', '=', 'Holeradio');

To compare two properties with each other in a WHERE or HAVING section, you must specify the parameter types more precisely. You achieve this by passing objects of type n2n\persistence\orm\criteria\item\CriteriaItem to CriteriaComparator::match(). You can generate these via n2n\persistence\orm\criteria\item\CrIt.

The following CriteriaItem types exist:

If we want to compare the properties Article::$title and Article::$text with each other in the WHERE section using CriteriaComparator::match(), we must generate a CriteriaItem at least for the third parameter.

$criteria->select('a')
    ->from(Article::getClass(), 'a')
    ->where()->match('a.title', '=', CrIt::p('a.text'));

You can also pass a function expression to CriteriaComparator::match(), instead of a property name. The same applies to Criteria::select().

$criteria->select('COUNT(a)')
    ->from(Article::getClass(), 'a');

To describe a Criteria consistently, you can of course simply use CriteriaItem objects for all parameters. By the way, CriteriaComparator::endClause() allows you to end the WHERE section and continue with describing the Criteria.

$criteria->select(CrIt::f('COUNT', 'a'))
    ->from(Article::getClass(), 'a')
    ->where()->match(CrIt::p('a.title'), '=', CrIt::c('holeradio'))->endClause()
    ->order(CrIt::f('RAND'));

This Criteria can be described using NQL as follows:

$criteria = $this->em->createNqlCriteria(
    'SELECT COUNT(a) FROM Article a WHERE a.title = :title ORDER BY RAND()',
    array('title' => 'holeradio'));

Joins​

Criteria::join() allows you to link entities with each other. This method expects the entity to be linked as the first parameter and its alias as the second. Via the third parameter you can optionally define the corresponding join type (INNER, LEFT, RIGHT). Criteria::join() returns a CriteriaComparator object, through which you can describe the associated ON section. With CriteriaComparator::endClause() you can end the ON section and continue with describing the Criteria.

$criteria = $em->createCriteria();
$criteria->select('ba')
    ->from(BlogArticle::getClass(), 'ba')
    ->join(Category::getClass(), 'cat')
    ->match('ba.title', '=', 'cat.name')->endClause()
    ->order('ba.title', 'ASC');

The same query can be described using NQL as follows:

$criteria = $this->em->createNqlCriteria(
    'SELECT ba FROM BlogArticle ba JOIN Category cat ON ba.title = cat.name ORDER BY ba.title ASC');

Sub-Queries​

Criteria::fromCriteria() and Criteria::joinCriteria() allow you to implement sub-queries. Both expect a Criteria object as the first parameter, which describes the sub-query. You can generate this via Criteria::subCriteria(). Otherwise, Criteria::fromCriteria() and Criteria::joinCriteria() do not differ from Criteria::from() and Criteria::join().

$criteria = $this->em->createCriteria();
$criteria->select('ba')->select('lc.numComments')
    ->from(BlogArticle::getClass(), 'ba')
    ->joinCriteria($criteria->subCriteria()
        ->select('COUNT(c)', 'numComments')
        ->select('c.blogArticle', 'blogArticle')
        ->from(Comment::getClass(), 'c')
        ->group('c.blogArticle'), 'lc', 'LEFT')
    ->match('lc.blogArticle', '=', 'ba')->endClause()
    ->order('ba.id', 'DESC');

The query above returns all articles and their number of comments.

Describing sub-queries with the Criteria API can quickly make the query confusing. Therefore, it is recommended to use NQL in such situations.

$criteria = $this->em->createNqlCriteria(
    'SELECT ba, lc.numComments
     FROM BlogArticle ba
     LEFT JOIN (
         SELECT COUNT(c.id) AS numComments, c.blogArticle AS blogArticle
         FROM Comment c
         GROUP BY c.blogArticle) lc ON lc.blogArticle = ba
     ORDER BY ba.id DESC');

You can use sub-queries in the WHERE, HAVING or ON section as well, as you are used to from SQL.

$deniedTitle = 'Test';

$criteria = $em->createCriteria();
$criteria->select('c')
    ->from(Comment::getClass(), 'c')
    ->where()->match('c.blogArticle', 'NOT IN', $criteria->subCriteria()
        ->select('ba')
        ->from(BlogArticle::getClass(), 'ba')
        ->where()->match('ba.title', '=', $deniedTitle)->endWhere());

$deniedTitle = 'Test';

$criteria = $em->createNqlCriteria(
    'SELECT c FROM Comment c WHERE c.blogArticle NOT IN (
        SELECT ba FROM BlogArticle ba WHERE ba.title = :deniedTitle)',
    array('deniedTitle' => $deniedTitle));

NQL
Criteria API

The Criteria API and NQL are your tools for formulating database queries based on entities and their properties. They offer you the same possibilities as plain SQL, which is why you can normally do without native SQL queries.

Criteria API and CriteriaItem​

With the Criteria API, comparisons (e.g. in the WHERE, HAVING or ON section) are described via n2n\persistence\orm\criteria\compare\CriteriaComparator. You get an instance of this type, for example, as the return value of Criteria::where() or Criteria::join().

Comparison methods like CriteriaComparator::match() expect the name of the property as the first parameter and the comparison operator as the second parameter. If you use CriteriaComparator to describe a WHERE and HAVING section, CriteriaComparator::match() expects the value to compare the property with as the last parameter. If, on the other hand, you describe an ON section (see Joins), it expects the name of the property to be compared with the first property as the last parameter.

$criteria = $this->em->createCriteria();

$criteria->select('a')
        ->from(Article::getClass(), 'a')
        ->where()->match('a.title', '=', 'Holeradio');

To compare two properties with each other in a WHERE or HAVING section, you must specify the parameter types more precisely. You achieve this by passing objects of type n2n\persistence\orm\criteria\item\CriteriaItem to CriteriaComparator::match(). You can generate these via n2n\persistence\orm\criteria\item\CrIt.

The following CriteriaItem types exist:

If we want to compare the properties Article::$title and Article::$text with each other in the WHERE section using CriteriaComparator::match(), we must generate a CriteriaItem at least for the third parameter.

$criteria
        ->select('a')
        ->from(Article::getClass(), 'a')
        ->where()->match('a.title', '=', CrIt::p('a.text'));

You can also pass a function expression to CriteriaComparator::match(), instead of a property name. The same applies to Criteria::select().

$criteria->select('COUNT(a)')->from(Article::getClass(), 'a');

To describe a Criteria consistently, you can of course simply use CriteriaItem objects for all parameters. By the way, CriteriaComparator::endClause() allows you to end the WHERE section and continue with describing the Criteria.

$criteria->select(CrIt::f('COUNT', 'a'))
        ->from(Article::getClass(), 'a')
        ->where()->match(CrIt::p('a.title'), '=', CrIt::c('holeradio'))->endClause()
        ->order(CrIt::f('RAND'));

This Criteria can be described using NQL as follows:

$criteria = $this->em->createNqlCriteria(
        'SELECT COUNT(a) FROM Article a WHERE a.title = :title ORDER BY RAND()',
        array('title' => 'holeradio'));

Joins​

Criteria::join() allows you to link entities with each other. This method expects the entity to be linked as the first parameter and its alias as the second. Via the third parameter you can optionally define the corresponding join type (INNER, LEFT, RIGHT). Criteria::join() returns a CriteriaComparator object, through which you can describe the associated ON section. With CriteriaComparator::endClause() you can end the ON section and continue with describing the Criteria.

$criteria = $em->createCriteria();

$criteria->select('ba')
        ->from(BlogArticle::getClass(), 'ba')
        ->join(Category::getClass(), 'cat')->match('ba.title', '=', 'cat.name')->endClause()
        ->order('ba.title', 'ASC');

The same query can be described using NQL as follows:

$criteria = $this->em->createNqlCriteria(
        'SELECT ba FROM BlogArticle ba JOIN Category cat ON ba.title = cat.name ORDER BY ba.title ASC');

Sub-Queries​

Criteria::fromCriteria() and Criteria::joinCriteria() allow you to implement sub-queries. Both expect a Criteria object as the first parameter, which describes the sub-query. You can generate this via Criteria::subCriteria(). Otherwise, Criteria::fromCriteria() and Criteria::joinCriteria() do not differ from Criteria::from() and Criteria::join().

$criteria = $this->em->createCriteria();

$criteria->select('ba')->select('lc.numComments')
        ->from(BlogArticle::getClass(), 'ba')
        ->joinCriteria($criteria->subCriteria()
                ->select('COUNT(c)', 'numComments')->select('c.blogArticle', 'blogArticle')
                ->from(Comment::getClass(), 'c')
                ->group('c.blogArticle'), 'lc', 'LEFT')->match('lc.blogArticle', '=', 'ba')->endClause()
        ->order('ba.id', 'DESC');

The query above returns all articles and their number of comments.

Describing sub-queries with the Criteria API can quickly make the query confusing. Therefore, it is recommended to use NQL in such situations.

$criteria = $this->em->createNqlCriteria(
        'SELECT ba, lc.numComments
        FROM BlogArticle ba
        LEFT JOIN (
            SELECT COUNT(c.id) AS numComments, c.blogArticle AS blogArticle
            FROM Comment c
            GROUP BY c.blogArticle) lc ON lc.blogArticle = ba
        ORDER BY ba.id DESC');

You can use sub-queries in the WHERE, HAVING or ON section as well, as you are used to from SQL.

$deniedTitle = 'Test';

$criteria = $em->createNqlCriteria(
        'SELECT c FROM Comment c WHERE c.blogArticle NOT IN (
            SELECT ba FROM BlogArticle ba WHERE ba.title = :deniedTitle)',
        array('deniedTitle' => $deniedTitle));

EXISTS and NOT EXISTS​

If you want to fall back on EXISTS and NOT EXISTS conditions in the WHERE, HAVING or ON section, you can describe them using the CriteriaComparator::test() method.

$deniedTitle = 'Test';

$criteria = $this->em->createCriteria();

$criteria->select('c')
        ->from(Comment::getClass(), 'c')
        ->where()->test('NOT EXISTS', $criteria->subCriteria()
                ->select('ba')
                ->from(BlogArticle::getClass(), 'ba')
                ->where()->match('ba.title', '=', $deniedTitle)
                ->andMatch('c.blogArticle', '=', CrIt::p('ba')))
        ->endClause();

EXISTS and NOT EXISTS conditions can of course also be integrated into NQL statements.

$deniedTitle = 'Test';

$criteria = $em->createNqlCriteria(
        'SELECT c FROM Comment c WHERE NOT EXISTS (
            SELECT ba FROM BlogArticle ba WHERE ba.title = :deniedTitle AND c.blogArticle = ba)',
        array('deniedTitle' => $deniedTitle));

return $criteria->toQuery()->fetchArray();

Placeholders​

Placeholders are available with n2n version 7.1.5 and are not yet documented.

$criteria = $this->em->createSimpleCriteria(Article::getClass(),
        array('type' => new CriteriaPlaceholder('type')));

$query->setParameter('type', 'news');

$newsArticles = $query->fetchArray();

$query->setParameter('type', 'event');

$eventArticles = $query->fetchArray();

$criteria = $this->em->createNqlCriteria('SELECT a FROM Article a WHERE a.type = :type');

$query = $criteria->toQuery();

$query->setParameter('type', 'news');

$newsArticles = $query->fetchArray();

$query->setParameter('type', 'event');

$eventArticles = $query->fetchArray();

IS NULL = null​

As long as you do without placeholders, ="null" conditions are automatically converted into IS NULL.

Criteria API

$criteria = $this->em->createSimpleCriteria(Article::getClass(),
        array('type' => null));

NQL

$criteria = $this->em->createNqlCriteria(
        'SELECT a FROM Article WHERE type = :type',
        array('type' => null));

If you use placeholders, you have to take care of this yourself, since the parameter values only become known after compiling the query (Criteria::toQuery()).

Criteria API

$criteria = $this->em->createCriteria();

$criteria->select('a')
        ->from(Article::getClass(), 'a')
        ->where()
        ->match('type', 'IS', new CriteriaPlaceholder('type'));

$criteria->setParameter('type', null);

NQL

$criteria = $this->em->createNqlCriteria(
        'SELECT a FROM Article WHERE type IS :type');

$criteria->setParameter('type', null);