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:

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

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

1
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

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

SELECT queries

SELECT statement syntax:

1
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>]
2
3
4
5
6
7
8
9
10
11

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:

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

1
$ php test.php
Array (
    [0] => Array
        (
            [id] => 1
            [name] => test 1
            [amount] =>
        )
    [1] => Array
        (
            [id] => 2
            [name] => test 2
            [amount] =>
        )
)
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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:

1
SELECT
    a.id AS a__id,
    a.name AS a__name,
    a.amount AS a__amount
FROM account a
2
3
4
5

Now lets execute the query and inspect the results:

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

The above example would produce the following output:

1
$ php test.php
Array (
    [0] => Array
        (
            [id] => 1
            [name] => test 1
            [amount] => 100.00
        )
    [1] => Array
        (
            [id] => 2
            [name] => test 2
            [amount] => 200.00
        )
)
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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:

1
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
2
3
4
5
6
7
8
9

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:

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

1
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
2
3
4
5
6
7

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:

1
SELECT
    u.id AS u__id,
    u.username AS u__username
FROM user u
ORDER BY u.username
2
3
4
5

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:

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

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

1
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
2
3
4
5
6
7
8

$ users = $q->execute();

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

UPDATE queries

UPDATE statement syntax:

1
UPDATE <component_name>
SET
    <col_name1> = <expr1>,
    <col_name2> = <expr2>
WHERE <where_condition>
ORDER BY <order_by>
LIMIT <record_count>
2
3
4
5
6
7

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

1
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

1
DELETE FROM <component_name>
WHERE <where_condition>
ORDER BY <order_by>
LIMIT <record_count>
2
3
4

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

1
DELETE FROM account WHERE id > 3

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

$ rows = $q->execute();

echo $rows;

FROM clause

Syntax:

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

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:

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

1
[[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>
2
3
4

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

1
SELECT
u.id AS u__id,
    p.id AS p__id
FROM user u
    LEFT JOIN phonenumber p
ON u.id = p.user_id
2
3
4
5
6

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:

1
SELECT
    u.id AS u__id,
    p.id AS p__id
FROM User u
    LEFT JOIN Phonenumbers p ON u.id = p.user_id
2
3
4
5

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

echo $q->getSqlQuery();

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

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

echo $q->getSqlQuery();

1
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
2
3
4
5
6

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

1
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
2
3
4
5
6
7
8
9
10
11

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

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:

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

1
SELECT
    u.id AS u__id
FROM user u
WHERE
    u.type = ?
    AND u.is_active = ?
    OR u.is_super_admin = ?
2
3
4
5
6
7

Conditional expressions

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

echo $q->getSqlQuery();

1
SELECT
    u.id AS u__id,
    u.username AS u__username
FROM user u
WHERE
    u.username = ?
2
3
4
5
6

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

echo $q->getSqlQuery();

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

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

echo $q->getSqlQuery();

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

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

echo $q->getSqlQuery();

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

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

echo $q->getSqlQuery();

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

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

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

1
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 = ?
)
2
3
4
5
6
7
8
9
10
11
12
13

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

echo $q->getSqlQuery();

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

1
string_expression [NOT] LIKE pattern_value [ESCAPE escape_character]

1
[NOT] EXISTS (<subquery>)

// 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
        ));
    }
}

1
# schema.yml
ReaderLog:
  columns:
    article_id:
      type: integer
      primary: true
    user_id:
      type: integer
      primary: true
2
3
4
5
6
7
8
9

$ php generate.php

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

1
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
    )
2
3
4
5
6
7
8
9
10
11

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

1
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
    )
2
3
4
5
6
7
8
9
10
11

1
operand comparison_operator ANY (subquery)
operand comparison_operator SOME (subquery)
operand comparison_operator ALL (subquery)
2
3

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

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

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

1
FROM C WHERE C.col1 <> ALL (FROM C2(col1));
FROM C WHERE C.col1 NOT IN (FROM C2(col1));
2

$ 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))');

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

1
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 = ?
)
2
3
4
5
6
7
8
9
10
11
12
13

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

1
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
)
2
3
4
5
6
7
8
9
10
11
12

Functional Expressions

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

echo $q->getSqlQuery();

1
SELECT
    u.id AS u__id,
    CONCAT(u.first_name, u.last_name) AS u__0
FROM user u
2
3
4

$ users = $q->execute();

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

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

echo $q->getSqlQuery();

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

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

echo $q->getSqlQuery();

1
SELECT
u.id AS u__id,
    u.username AS u__username
FROM user u
WHERE
TRIM(u.username) = ?
2
3
4
5
6

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

echo $q->getSqlQuery();

1
SELECT
    u.id AS u__id,
    u.username AS u__username
FROM user u
WHERE
    LOWER(u.username) = 'someone'
2
3
4
5
6

Subqueries

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

1
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 = ?
    )
2
3
4
5
6
7
8
9
10
11
12
13
14

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

1
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
2
3
4
5
6
7
8
9
10

GROUP BY, HAVING clauses

DQL GROUP BY syntax:

1
GROUP BY groupby_item {, groupby_item}*

DQL HAVING syntax:

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

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

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

  1
  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
  2
  3
  4
  5
  6
  7

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

  1
  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
  2
  3
  4
  5
  6
  7
  8

  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

1
[
   ORDER BY {ComponentAlias.columnName}
   [ASC | DESC], ...
]
2
3
4

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

echo $q->getSqlQuery();

1
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
2
3
4
5
6
7
8
9

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

echo $q->getSqlQuery();

1
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
2
3
4
5
6
7
8
9

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

echo $q->getSqlQuery();

1
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
2
3
4
5
6
7
8

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

echo $q->getSqlQuery();

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

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

1
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
2
3
4
5
6
7
8
9

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

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

echo $q->getSqlQuery();

1
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
    )
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

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

echo $q->getSqlQuery();

1
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
    )
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

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.

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(?)')
        );
    }
}

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

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

echo $q->getSqlQuery();

1
SELECT
    u.id AS u__id,
    u.username AS u__username
FROM user u
WHERE u.id = ?
2
3
4
5

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

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

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

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:

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

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.