| This project is no longer maintained and has been archived.

master

# 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:

| 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";
        }
    }

| 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__phonenumberFROM 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";
        }
    }

| 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.phpArray (    [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__amountFROM account a

| 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.phpArray (    [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 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__phonenumberFROM 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 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 getSqlQuery() would output the following SQL query:

    SELECT   u.id AS u__id,   u.username AS u__usernameFROM user u    LEFT JOIN phonenumber p        ON u.id = p.user_idHAVING 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 getSqlQuery() would output the following SQL query:

    SELECT    u.id AS u__id,    u.username AS u__usernameFROM user uORDER 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 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 getSqlQuery() would output the following SQL query:

    SELECT    u.id AS u__id,    COUNT(f.id) AS f__0FROM user u    LEFT JOIN forum__thread f        ON u.id = f.user_idWHERE 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 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 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;

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

| 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 getSqlQuery() would output the following SQL query:

    SELECTu.id AS u__id,    p.id AS p__idFROM user u    LEFT JOIN phonenumber pON u.id = p.user_id

| 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 getSqlQuery() would output the following SQL query:

    SELECT    u.id AS u__id,    p.id AS p__idFROM 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 getSqlQuery() would output the following SQL query:

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

| 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 getSqlQuery() would output the following SQL query:

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

| 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 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 getSqlQuery() would output the following SQL query:

    SELECT    u.id AS u__idFROM 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();

| The `INDEXBY` keyword does not alter the generated SQL. It is simply used internally by 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 orWhere, whereNotIn, orWhereNotIn functions for building complex where conditions using 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 getSqlQuery() would output the following SQL query:

    SELECT    u.id AS u__idFROM user uWHERE    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 getSqlQuery() would output the following SQL query:

    SELECT    u.id AS u__id,    u.username AS u__usernameFROM user uWHERE    u.username = ?

| Because we passed the value of the `username` as a parameter to the 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 Doctrine_Query instance you can use the 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 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 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 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 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();

  | 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 getSqlQuery() would output the following SQL query:

      SELECT    u.id AS u__idFROM user uWHERE 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 getSqlQuery() would output the following SQL query:

      SELECT    u.id AS u__idFROM user uWHERE (    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 getSqlQuery() would output the following SQL query:

      SELECT    u.id AS u__idFROM user uWHERE    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 getSqlQuery() would output the following SQL query:

      SELECT    u.id AS u__idFROM user uWHERE    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 getSqlQuery() would output the following SQL query:

    SELECT    u.id AS u__idFROM user uWHERE 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 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 getSqlQuery() would output the following SQL query:

      SELECT    u.id AS u__idFROM user u    LEFT JOIN email e ON u.id = e.user_idWHERE    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 getSqlQuery() would output the following SQL query:

      SELECT    u.id AS u__idFROM user uWHERE    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.

| 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.ymlReaderLog:  columns:    article_id:      type: integer      primary: true    user_id:      type: integer      primary: true
| 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 getSqlQuery() would output the following SQL query:

    SELECT    a.id AS a__idFROM article aWHERE    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 getSqlQuery() would output the following SQL query:

    SELECT    a.id AS a__idFROM article aWHERE    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 getSqlQuery() would output the following SQL query:

    SELECT    u.id AS u__idFROM user uWHERE 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 getSqlQuery() would output the following SQL query:

    SELECT    u.id AS u__idFROM user uWHERE 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 getSqlQuery() would output the following SQL query:

    SELECT    u.id AS u__id,    CONCAT(u.first_name, u.last_name) AS u__0FROM 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 getSqlQuery() would output the following SQL query:

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

| 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 getSqlQuery() would output the following SQL query:

    SELECTu.id AS u__id,    u.username AS u__usernameFROM user uWHERETRIM(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 getSqlQuery() would output the following SQL query:

    SELECT    u.id AS u__id,    u.username AS u__usernameFROM user uWHERE    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:

> ABS(simple_arithmetic_expression) SQRT(simple_arithmetic_expression) MOD(simple_arithmetic_expression`, :token:`simple_arithmetic_expression)

- 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 getSqlQuery() would output the following SQL query:

    SELECT    u.id AS u__idFROM user uWHERE    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 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__0FROM 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 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 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 getSqlQuery() would output the following SQL query:

      SELECT    u.id AS u__id,    u.username AS u__username, COUNT(p.id) AS p__0FROM user u    LEFT JOIN phonenumber p        ON u.id = p.user_idGROUP 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 getSqlQuery() would output the following SQL query:

      SELECT    u.id AS u__id,    u.username AS u__username, COUNT(p.id) AS p__0FROM user u    LEFT JOIN phonenumber p        ON u.id = p.user_idGROUP BY u.idHAVING 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 getSqlQuery() would output the following SQL query:

    SELECT    u.id AS u__id,    u.username AS u__usernameFROM user u    LEFT JOIN phonenumber p        ON u.id = p.user_idORDER 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 getSqlQuery() would output the following SQL query:

    SELECT    u.id AS u__id,    u.username AS u__usernameFROM user u    LEFT JOIN email e    ON u.id = e.user_idORDER 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 getSqlQuery() would output the following SQL query:

    SELECT    u.id AS u__id,    u.username AS u__username,    COUNT(p.id) AS p__0FROM user u    INNER JOIN phonenumber p        ON u.id = p.user_idORDER 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 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();

| You can also use the offset method of the limit method to produce your desired `LIMIT` and `OFFSET` in the executed SQL query.

The above call to 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__phonenumberFROM user u    LEFT JOIN phonenumber p        ON u.id = p.user_idLIMIT 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 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_idFROM user u    LEFT JOIN phonenumber p        ON u.id = p.user_idWHERE    u.id IN (        SELECT            DISTINCT u2.id        FROM user u2        LIMIT 20    )

| 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 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_idFROM user u    INNER JOIN phonenumber p        ON u.id = p.user_idWHERE    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 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, 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 Doctrine_Table.

Then, you can implement the method 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 Doctrine_Table, you can do the following:

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

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

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

    echo $q->getSqlQuery();

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

    SELECT    u.id AS u__id,    u.username AS u__usernameFROM user uWHERE u.id = ?

### Executing a Named Query

There are two ways to execute a Named Query. The first one is by retrieving the 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 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 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 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 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);

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

| The documented magic finders above are made possibly by using PHP's __call() overloading functionality. The undefined functions are forwarded to Doctrine_Query objects are built, executed and returned to the user.

## Debugging Queries

The 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 Doctrine_Query object:

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

    echo $q->getSqlQuery();

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

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

| The SQL returned above by the 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 Doctrine_Query::getParams method.

Get the array of parameters for the 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.