DQL: Doctrine Query Language

Introduction

Doctrine Query Language (DQL) is an Object Query Language created for helping users in complex object retrieval. You should always consider using DQL (or raw SQL) when retrieving relational data efficiently (eg. when fetching users and their phonenumbers).

In this chapter we will execute dozens of examples of how to use the Doctrine Query Language. All of these examples assume you are using the schemas defined in the previous chapters, primarily the Defining Models chapter. We will define one additional model for our testing purposes:

// models/Account.php
class Account extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('name', 'string', 255);
        $this->hasColumn('amount', 'decimal');
    }
}

Here is the same example in YAML format. You can read more about YAML in the YAML Schema Files chapter:

# schema.yml
Account:
  columns:
    name: string(255)
    amount: decimal

When compared to using raw SQL, DQL has several benefits:

  • From the start it has been designed to retrieve records(objects) not result set rows
  • DQL understands relations so you don’t have to type manually SQL joins and join conditions
  • DQL is portable on different databases
  • DQL has some very complex built-in algorithms like (the record limit algorithm) which can help the developer to efficiently retrieve objects
  • It supports some functions that can save time when dealing with one-to-many, many-to-many relational data with conditional fetching.

If the power of DQL isn’t enough, you should consider using the RawSql API for object population.

You may already be familiar with the following syntax:

Caution

DO NOT USE THE FOLLOWING CODE. It uses many SQL queries for object population.

// test.php
$users = Doctrine_Core::getTable('User')->findAll();

foreach($users as $user) {
    echo $user->username . " has phonenumbers: ";

    foreach($user->Phonenumbers as $phonenumber) {
        echo $phonenumber->phonenumber . "\n";
    }
}

Tip

Here is the same code but implemented more efficiently using only one SQL query for object population.

$q = Doctrine_Query::create()
        ->from('User u')
        ->leftJoin('u.Phonenumbers p');

echo $q->getSqlQuery();

Lets take a look at the SQL that would be generated by the above query:

SELECT
    u.id AS u__id,
    u.is_active AS u__is_active,
    u.is_super_admin AS u__is_super_admin,
    u.first_name AS u__first_name,
    u.last_name AS u__last_name,
    u.username AS u__username,
    u.password AS u__password,
    u.type AS u__type,
    u.created_at AS u__created_at,
    u.updated_at AS u__updated_at,
    p.id AS p__id,
    p.user_id AS p__user_id,
    p.phonenumber AS p__phonenumber
FROM user u
    LEFT JOIN phonenumber p
        ON u.id = p.user_id

Now lets execute the query and play with the data:

$users = $q->execute();

foreach($users as $user) {
    echo $user->username . " has phonenumbers: ";

    foreach($user->Phonenumbers as $phonenumber) {
        echo $phonenumber->phonenumber . "\n";
    }
}

Caution

Using double quotes (”) in DQL strings is discouraged. This is sensible in MySQL standard but in DQL it can be confused as an identifier. Instead it is recommended to use prepared statements for your values and it will be escaped properly.

SELECT queries

SELECT statement syntax:

SELECT
    [ALL | DISTINCT]
    <select_expr>, ...
    [FROM <components>
    [WHERE <where_condition>]
    [GROUP BY <groupby_expr>
      [ASC | DESC], ... ]
    [HAVING <where_condition>]
    [ORDER BY <orderby_expr>
      [ASC | DESC], ...]
    [LIMIT <row_count> OFFSET <offset>]

The SELECT statement is used for the retrieval of data from one or more components.

Each select_expr indicates a column or an aggregate function value that you want to retrieve. There must be at least one select_expr in every SELECT statement.

First insert a few sample Account records:

// test.php
$account = new Account();
$account->name = 'test 1';

$account->amount = '100.00';
$account->save();

$account = new Account();
$account->name = 'test 2';
$account->amount = '200.00';
$account->save();

Be sure to execute test.php:

php test.php

Now you can test the selecting of the data with these next few sample queries:

$q = Doctrine_Query::create()
        ->select('a.name')
        ->from('Account a');

echo $q->getSqlQuery();

Lets take a look at the SQL that would be generated by the above query:

SELECT a.id AS a__id, a.name AS a__name FROM account a
// ...
$accounts = $q->execute();
print_r($accounts->toArray());

The above example would produce the following output:

$ php test.php
Array (
    [0] => Array
        (
            [id] => 1
            [name] => test 1
            [amount] =>
        )
    [1] => Array
        (
            [id] => 2
            [name] => test 2
            [amount] =>
        )
)

An asterisk can be used for selecting all columns from given component. Even when using an asterisk the executed SQL queries never actually use it (Doctrine converts asterisk to appropriate column names, hence leading to better performance on some databases).

$q = Doctrine_Query::create()
        ->select('a.*')
        ->from('Account a');

echo $q->getSqlQuery();

Compare the generated SQL from the last query example to the SQL generated by the query right above:

SELECT
    a.id AS a__id,
    a.name AS a__name,
    a.amount AS a__amount
FROM account a

Note

Notice how the asterisk is replace by all the real column names that exist in the Account model.

Now lets execute the query and inspect the results:

$accounts = $q->execute();
print_r($accounts->toArray());

The above example would produce the following output:

$ php test.php
Array (
    [0] => Array
        (
            [id] => 1
            [name] => test 1
            [amount] => 100.00
        )
    [1] => Array
        (
            [id] => 2
            [name] => test 2
            [amount] => 200.00
        )
)

FROM clause components indicate the component or components from which to retrieve records.

$q = Doctrine_Query::create()
        ->select('u.username, p.*')
        ->from('User u')
        ->leftJoin('u.Phonenumbers p')

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT
    u.id AS u__id,
    u.username AS u__username,
    p.id AS p__id,
    p.user_id AS p__user_id,
    p.phonenumber AS p__phonenumber
FROM user u
    LEFT JOIN phonenumber p
        ON u.id = p.user_id

The WHERE clause, if given, indicates the condition or conditions that the records must satisfy to be selected. where_condition is an expression that evaluates to true for each row to be selected. The statement selects all rows if there is no WHERE clause.

$q = Doctrine_Query::create()
        ->select('a.name')
        ->from('Account a')
        ->where('a.amount > 2000');

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT a.id AS a__id, a.name AS a__name FROM account a WHERE a.amount > 2000

In the WHERE clause, you can use any of the functions and operators that DQL supports, except for aggregate (summary) functions. The HAVING clause can be used for narrowing the results with aggregate functions:

$q = Doctrine_Query::create()
        ->select('u.username')
        ->from('User u')
        ->leftJoin('u.Phonenumbers p')
        ->having('COUNT(p.id) > 3');

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT
   u.id AS u__id,
   u.username AS u__username
FROM user u
    LEFT JOIN phonenumber p
        ON u.id = p.user_id
HAVING COUNT(p.id) > 3

The ORDER BY clause can be used for sorting the results:

$q = Doctrine_Query::create()
        ->select('u.username')
        ->from('User u')
        ->orderBy('u.username');

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT
    u.id AS u__id,
    u.username AS u__username
FROM user u
ORDER BY u.username

The LIMIT and OFFSET clauses can be used for efficiently limiting the number of records to a given row_count:

$q = Doctrine_Query::create()
        ->select('u.username')
        ->from('User u')
        ->limit(20);

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT u.id AS u__id, u.username AS u__username FROM user u LIMIT 20

Aggregate values

Aggregate value SELECT syntax:

$q = Doctrine_Query::create()
        ->select('u.id, COUNT(t.id) AS num_threads')
        ->from('User u, u.Threads t')
        ->where('u.id = ?', 1)
        ->groupBy('u.id');

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT
    u.id AS u__id,
    COUNT(f.id) AS f__0
FROM user u
    LEFT JOIN forum__thread f
        ON u.id = f.user_id
WHERE u.id = ?
GROUP BY u.id

Now execute the query and inspect the results:

$users = $q->execute();

You can easily access the num_threads data with the following code:

echo $users->num_threads . ' threads found';

UPDATE queries

UPDATE statement syntax:

UPDATE <component_name>
SET
    <col_name1> = <expr1>,
    <col_name2> = <expr2>
WHERE <where_condition>
ORDER BY <order_by>
LIMIT <record_count>
  • The UPDATE statement updates columns of existing records in component_name with new values and returns the number of affected records.
  • The SET clause indicates which columns to modify and the values they should be given.
  • The optional WHERE clause specifies the conditions that identify which records to update. Without WHERE clause, all records are updated.
  • The optional ORDER BY clause specifies the order in which the records are being updated.
  • The LIMIT clause places a limit on the number of records that can be updated. You can use LIMIT row_count to restrict the scope of the UPDATE. A LIMIT clause is a rows-matched restriction not a rows-changed restriction. The statement stops as soon as it has found record_count rows that satisfy the WHERE clause, whether or not they actually were changed.
$q = Doctrine_Query::create()
        ->update('Account')
        ->set('amount', 'amount + 200')
        ->where('id > 200');

// If you just want to set the amount to a value
//   $q->set('amount', '?', 500);

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

UPDATE account SET amount = amount + 200 WHERE id > 200

Now to perform the update is simple. Just execute the query:

$rows = $q->execute();

echo $rows;

DELETE Queries

DELETE FROM <component_name>
WHERE <where_condition>
ORDER BY <order_by>
LIMIT <record_count>
  • The DELETE statement deletes records from component_name and returns the number of records deleted.
  • The optional WHERE clause specifies the conditions that identify which records to delete. Without WHERE clause, all records are deleted.
  • If the ORDER BY clause is specified, the records are deleted in the order that is specified.
  • The LIMIT clause places a limit on the number of rows that can be deleted. The statement will stop as soon as it has deleted record_count records.
$q = Doctrine_Query::create()
        ->delete('Account a')
        ->where('a.id > 3');

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

DELETE FROM account WHERE id > 3

Now executing the DELETE query is just as you would think:

$rows = $q->execute();

echo $rows;

Note

When executing DQL UPDATE and DELETE queries the executing of a query returns the number of affected rows.

FROM clause

Syntax:

FROM <component_reference>
    [[LEFT | INNER] JOIN <component_reference>] ...

The FROM clause indicates the component or components from which to retrieve records. If you name more than one component, you are performing a join. For each table specified, you can optionally specify an alias.

Consider the following DQL query:

$q = Doctrine_Query::create()
        ->select('u.id')
        ->from('User u');

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT u.id AS u__id FROM user u

Here User is the name of the class (component) and u is the alias. You should always use short aliases, since most of the time those make the query much shorther and also because when using for example caching the cached form of the query takes less space when short aliases are being used.

JOIN syntax

DQL JOIN Syntax:

[[LEFT | INNER] JOIN <component_reference1>] [ON | WITH] <join_condition1> [INDEXBY] <map_condition1>,
[[LEFT | INNER] JOIN <component_reference2>] [ON | WITH] <join_condition2> [INDEXBY] <map_condition2>,
...
[[LEFT | INNER] JOIN <component_referenceN>] [ON | WITH] <join_conditionN> [INDEXBY] <map_conditionN>

DQL supports two kinds of joins INNER JOINs and LEFT JOINs. For each joined component, you can optionally specify an alias.

The default join type is LEFT JOIN. This join can be indicated by the use of either LEFT JOIN clause or simply ‘,‘, hence the following queries are equal:

$q = Doctrine_Query::create()
        ->select('u.id, p.id')
        ->from('User u')
        ->leftJoin('u.Phonenumbers p');

$q = Doctrine_Query::create()
        ->select('u.id, p.id')
        ->from('User u, u.Phonenumbers p');

echo $q->getSqlQuery();

Tip

The recommended form is the first because it is more verbose and easier to read and understand what is being done.

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT
u.id AS u__id,
    p.id AS p__id
FROM user u
    LEFT JOIN phonenumber p
ON u.id = p.user_id

Note

Notice how the JOIN condition is automatically added for you. This is because Doctrine knows how User and Phonenumber are related so it is able to add it for you.

INNER JOIN produces an intersection between two specified components (that is, each and every record in the first component is joined to each and every record in the second component). So basically INNER JOIN can be used when you want to efficiently fetch for example all users which have one or more phonenumbers.

By default DQL auto-adds the primary key join condition:

$q = Doctrine_Query::create()
        ->select('u.id, p.id')
        ->from('User u')
        ->leftJoin('u.Phonenumbers p');

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT
    u.id AS u__id,
    p.id AS p__id
FROM User u
    LEFT JOIN Phonenumbers p ON u.id = p.user_id

ON keyword

If you want to override this behavior and add your own custom join condition you can do it with the ON keyword. Consider the following DQL query:

$q = Doctrine_Query::create()
        ->select('u.id, p.id')
        ->from('User u')
        ->leftJoin('u.Phonenumbers p ON u.id = 2');

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT
    u.id AS u__id,
    p.id AS p__id
FROM User u
    LEFT JOIN Phonenumbers p ON u.id = 2

Note

Notice how the ON condition that would be normally automatically added is not present and the user specified condition is used instead.

WITH keyword

Most of the time you don’t need to override the primary join condition, rather you may want to add some custom conditions. This can be achieved with the WITH keyword.

$q = Doctrine_Query::create()
        ->select('u.id, p.id')
        ->from('User u')
        ->leftJoin('u.Phonenumbers p WITH u.id = 2');

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT
    u.id AS u__id,
    p.id AS p__id
FROM User u
    LEFT JOIN Phonenumbers p
        ON u.id = p.user_id AND u.id = 2

Note

Notice how the ON condition isn’t completely replaced. Instead the conditions you specify are appended on to the automatic condition that is added for you.

The Doctrine_Query API offers two convenience methods for adding JOINS. These are called :php:meth:`innerJoin` and :php:meth:`leftJoin`, which usage should be quite intuitive as shown below:

$q = Doctrine_Query::create()
        ->select('u.id')
        ->from('User u')
        ->leftJoin('u.Groups g')
        ->innerJoin('u.Phonenumbers p WITH u.id > 3')
        ->leftJoin('u.Email e');

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT
    u.id AS u__id
FROM user u
    LEFT JOIN user_group u2
        ON u.id = u2.user_id
    LEFT JOIN groups g
        ON g.id = u2.group_id
    INNER JOIN phonenumber p
        ON u.id = p.user_id AND u.id > 3
    LEFT JOIN email e
        ON u.id = e.user_id

INDEXBY keyword

The INDEXBY keyword offers a way of mapping certain columns as collection / array keys. By default Doctrine indexes multiple elements to numerically indexed arrays / collections. The mapping starts from zero. In order to override this behavior you need to use INDEXBY keyword as shown above:

$q = Doctrine_Query::create()
        ->from('User u INDEXBY u.username');

$users = $q->execute();

Note

The INDEXBY keyword does not alter the generated SQL. It is simply used internally by :php:class:`Doctrine_Query` to hydrate the data with the specified column as the key of each record in the collection.

Now the users in $users collection are accessible through their names:

echo $user['jack daniels']->id;

The INDEXBY keyword can be applied to any given JOIN. This means that any given component can have each own indexing behavior. In the following we use distinct indexing for both Users and Groups.

$q = Doctrine_Query::create()
        ->from('User u INDEXBY u.username')
        ->innerJoin('u.Groups g INDEXBY g.name');

$users = $q->execute();

Now lets print out the drinkers club’s creation date.

echo $users['jack daniels']->Groups['drinkers club']->createdAt;

WHERE clause

Syntax:

WHERE <where_condition>
  • The WHERE clause, if given, indicates the condition or conditions that the records must satisfy to be selected.
  • where_condition is an expression that evaluates to true for each row to be selected.
  • The statement selects all rows if there is no WHERE clause.
  • When narrowing results with aggregate function values HAVING clause should be used instead of WHERE clause

You can use the :php:meth:`addWhere`, :php:meth:`andWhere`, :php:meth:`orWhere`, :php:meth:`whereIn`, :php:meth:`andWhereIn`, :php:meth:`orWhereIn`, :php:meth:`whereNotIn`, :php:meth:`andWhereNotIn`, :php:meth:`orWhereNotIn` functions for building complex where conditions using :php:class:`Doctrine_Query` objects.

Here is an example where we retrieve all active registered users or super administrators:

$q = Doctrine_Query::create()
        ->select('u.id')
        ->from('User u')
        ->where('u.type = ?', 'registered')
        ->andWhere('u.is_active = ?', 1)
        ->orWhere('u.is_super_admin = ?', 1);

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT
    u.id AS u__id
FROM user u
WHERE
    u.type = ?
    AND u.is_active = ?
    OR u.is_super_admin = ?

Conditional expressions

Literals

Strings

A string literal that includes a single quote is represented by two single quotes; for example: ´´literal’’s´´.

$q = Doctrine_Query::create()
        ->select('u.id, u.username')
        ->from('User u')
        ->where('u.username = ?', 'Vincent');

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT
    u.id AS u__id,
    u.username AS u__username
FROM user u
WHERE
    u.username = ?

Note

Because we passed the value of the username as a parameter to the :php:meth:`where` method it is not included in the generated SQL. PDO handles the replacement when you execute the query. To check the parameters that exist on a :php:class:`Doctrine_Query` instance you can use the :php:meth:`getParams` method.

Integers

Integer literals support the use of PHP integer literal syntax.

$q = Doctrine_Query::create()
        ->select('a.id')
        ->from('User u')
        ->where('u.id = 4');

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT u.id AS u__id FROM user u WHERE u.id = 4

Floats

Float literals support the use of PHP float literal syntax.

$q = Doctrine_Query::create()
        ->select('a.id')
        ->from('Account a')
        ->where('a.amount = 432.123');

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT a.id AS a__id FROM account a WHERE a.amount = 432.123

Booleans

The boolean literals are true and false.

$q = Doctrine_Query::create()
        ->select('a.id')
        ->from('User u')
        ->where('u.is_super_admin = true');

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT u.id AS u__id FROM user u WHERE u.is_super_admin = 1

Enums

The enumerated values work in the same way as string literals.

$q = Doctrine_Query::create()
        ->select('a.id')
        ->from('User u')
        ->where("u.type = 'admin'");

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT u.id AS u__id FROM user u WHERE u.type = 'admin'

Predefined reserved literals are case insensitive, although its a good standard to write them in uppercase.

Input parameters

Here are some examples of using positional parameters:

  • Single positional parameter:

    $q = Doctrine_Query::create()
            ->select('u.id')
            ->from('User u')
            ->where('u.username = ?', array('Arnold'));
    
    echo $q->getSqlQuery();
    

    Note

    When the passed parameter for a positional parameter contains only one value you can simply pass a single scalar value instead of an array containing one value.

    The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

    SELECT
        u.id AS u__id
    FROM user u
    WHERE u.username = ?
    
  • Multiple positional parameters:

    $q = Doctrine_Query::create()
            ->from('User u')
            ->where('u.id > ? AND u.username LIKE ?', array(50, 'A%'));
    
    echo $q->getSqlQuery();
    

    The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

    SELECT
        u.id AS u__id
    FROM user u
    WHERE (
        u.id > ?
        AND u.username LIKE ?
    )
    

Here are some examples of using named parameters:

  • Single named parameter:

    $q = Doctrine_Query::create()
            ->select('u.id')
            ->from('User u')
            ->where('u.username = :name', array(':name' => 'Arnold'));
    
    echo $q->getSqlQuery();
    

    The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

    SELECT
        u.id AS u__id
    FROM user u
    WHERE
        u.username = :name
    
  • Named parameter with a LIKE statement:

    $q = Doctrine_Query::create()
            ->select('u.id')
            ->from('User u')
            ->where('u.id > :id', array(':id' => 50))
            ->andWhere('u.username LIKE :name', array(':name' => 'A%'));
    
    echo $q->getSqlQuery();
    

    The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

    SELECT
        u.id AS u__id
    FROM user u
    WHERE
        u.id > :id
        AND u.username LIKE :name
    

Operators and operator precedence

The operators are listed below in order of decreasing precedence.

Operator Description
. Navigation operator
Arithmetic operators:
+, - Unary
*, / Multiplication and division
+, - Addition and subtraction
Comparison operators:
=, >, >=, <, <=, <> (not equal)  
[NOT] LIKE, [NOT] IN, IS [NOT] NULL, IS [NOT] EMPTY  
Logical operators:
NOT, AND, OR,  

In expressions

Syntax:

<operand> IN (<subquery>|<value list>)

An IN conditional expression returns true if the operand is found from result of the subquery or if its in the specificied comma separated value list, hence the IN expression is always false if the result of the subquery is empty.

When value list is being used there must be at least one element in that list.

Here is an example where we use a subquery for the IN:

$q = Doctrine_Query::create()
        ->from('User u')
        ->where('u.id IN (SELECT u.id FROM User u INNER JOIN u.Groups g WHERE g.id = ?)', 1);

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT
    u.id AS u__id
FROM user u
WHERE u.id IN (
    SELECT
        u2.id AS u2__id
    FROM user u2
    INNER JOIN user_group u3
        ON u2.id = u3.user_id
    INNER JOIN groups g
        ON g.id = u3.group_id
    WHERE g.id = ?
)

Here is an example where we just use a list of integers:

$q = Doctrine_Query::create()
        ->select('u.id')
        ->from('User u')
        ->whereIn('u.id', array(1, 3, 4, 5));

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT u.id AS u__id FROM user u WHERE u.id IN (?, ?, ?, ?)

Like Expressions

Syntax:

string_expression [NOT] LIKE pattern_value [ESCAPE escape_character]

The string_expression must have a string value. The pattern_value is a string literal or a string-valued input parameter in which an underscore (_) stands for any single character, a percent (%) character stands for any sequence of characters (including the empty sequence), and all other characters stand for themselves. The optional escape_character is a single-character string literal or a character-valued input parameter (i.e., char or Character) and is used to escape the special meaning of the underscore and percent characters in pattern_value.

Examples:

  • address.phone LIKE ‘12%3’ is true for ‘123’ ‘12993’ and false for ‘1234’
  • asentence.word LIKE ‘l_se’ is true for ‘lose’ and false for ‘loose’
  • aword.underscored LIKE ‘_%’ ESCAPE ‘’ is true for ‘_foo’ and false for ‘bar’
  • address.phone NOT LIKE ‘12%3’ is false for ‘123’ and ‘12993’ and true for ‘1234’

If the value of the string_expression or pattern_value is NULL or unknown, the value of the LIKE expression is unknown. If the escape_characteris specified and is NULL, the value of the LIKE expression is unknown.

  • Find all users whose email ends with '@gmail.com‘:

    $q = Doctrine_Query::create()
            ->select('u.id')
            ->from('User u')
            ->leftJoin('u.Email e')
            ->where('e.address LIKE ?', '%@gmail.com');
    
    echo $q->getSqlQuery();
    

    The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

    SELECT
        u.id AS u__id
    FROM user u
        LEFT JOIN email e ON u.id = e.user_id
    WHERE
        e.address LIKE ?
    
  • Find all users whose name starts with letter ‘A’:

    $q = Doctrine_Query::create()
            ->select('u.id')
            ->from('User u')
            ->where('u.username LIKE ?', 'A%');
    
    echo $q->getSqlQuery();
    

    The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

    SELECT
        u.id AS u__id
    FROM user u
    WHERE
        u.username LIKE ?
    

Exists Expressions

Syntax:

[NOT] EXISTS (<subquery>)

The EXISTS operator returns TRUE if the subquery returns one or more rows and FALSE otherwise.

The NOT EXISTS operator returns TRUE if the subquery returns 0 rows and FALSE otherwise.

Note

For the next few examples we need to add the ReaderLog model.

// models/ReaderLog.php
class ReaderLog extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('article_id', 'integer', null, array(
            'primary' => true
        ));
        $this->hasColumn('user_id', 'integer', null, array(
            'primary' => true
        ));
    }
}

Here is the same example in YAML format. You can read more about YAML in the YAML Schema Files chapter:

# schema.yml
ReaderLog:
  columns:
    article_id:
      type: integer
      primary: true
    user_id:
      type: integer
      primary: true

Note

After adding the ReaderLog model don’t forget to run the generate.php script!

php generate.php

Now we can run some tests! First, finding all articles which have readers:

$q = Doctrine_Query::create()
        ->select('a.id')
        ->from('Article a')
        ->where('EXISTS (SELECT r.id FROM ReaderLog r WHERE r.article_id = a.id)');

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT
    a.id AS a__id
FROM article a
WHERE
    EXISTS (
        SELECT
            r.id AS r__id
        FROM reader_log r
        WHERE
            r.article_id = a.id
    )

Finding all articles which don’t have readers:

$q = Doctrine_Query::create()
        ->select('a.id')
        ->from('Article a')
        ->where('NOT EXISTS (SELECT r.id FROM ReaderLog r WHERE r.article_id = a.id)');

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT
    a.id AS a__id
FROM article a
WHERE
    NOT EXISTS (
        SELECT
            r.id AS r__id
        FROM reader_log r
        WHERE
            r.article_id = a.id
    )

All and Any Expressions

Syntax:

operand comparison_operator ANY (subquery)
operand comparison_operator SOME (subquery)
operand comparison_operator ALL (subquery)

An ALL conditional expression returns true if the comparison operation is true for all values in the result of the subquery or the result of the subquery is empty. An ALL conditional expression is false if the result of the comparison is false for at least one row, and is unknown if neither true nor false.

$q = Doctrine_Query::create()
       ->from('C')
       ->where('C.col1 < ALL (FROM C2(col1))');

An ANY conditional expression returns true if the comparison operation is true for some value in the result of the subquery. An ANY conditional expression is false if the result of the subquery is empty or if the comparison operation is false for every value in the result of the subquery, and is unknown if neither true nor false.

$q = Doctrine_Query::create()
        ->from('C')
        ->where('C.col1 > ANY (FROM C2(col1))');

The keyword SOME is an alias for ANY.

$q = Doctrine_Query::create()
        ->from('C')
        ->where('C.col1 > SOME (FROM C2(col1))');

The comparison operators that can be used with ALL or ANY conditional expressions are =, <, <=, >, >=, <>. The result of the subquery must be same type with the conditional expression.

NOT IN is an alias for <> ALL. Thus, these two statements are equal:

FROM C WHERE C.col1 <> ALL (FROM C2(col1));
FROM C WHERE C.col1 NOT IN (FROM C2(col1));
$q = Doctrine_Query::create()
        ->from('C')
        ->where('C.col1 <> ALL (FROM C2(col1))');
$q = Doctrine_Query::create()
        ->from('C')
        ->where('C.col1 NOT IN (FROM C2(col1))');

Subqueries

A subquery can contain any of the keywords or clauses that an ordinary SELECT query can contain.

Some advantages of the subqueries:

  • They allow queries that are structured so that it is possible to isolate each part of a statement.
  • They provide alternative ways to perform operations that would otherwise require complex joins and unions.
  • They are, in many people’s opinion, readable. Indeed, it was the innovation of subqueries that gave people the original idea of calling the early SQL “Structured Query Language.”

Here is an example where we find all users which don’t belong to the group id 1:

$q = Doctrine_Query::create()
        ->select('u.id')
        ->from('User u')
        ->where('u.id NOT IN (SELECT u2.id FROM User u2 INNER JOIN u2.Groups g WHERE g.id = ?)', 1);

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT
    u.id AS u__id
FROM user u
WHERE u.id NOT IN (
    SELECT u2.id AS
        u2__id
    FROM user u2
        INNER JOIN user_group u3
            ON u2.id = u3.user_id
        INNER JOIN groups g
            ON g.id = u3.group_id
    WHERE g.id = ?
)

Here is an example where we find all users which don’t belong to any groups:

$q = Doctrine_Query::create()
        ->select('u.id')
        ->from('User u')
        ->where('u.id NOT IN (SELECT u2.id FROM User u2 INNER JOIN u2.Groups g)');

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT
    u.id AS u__id
FROM user u
WHERE u.id NOT IN (
    SELECT
        u2.id AS u2__id
    FROM user u2
        INNER JOIN user_group u3
            ON u2.id = u3.user_id
        INNER JOIN groups g
            ON g.id = u3.group_id
)

Functional Expressions

String functions

The CONCAT function returns a string that is a concatenation of its arguments. In the example above we map the concatenation of users first_name and last_name to a value called name.

$q = Doctrine_Query::create()
        ->select('CONCAT(u.first_name, u.last_name) AS name')
        ->from('User u');

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT
    u.id AS u__id,
    CONCAT(u.first_name, u.last_name) AS u__0
FROM user u

Now we can execute the query and get the mapped function value:

$users = $q->execute();

foreach($users as $user) {
    // here 'name' is not a property of $user,
    // its a mapped function value echo $user->name;
}

The second and third arguments of the SUBSTRING function denote the starting position and length of the substring to be returned. These arguments are integers. The first position of a string is denoted by 1. The SUBSTRING function returns a string.

$q = Doctrine_Query::create()
        ->select('u.username')
        ->from('User u')
        ->where("SUBSTRING(u.username, 0, 1) = 'z'");

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT
    u.id AS u__id,
    u.username AS u__username
FROM user u
WHERE
    SUBSTRING(u.username FROM 0 FOR 1) = 'z'

Note

Notice how the SQL is generated with the proper SUBSTRING syntax for the DBMS you are using!

The TRIM function trims the specified character from a string. If the character to be trimmed is not specified, it is assumed to be space (or blank). The optional trim_character is a single-character string literal or a character-valued input parameter (i.e., char or Character)[30]. If a trim specification is not provided, BOTH is assumed. The TRIM function returns the trimmed string.

$q = Doctrine_Query::create()
        ->select('u.username')
        ->from('User u')
        ->where('TRIM(u.username) = ?', 'Someone');

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT
u.id AS u__id,
    u.username AS u__username
FROM user u
WHERE
TRIM(u.username) = ?

The LOWER and UPPER functions convert a string to lower and upper case, respectively. They return a string.

$q = Doctrine_Query::create()
        ->select('u.username')
        ->from('User u')
        ->where("LOWER(u.username) = 'jon wage'");

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT
    u.id AS u__id,
    u.username AS u__username
FROM user u
WHERE
    LOWER(u.username) = 'someone'

The LOCATE function returns the position of a given string within a string, starting the search at a specified position. It returns the first position at which the string was found as an integer. The first argument is the string to be located; the second argument is the string to be searched; the optional third argument is an integer that represents the string position at which the search is started (by default, the beginning of the string to be searched). The first position in a string is denoted by 1. If the string is not found, 0 is returned.

The LENGTH function returns the length of the string in characters as an integer.

Arithmetic functions

Availible DQL arithmetic functions:

  • The ABS function returns the absolute value for given number.
  • The SQRT function returns the square root for given number.
  • The MOD function returns the modulus of first argument using the second argument.

Subqueries

Introduction

Doctrine allows you to use sub-dql queries in the FROM, SELECT and WHERE statements. Below you will find examples for all the different types of subqueries Doctrine supports.

Comparisons using subqueries

Find all the users which are not in a specific group.

$q = Doctrine_Query::create()
        ->select('u.id')
        ->from('User u')
        ->where('u.id NOT IN (
                    SELECT u.id
                    FROM User u
                    INNER JOIN u.Groups g
                    WHERE g.id = ?
                )', 1);

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT
    u.id AS u__id
FROM user u
WHERE
    u.id NOT IN (
        SELECT u2.id AS
            u2__id
        FROM user u2
            INNER JOIN user_group u3
                ON u2.id = u3.user_id
            INNER JOIN groups g
                ON g.id = u3.group_id
        WHERE g.id = ?
    )

Retrieve the users phonenumber in a subquery and include it in the resultset of user information.

$q = Doctrine_Query::create()
        ->select('u.id')
        ->addSelect('(SELECT p.phonenumber
                        FROM Phonenumber p
                        WHERE p.user_id = u.id
                        LIMIT 1) as phonenumber')
        ->from('User u');

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT
    u.id AS u__id,
    (
        SELECT
            p.phonenumber AS p__phonenumber
        FROM phonenumber p
        WHERE p.user_id = u.id
        LIMIT 1
    ) AS u__0
FROM user u

GROUP BY, HAVING clauses

DQL GROUP BY syntax:

GROUP BY groupby_item {, groupby_item}*

DQL HAVING syntax:

HAVING conditional_expression

GROUP BY and HAVING clauses can be used for dealing with aggregate functions. The Following aggregate functions are available on DQL: COUNT, MAX, MIN, AVG, SUM

  • Selecting alphabetically first user by name:

    $q = Doctrine_Query::create()
            ->select('MIN(a.amount)')
            ->from('Account a');
    
    echo $q->getSqlQuery();
    

    The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

    SELECT MIN(a.amount) AS a__0 FROM account a
    
  • Selecting the sum of all Account amounts:

    $q = Doctrine_Query::create()
            ->select('SUM(a.amount)')
            ->from('Account a');
    
    echo $q->getSqlQuery();
    

    The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

    SELECT SUM(a.amount) AS a__0 FROM account a
    
  • Using an aggregate function in a statement containing no GROUP BY clause, results in grouping on all rows. In the example below we fetch all users and the number of phonenumbers they have.

    $q = Doctrine_Query::create()
            ->select('u.username')
            ->addSelect('COUNT(p.id) as num_phonenumbers')
            ->from('User u')
            ->leftJoin('u.Phonenumbers p')
            ->groupBy('u.id');
    
    echo $q->getSqlQuery();
    

    The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

    SELECT
        u.id AS u__id,
        u.username AS u__username, COUNT(p.id) AS p__0
    FROM user u
        LEFT JOIN phonenumber p
            ON u.id = p.user_id
    GROUP BY u.id
    
  • The HAVING clause can be used for narrowing the results using aggregate values. In the following example we fetch all users which have at least 2 phonenumbers:

    $q = Doctrine_Query::create()
            ->select('u.username')
            ->addSelect('COUNT(p.id) as num_phonenumbers')
            ->from('User u')
            ->leftJoin('u.Phonenumbers p')
            ->groupBy('u.id')
            ->having('num_phonenumbers >= 2');
    
    echo $q->getSqlQuery();
    

    The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

    SELECT
        u.id AS u__id,
        u.username AS u__username, COUNT(p.id) AS p__0
    FROM user u
        LEFT JOIN phonenumber p
            ON u.id = p.user_id
    GROUP BY u.id
    HAVING p__0 >= 2
    

    You can access the number of phonenumbers with the following code:

    $users = $q->execute();
    
    foreach($users as $user) {
        echo $user->name
            . ' has '
            . $user->num_phonenumbers
            . ' phonenumbers';
    }
    

ORDER BY clause

Introduction

Record collections can be sorted efficiently at the database level using the ORDER BY clause.

Syntax:

[
   ORDER BY {ComponentAlias.columnName}
   [ASC | DESC], ...
]

Examples:

$q = Doctrine_Query::create()
        ->select('u.username')
        ->from('User u')
        ->leftJoin('u.Phonenumbers p')
        ->orderBy('u.username, p.phonenumber');

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT
    u.id AS u__id,
    u.username AS u__username
FROM user u
    LEFT JOIN phonenumber p
        ON u.id = p.user_id
ORDER BY
    u.username,
    p.phonenumber

In order to sort in reverse order you can add the DESC (descending) keyword to the name of the column in the ORDER BY clause that you are sorting by. The default is ascending order; this can be specified explicitly using the ASC keyword.

$q = Doctrine_Query::create()
        ->select('u.username')
        ->from('User u')
        ->leftJoin('u.Email e')
        ->orderBy('e.address DESC, u.id ASC');

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT
    u.id AS u__id,
    u.username AS u__username
FROM user u
    LEFT JOIN email e
    ON u.id = e.user_id
ORDER BY
    e.address DESC,
    u.id ASC

Sorting by an aggregate value

In the following example we fetch all users and sort those users by the number of phonenumbers they have.

$q = Doctrine_Query::create()
        ->select('u.username, COUNT(p.id) count')
        ->from('User u')
        ->innerJoin('u.Phonenumbers p')
        ->orderby('count');

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT
    u.id AS u__id,
    u.username AS u__username,
    COUNT(p.id) AS p__0
FROM user u
    INNER JOIN phonenumber p
        ON u.id = p.user_id
ORDER BY p__0

Using random order

In the following example we use random in the ORDER BY clause in order to fetch random post.

$q = Doctrine_Query::create()
        ->select('t.id, RANDOM() AS rand')
        ->from('Forum_Thread t')
        ->orderby('rand')
        ->limit(1);

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT f.id AS f__id, RAND() AS f__0 FROM forum__thread f ORDER BY f__0 LIMIT 1

LIMIT and OFFSET clauses

Propably the most complex feature DQL parser has to offer is its LIMIT clause parser. Not only does the DQL LIMIT clause parser take care of LIMIT database portability it is capable of limiting the number of records instead of rows by using complex query analysis and subqueries.

Retrieve the first 20 users and all their associated phonenumbers:

$q = Doctrine_Query::create()
        ->select('u.username, p.phonenumber')
        ->from('User u')
        ->leftJoin('u.Phonenumbers p')
        ->limit(20);

echo $q->getSqlQuery();

Tip

You can also use the :php:meth:`offset` method of the :php:class:`Doctrine_Query` object in combination with the :php:meth:`limit` method to produce your desired LIMIT and OFFSET in the executed SQL query.

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT
    u.id AS u__id,
    u.username AS u__username,
    p.id AS p__id,
    p.phonenumber AS p__phonenumber
FROM user u
    LEFT JOIN phonenumber p
        ON u.id = p.user_id
LIMIT 20

Driver Portability

DQL LIMIT clause is portable on all supported databases. Special attention have been paid to following facts:

  • Only MySQL, PgSQL and Sqlite implement LIMIT / OFFSET clauses natively
  • In Oracle / MSSQL / Firebird LIMIT / OFFSET clauses need to be emulated in driver specific way
  • The limit-subquery-algorithm needs to execute to subquery separately in MySQL, since MySQL doesn’t yet support LIMIT clause in subqueries
  • PgSQL needs the order by fields to be preserved in SELECT clause, hence limit-subquery-algorithm needs to take this into consideration when pgSQL driver is used
  • Oracle only allows < 30 object identifiers (= table/column names/aliases), hence the limit subquery must use as short aliases as possible and it must avoid alias collisions with the main query.

The limit-subquery-algorithm

The limit-subquery-algorithm is an algorithm that DQL parser uses internally when one-to-many / many-to-many relational data is being fetched simultaneously. This kind of special algorithm is needed for the LIMIT clause to limit the number of records instead of SQL result set rows.

This behavior can be overwritten using the configuration system (at global, connection or table level) using:

$table->setAttribute(Doctrine_Core::ATTR_QUERY_LIMIT, Doctrine_Core::LIMIT_ROWS);
$table->setAttribute(Doctrine_Core::ATTR_QUERY_LIMIT, Doctrine_Core::LIMIT_RECORDS); // revert

In the following example we have users and phonenumbers with their relation being one-to-many. Now lets say we want fetch the first 20 users and all their related phonenumbers.

Now one might consider that adding a simple driver specific LIMIT 20 at the end of query would return the correct results. Thats wrong, since we you might get anything between 1-20 users as the first user might have 20 phonenumbers and then record set would consist of 20 rows.

DQL overcomes this problem with subqueries and with complex but efficient subquery analysis. In the next example we are going to fetch first 20 users and all their phonenumbers with single efficient query. Notice how the DQL parser is smart enough to use column aggregation inheritance even in the subquery and how it’s smart enough to use different aliases for the tables in the subquery to avoid alias collisions.

$q = Doctrine_Query::create()
        ->select('u.id, u.username, p.*')
        ->from('User u')
        ->leftJoin('u.Phonenumbers p')
        ->limit(20);

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT
    u.id AS u__id,
    u.username AS u__username,
    p.id AS p__id,
    p.phonenumber AS p__phonenumber,
    p.user_id AS p__user_id
FROM user u
    LEFT JOIN phonenumber p
        ON u.id = p.user_id
WHERE
    u.id IN (
        SELECT
            DISTINCT u2.id
        FROM user u2
        LIMIT 20
    )

Note

Notice how the IN clause with the subquery was added. This is so that the users are limited to 20 and the users phonenumbers are not limited.

In the next example we are going to fetch first 20 users and all their phonenumbers and only those users that actually have phonenumbers with single efficient query, hence we use an INNER JOIN. Notice how the DQL parser is smart enough to use the INNER JOIN in the subquery:

$q = Doctrine_Query::create()
        ->select('u.id, u.username, p.*')
        ->from('User u')
        ->innerJoin('u.Phonenumbers p')
        ->limit(20);

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT
    u.id AS u__id,
    u.username AS u__username,
    p.id AS p__id,
    p.phonenumber AS p__phonenumber,
    p.user_id AS p__user_id
FROM user u
    INNER JOIN phonenumber p
        ON u.id = p.user_id
WHERE
    u.id IN (
        SELECT
        DISTINCT u2.id
        FROM user u2
            INNER JOIN phonenumber p2
                ON u2.id = p2.user_id
        LIMIT 20
    )

Named Queries

When you are dealing with a model that may change, but you need to keep your queries easily updated, you need to find an easy way to define queries. Imagine for example that you change one field and you need to follow all queries in your application to make sure it’ll not break anything.

Named Queries is a nice and effective way to solve this situation, allowing you to create Doctrine_Queries and reuse them without the need to keep rewritting them.

The Named Query support is built at the top of Doctrine_Query_Registry support. Doctrine_Query_Registry is a class for registering and naming queries. It helps with the organization of your applications queries and along with that it offers some very nice convenience stuff.

The queries are added using the :php:meth:`add` method of the registry object. It takes two parameters, the query name and the actual DQL query.

$r = Doctrine_Manager::getInstance()->getQueryRegistry();
$r->add('User/all', 'FROM User u');

$userTable = Doctrine_Core::getTable('User');
// find all users $users = $userTable->find('all');

To simplify this support, :php:class:`Doctrine_Table` support some accessors to Doctrine_Query_Registry.

Creating a Named Query

When you build your models with option generateTableClasses defined as true, each record class will also generate a *Table class, extending from :php:class:`Doctrine_Table`.

Then, you can implement the method :php:meth:`construct` to include your Named Queries:

class UserTable extends Doctrine_Table
{
    public function construct()
    {
        // Named Query defined using DQL string
        $this->addNamedQuery('get.by.id', 'SELECT u.username FROM User u WHERE u.id = ?');

        // Named Query defined using Doctrine_Query object
        $this->addNamedQuery(
            'get.by.similar.usernames',
            Doctrine_Query::create()
                ->select('u.id, u.username')
                ->from('User u')
                ->where('LOWER(u.username) LIKE LOWER(?)')
        );
    }
}

Accessing Named Query

To reach the MyFooTable class, which is a subclass of :php:class:`Doctrine_Table`, you can do the following:

$userTable = Doctrine_Core::getTable('User');

To access the Named Query (will return you a :php:class:`Doctrine_Query` instance, always):

$q = $userTable->createNamedQuery('get.by.id');

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT
    u.id AS u__id,
    u.username AS u__username
FROM user u
WHERE u.id = ?

Executing a Named Query

There are two ways to execute a Named Query. The first one is by retrieving the :php:class:`Doctrine_Query` and then executing it normally, as a normal instance:

$users = Doctrine_Core::getTable('User')
            ->createNamedQuery('get.by.similar.usernames')
            ->execute(array('%jon%wage%'));

You can also simplify the execution, by doing:

$users = Doctrine_Core::getTable('User')
            ->find('get.by.similar.usernames', array('%jon%wage%'));

The method :php:meth:`find` also accepts a third parameter, which is the hydration mode.

Cross-Accessing Named Query

If that’s not enough, Doctrine take advantage the Doctrine_Query_Registry and uses namespace queries to enable cross-access of Named Queries between objects. Suppose you have the *Table class instance of record Article. You want to call the “get.by.id” Named Query of record User. To access the Named Query, you have to do:

$articleTable = Doctrine_Core::getTable('Article');
$users = $articleTable->find('User/get.by.id', array(1, 2, 3));

BNF

QL_statement                                   ::=  select_statement | update_statement | delete_statement
select_statement                               ::=  select_clause from_clause
                                                    [where_clause]
                                                    [groupby_clause]
                                                    [having_clause]
                                                    [orderby_clause]
update_statement                               ::=  update_clause [where_clause]
delete_statement                               ::=  delete_clause [where_clause]
from_clause                                    ::=  "FROM" identification_variable_declaration
                                                        {
                                                            ","
                                                            {identification_variable_declaration | collection_member_declaration}
                                                        }*
identification_variable_declaration            ::=  range_variable_declaration
                                                    {join | fetch_join}*
range_variable_declaration                     ::=  abstract_schema_name
                                                    ["AS"]
                                                    identification_variable
join                                           ::=  join_spec
                                                    join_association_path_expression
                                                    ["AS"] identification_variable
fetch_join                                     ::=  join_spec "FETCH" join_association_path_expression
association_path_expression                    ::=  collection_valued_path_expression
                                                        | single_valued_association_path_expression
join_spec                                      ::=  ["LEFT"]
                                                    ["OUTER" | "INNER"]
                                                    "JOIN"
join_association_path_expression               ::=  join_collection_valued_path_expression
                                                        | join_single_valued_association_path_expression
join_collection_valued_path_expression         ::=  identification_variable.collection_valued_association_field
join_single_valued_association_path_expression ::=  identification_variable.single_valued_association_field
collection_member_declaration                  ::=  "IN"
                                                    "(" collection_valued_path_expression ")"
                                                    ["AS"]
                                                    identification_variable
single_valued_path_expression                  ::=  state_field_path_expression
                                                        | single_valued_association_path_expression
state_field_path_expression                    ::=  {
                                                        identification_variable
                                                            | single_valued_association_path_expression
                                                    }.state_field
single_valued_association_path_expression      ::=  identification_variable.{single_valued_association_field.}*
                                                    single_valued_association_field
collection_valued_path_expression              ::=  identification_variable.{single_valued_association_field.}*
                                                    collection_valued_association_field
state_field                                    ::=  {embedded_class_state_field.}*
                                                    simple_state_field
update_clause                                  ::=  "UPDATE"
                                                    abstract_schema_name
                                                    [["AS"] identification_variable]
                                                    "SET"
                                                    update_item {"," update_item}*
update_item                                    ::=  [identification_variable.]{state_field | single_valued_association_field}
                                                    "="
                                                    new_value
new_value                                      ::=  simple_arithmetic_expression
                                                        | string_primary
                                                        | datetime_primary
                                                        | boolean_primary
                                                        | enum_primary simple_entity_expression
                                                        | "NULL"
delete_clause                                  ::=  "DELETE" "FROM" abstract_schema_name [["AS"] identification_variable]
select_clause                                  ::=  "SELECT" ["DISTINCT"] select_expression {"," select_expression}*
select_expression                              ::=  single_valued_path_expression
                                                        | aggregate_expression
                                                        | identification_variable
                                                        | "OBJECT" "(" identification_variable ")"
                                                        | constructor_expression
constructor_expression                         ::=  "NEW" constructor_name "("
                                                        constructor_item {"," constructor_item}*
                                                    ")"
constructor_item                               ::=  single_valued_path_expression | aggregate_expression
aggregate_expression                           ::=      { "AVG" | "MAX" | "MIN" | "SUM"} "("
                                                            ["DISTINCT"]
                                                            state_field_path_expression
                                                        ")"
                                                    |
                                                        "COUNT" "("
                                                            ["DISTINCT"]
                                                            identification_variable
                                                                | state_field_path_expression
                                                                | single_valued_association_path_expression
                                                        ")"
where_clause                                   ::=  "WHERE" conditional_expression
groupby_clause                                 ::=  "GROUP" "BY" groupby_item {"," groupby_item}*
groupby_item                                   ::=  single_valued_path_expression` | identification_variable
having_clause                                  ::=  "HAVING" conditional_expression
orderby_clause                                 ::=  "ORDER" "BY" orderby_item {"," orderby_item}*
orderby_item                                   ::=  state_field_path_expression` ["ASC" | "DESC"]
subquery                                       ::=  simple_select_clause` subquery_from_clause [where_clause] [groupby_clause] [having_clause]
subquery_from_clause                           ::=  FROM" subselect_identification_variable_declaration{"," subselect_identification_variable_declaration}*
subselect_identification_variable_declaration  ::=  identification_variable_declaration
                                                        | association_path_expression ["AS"] identification_variable
                                                        | collection_member_declaration
simple_select_clause                           ::=  "SELECT" ["DISTINCT"] simple_select_expression
simple_select_expression                       ::=  single_valued_path_expression
                                                        | aggregate_expression
                                                        | identification_variable
conditional_expression                         ::=  conditional_term
                                                        | conditional_expression "OR" conditional_term
conditional_term                               ::=  conditional_factor
                                                        | conditional_term "AND" conditional_factor
conditional_factor                             ::=  ["NOT"]
                                                    conditional_primary
conditional_primary                            ::=  simple_cond_expression
                                                        | "(" conditional_expression ")"
simple_cond_expression                         ::=  comparison_expression`
                                                        | between_expression
                                                        | like_expression
                                                        | in_expression
                                                        | null_comparison_expression
                                                        | empty_collection_comparison_expression
                                                        | collection_member_expression
                                                        | exists_expression
between_expression                             ::=  arithmetic_expression ["NOT"] "BETWEEN" arithmetic_expression "AND" arithmetic_expression
                                                        | string_expression ["NOT"] "BETWEEN" string_expression "AND" string_expression
                                                        | datetime_expression ["NOT"] "BETWEEN" datetime_expression "AND" datetime_expression
in_expression                                  ::=  state_field_path_expression
                                                    ["NOT"] "IN"
                                                    "("
                                                        in_item {"," in_item}* | subquery
                                                    ")"
in_item                                        ::=  literal` | input_parameter
like_expression                                ::=  string_expression
                                                    ["NOT"] "LIKE"
                                                    pattern_value
                                                    ["ESCAPE" escape_character]
null_comparison_expression                     ::=  {single_valued_path_expression | input_parameter}
                                                    "IS" ["NOT"] "NULL"
empty_collection_comparison_expression         ::=  collection_valued_path_expression
                                                    "IS" ["NOT"] "EMPTY"
collection_member_expression                   ::=  entity_expression
                                                    ["NOT"] "MEMBER" ["OF"]
                                                    collection_valued_path_expression
exists_expression                              ::=  ["NOT"] "EXISTS" "(" subquery ")"
all_or_any_expression                          ::=   "ALL" | "ANY" | "SOME" } "(" subquery ")"
comparison_expression                          ::=  string_expression` comparison_operator {string_expression | all_or_any_expression}
                                                        | boolean_expression {"=" | "<>"} {boolean_expression | all_or_any_expression}
                                                        | enum_expression {"=" | "<>"} {enum_expression | all_or_any_expression}
                                                        | datetime_expression comparison_operator {datetime_expression | all_or_any_expression}
                                                        | entity_expression {"=" | "<>"} {entity_expression | all_or_any_expression}
                                                        | arithmetic_expression comparison_operator {arithmetic_expression | all_or_any_expression}
comparison_operator                            ::=  "=" | ">" | ">=" | "<" | "<=" | "<>"
arithmetic_expression                          ::=  simple_arithmetic_expression | "(" subquery ")"
simple_arithmetic_expression                   ::=  arithmetic_term
                                                        | simple_arithmetic_expression {"+" | "-"} arithmetic_term
arithmetic_term                                ::=  arithmetic_factor
                                                        | arithmetic_term { "*" | "/" } arithmetic_factor
arithmetic_factor                              ::=  [{"+" | "-"}] arithmetic_primary
arithmetic_primary                             ::=  state_field_path_expression
                                                        | numeric_literal
                                                        | "(" simple_arithmetic_expression ")"
                                                        | input_parameter
                                                        | functions_returning_numerics
                                                        | aggregate_expression
string_expression                              ::=  string_primary` | "(" subquery ")"
string_primary                                 ::=  state_field_path_expression
                                                        | string_literal
                                                        | input_parameter
                                                        | functions_returning_strings
                                                        | aggregate_expression
datetime_expression                            ::=  datetime_primary` | "(" subquery ")"
datetime_primary                               ::=  state_field_path_expression
                                                        | input_parameter
                                                        | functions_returning_datetime
                                                        | aggregate_expression
boolean_expression                             ::=  boolean_primary` | "(" subquery ")"
boolean_primary                                ::=  state_field_path_expression | boolean_literal | input_parameter
enum_expression                                ::=  enum_primary` | "(" subquery ")"
enum_primary                                   ::=  state_field_path_expression` | enum_literal | input_parameter
entity_expression                              ::=  single_valued_association_path_expression | simple_entity_expression
simple_entity_expression                       ::=  identification_variable | input_parameter
functions_returning_numerics                   ::=  "LENGTH" "(" string_primary ")"
                                                        | "LOCATE" "(" `string_primary "," string_primary ["," simple_arithmetic_expression] ")"
                                                        | "ABS" "(" simple_arithmetic_expression ")"
                                                        | "SQRT" "(" simple_arithmetic_expression ")"
                                                        | "MOD" "(" simple_arithmetic_expression "," simple_arithmetic_expression ")"
                                                        | "SIZE" "(" collection_valued_path_expression ")"
functions_returning_datetime                   ::=  "CURRENT_DATE" | "CURRENT_TIME" | "CURRENT_TIMESTAMP"
functions_returning_strings                    ::=  CONCAT" "(" string_primary "," string_primary ")"
                                                        | "SUBSTRING" "(" string_primary "," simple_arithmetic_expression "," simple_arithmetic_expression ")"
                                                        | "TRIM" "(" [[trim_specification] [trim_character] "FROM"] string_primary ")"
                                                        | "LOWER" "(" string_primary ")"
                                                        | "UPPER" "(" string_primary ")"
trim_specification                             ::=  "LEADING" | "TRAILING" | "BOTH"

Magic Finders

Doctrine offers some magic finders for your Doctrine models that allow you to find a record by any column that is present in the model. This is helpful for simply finding a user by their username, or finding a group by the name of it. Normally this would require writing a :php:class:`Doctrine_Query` instance and storing this somewhere so it can be reused. That is no longer needed for simple situations like that.

The basic pattern for the finder methods are as follows: findBy%s($value) or findOneBy%s($value). The %s can be a column name or a relation alias. If you give a column name you must give the value you are looking for. If you specify a relationship alias, you can either pass an instance of the relation class to find, or give the actual primary key value.

First lets retrieve the UserTable instance to work with:

$userTable = Doctrine_Core::getTable('User');

Now we can easily find a User record by its primary key by using the :php:meth:`find` method:

$user = $userTable->find(1);

Now if you want to find a single user by their username you can use the following magic finder:

$user = $userTable->findOneByUsername('jonwage');

You can also easily find records by using the relationships between records. Because User has many Phonenumbers we can find those Phonenumbers by passing the :php:meth:`findBy**` method a User instance:

$phonenumberTable = Doctrine_Core::getTable('Phonenumber');
$phonenumbers = $phonenumberTable->findByUser($user);

The magic finders will even allow a little more complex finds. You can use the And and Or keywords in the method name to retrieve record by multiple properties.

$user = $userTable->findOneByUsernameAndPassword('jonwage', md5('changeme'));

You can even mix the conditions.

$users = $userTable->findByIsAdminAndIsModeratorOrIsSuperAdmin(true, true, true);

Caution

These are very limited magic finders and it is always recommended to expand your queries to be manually written DQL queries. These methods are meant for only quickly accessing single records, no relationships, and are good for prototyping code quickly.

Note

The documented magic finders above are made possibly by using PHP’s __call() overloading functionality. The undefined functions are forwarded to :php:meth:`Doctrine_Table::{*}call` where the :php:class:`Doctrine_Query` objects are built, executed and returned to the user.

Debugging Queries

The :php:class:`Doctrine_Query` object has a few functions that can be used to help debug problems with the query:

Sometimes you may want to see the complete SQL string of your :php:class:`Doctrine_Query` object:

$q = Doctrine_Query::create()
        ->select('u.id')
        ->from('User u')
        ->orderBy('u.username');

echo $q->getSqlQuery();

The above call to :php:meth:`getSqlQuery()` would output the following SQL query:

SELECT u.id AS u__id FROM user u ORDER BY u.username

Note

The SQL returned above by the :php:meth:`Doctrine_Query::getSql` function does not replace the tokens with the parameters. This is the job of PDO and when we execute the query we pass the parameters to PDO where the replacement is executed. You can retrieve the array of parameters with the :php:meth:`Doctrine_Query::getParams` method.

Get the array of parameters for the :php:class:`Doctrine_Query` instance:

print_r($q->getParams());

Conclusion

The Doctrine Query Language is by far one of the most advanced and helpful feature of Doctrine. It allows you to easily select very complex data from RDBMS relationships efficiently!

Now that we have gone over most of the major components of Doctrine and how to use them we are going to take a step back in the next chapter and look at everything from a birds eye view in the Component Overview chapter.

Fork me on GitHub