Call the expert: Retrieving Data with Doctrine

In the last few blog posts about Doctrine we have demonstrated some of the functionality that integrates Doctrine with symfony like customizing sfDoctrineGuardPlugin and the new admin generator. This article is a bit different as it will demonstrate some of the functionality that exists in Doctrine whether you use it with symfony or by itself.

Schema File & Data Fixtures

First we need to define a schema and some data fixtures to test our queries against.

Schema File

[yml]
User:
  actAs: [Timestampable]
  columns:
    username:
      type: string(255)
    password:
      type: string(255)
    last_login:
      type: timestamp
  relations:
    Friends:
      class: User
      refClass: UserFriend
      local: user_id1
      foreign: user_id2
    Groups:
      class: Group
      refClass: UserGroup
      foreignAlias: Users
    Permissions:
      class: Permission
      refClass: UserPermission
      foreignAlias: Users

Group:
  tableName: groups
  columns:
    name: string(255)
  relations:
    Permissions:
      class: Permission
      refClass: GroupPermission
      foreignAlias: Groups

Permission:
  columns:
    name: string(255)

Phonenumber:
  columns:
    user_id: integer
    phonenumber: string(55)
  relations:
    User:
      foreignAlias: Phonenumbers
      onDelete: CASCADE

Profile:
  columns:
    user_id: integer
    first_name: string(255)
    last_name: string(255)
    email_address: string(255)
  relations:
    User:
      foreignType: one
      onDelete: CASCADE

UserFriend:
  columns:
    user_id1:
      type: integer
      primary: true
    user_id2:
      type: integer
      primary: true
  relations:
    User1:
      class: User
      local: user_id1
      foreignAlias: UserFriends
      onDelete: CASCADE
    User2:
      class: User
      local: user_id2
      foreignAlias: UserFriends
      onDelete: CASCADE

UserGroup:
  columns:
    user_id:
      type: integer
      primary: true
    group_id:
      type: integer
      primary: true
  relations:
    User:
      foreignAlias: UserGroups
      onDelete: CASCADE
    Group:
      foreignAlias: UserGroups
      onDelete: CASCADE

UserPermission:
  columns:
    user_id:
      type: integer
      primary: true
    permission_id:
      type: integer
      primary: true
  relations:
    User:
      foreignAlias: UserPermissions
      onDelete: CASCADE
    Permission:
      foreignAlias: UserPermissions
      onDelete: CASCADE

GroupPermission:
  columns:
    group_id:
      type: integer
      primary: true
    permission_id:
      type: integer
      primary: true
  relations:
    Group:
      foreignAlias: GroupPermissions
      onDelete: CASCADE
    Permission:
      foreignAlias: GroupPermissions
      onDelete: CASCADE

BlogPost:
  actAs:
    Timestampable:
    Sluggable:
      fields: [title]
  columns:
    user_id: integer
    title: string(255)
    body: clob
  relations:
    Author:
      class: User
      foreignAlias: BlogPosts
      onDelete: CASCADE
    Tags:
      class: Tag
      refClass: BlogPostTag
      foreignAlias: BlogPosts
    Comments:
      class: Comment
      refClass: BlogPostComment
      foreignAlias: BlogPosts

Tag:
  columns:
    name: string(255)

Comment:
  columns:
    title: string(255)
    body: clob

Page:
  actAs:
    Timestampable:
    Sluggable:
      fields: [title]
  columns:
    title: string(255)
    body: clob

BlogPostTag:
  columns:
    blog_post_id:
      type: integer
      primary: true
    tag_id:
      type: integer
      primary: true
  relations:
    BlogPost:
      foreignAlias: BlogPostTags
      onDelete: CASCADE
    Tag:
      foreignAlias: BlogPostTags
      onDelete: CASCADE

BlogPostComment:
  columns:
    blog_post_id:
      type: integer
      primary: true
    comment_id:
      type: integer
      primary: true
  relations:
    BlogPost:
      foreignAlias: BlogPostComments
      onDelete: CASCADE
    Comment:
      foreignAlias: BlogPostComments
      onDelete: CASCADE

Data Fixtures

[yml]
User:
  jwage:
    username: jwage
    password: changeme
    Profile:
      first_name: Jonathan
      last_name: Wage
      email_address: jonwage@gmail.com
    Groups: [Administrator]
    Friends: [fabpot, joeblow]
    Phonenumbers:
      Phonenumber_1:
        phonenumber: 6155139185
  fabpot:
    username: fabpot
    password: changeme
    Profile:
      first_name: Fabien
      last_name: Potencier
      email_address: fabien.potencier@symfony-project.com
    Groups: [ContentEditor]
    Friends: [jwage]
  joeblow:
    username: joeblow
    password: changeme
    Profile:
      first_name: Joe
      last_name: Blow
      email_address: jowblow@gmail.com
    Groups: [Registered]
    Friends: [jwage, fabpot]

Group:
  Administrator:
    name: Administrator
    Permissions: [EditPages, EditBlog, EditUsers, EditPages, Frontend]
  Blogger:
    name: Blogger
    Permissions: [EditBlog, Frontend]
  Moderator:
    name: Moderator
    Permissions: [EditUsers, EditComments, Frontend]
  ContentEditor:
    name: Content Editor
    Permissions: [EditPages, EditBlog, Frontend]
  Registered:
    name: Registered
    Permissions: [Frontend]

Permission:
  EditPages:
    name: Edit Pages
  EditBlog:
    name: Edit Blog
  EditUsers:
    name: Edit Users
  EditPages:
    name: Edit Pages
  EditComments:
    name: Edit Comments
  Frontend:
    name: Frontend

BlogPost:
  BlogPost_1:
    Author: jwage
    title: Sample Blog Post
    body: This is a sample blog post
    Tags: [symfony, doctrine, php, mvc]
    Comments:
      Comment_1:
        title: This is a bad blog post
        body: Yes this is indeed a horrible blog post
      Comment_2:
        title: I think this is awesome
        body: This is an awesome blog post, what are you talking about?!?!?!

Tag:
  symfony:
    name: symfony
  php:
    name: PHP
  doctrine:
    name: Doctrine
  mvc:
    name: MVC

Page:
  home:
    title: Home
    body: This is the content of the home page
  about:
    title: About
    body: This is the content of the about page
  faq:
    title: F.A.Q.
    body: This is the content of the frequently asked questions page

Select Queries

DBMS Functions

First we will demonstrate how you can use DBMS functions in your queries. For example you might want to retrieve all blog posts with a count of the number of comments for each blog post.

[php]
$q = Doctrine_Query::create()
  ->select('p.*, COUNT(c.id) as num_comments')
  ->from('BlogPost p')
  ->leftJoin('p.Comments c')
  ->groupBy('p.id');
$results = $q->execute();
echo $results[0]['num_comments'];

You can use any combination of functions and nest them as deeply as you want.

Multiple Joins

Doctrine makes it easy to retrieve data from multiple tables. In this example we can retrieve all the permissions a user has, even the ones through his assigned groups.

[php]
$q = Doctrine_Query::create()
  ->from('User u')
  ->leftJoin('u.Permissions p')
  ->leftJoin('u.Groups g')
  ->leftJoin('g.Permissions p2')
  ->where('u.id = ?', 1);
$user = $q->fetchOne();

Now we can build a Doctrine_Collection of all the Permissions the user has.

[php]
$permissions = new Doctrine_Collection('Permission');
foreach ($user['Groups'] as $group)
{
  foreach ($group['Permissions'] as $permission)
  {
    $permissions[] = $permission;
  }
}
foreach ($user['Permissions'] as $permission)
{
  $permissions[] = $permission;
}

In a blog application, it is a common need to want to retrieve a BlogPost with the related Author, Comments and Tags all in one query. With Doctrine this is just as easy as it was for me to type the previous sentence.

[php]
$q = Doctrine_Query::create()
  ->from('BlogPost p')
  ->leftJoin('p.Author a')
  ->leftJoin('p.Comments c')
  ->leftJoin('p.Tags t')
  ->where('p.id = ?', 1);

Sub-Queries

We can alternatively retrieve the same Permission Doctrine_Collection directly from Doctrine using sub-queries to know which Permission records to retrieve.

[php]
$userId = 1;
$q = Doctrine_Query::create()
  ->from('Permission p');

$q2 = $q->createSubquery()
  ->select('p2.permission_id')
  ->from('UserPermission p2')
  ->where('p2.user_id = ?');

$q3 = $q->createSubquery()
  ->select('p3.id')
  ->from('Permission p3')
  ->leftJoin('p3.GroupPermissions gp')
  ->leftJoin('gp.Group g')
  ->leftJoin('g.Users u')
  ->where('u.id = ?');

$q->where('p.id IN (' . $q2->getDql() . ')')
  ->orWhere('p.id IN (' . $q3->getDql() . ')');

$permissions = $q->execute(array($userId, $userId));

Shorthand Left Joins

One of the great convenience features of Doctrine is the ability to specify joins in a shorthand syntax. This will greatly reduce the number of lines of code a query may occupy. You can simply change models together in the from() part to specify joins and they default to the same thing as using leftJoin().

[php]
$q = Doctrine_Query::create()
  ->from('User u, u.Profile p, u.Groups g');

The above code is equal to doing:

[php]
$q = Doctrine_Query::create()
  ->from('User u')
  ->leftJoin('u.Profile p')
  ->leftJoin('u.Groups g');

Delete & Update Queries

Doctrine_Query can be used to specify UPDATE and DELETE queries by simply using the update() or delete() functions. Here are some examples.

Delete Query

In this example we will delete a user by his username.

[php]
Doctrine_Query::create()
  ->delete()
  ->from('User u')
  ->where('u.username = ?', 'jwage')
  ->execute();

Update Query

In this example query we will update a users password.

[php]
Doctrine_Query::create()
  ->update('User u')
  ->set('u.password', '?', 'newpassword')
  ->where('u.username = ?', 'jwage')
  ->execute();

The set() function accepts three arguments. The first is the name of the field you want to set, the second is the part that is passed through to PDO untouched and the third is the parameter/value.

Another example would be setting a timestamp field with a dbms functions. We don't use the third argument because we want NOW() to be passed through to PDO un-touched.

[php]
Doctrine_Query::create()
  ->update('User u')
  ->set('u.last_login', 'NOW()')
  ->where('u.username = ?', 'jwage')
  ->execute();

The benefit of using the DQL update and delete is that it only requires one query to accomplish what you want. If you use objects then the object must be retrieved first, then updated or deleted which means two individual queries.

Manually writing DQL

For you SQL buffs, we didn't forget about you. You can optionally write your DQL queries manually and parse them in to a Doctrine_Query instance or just execute them.

[php]
$dql = "FROM User u, u.Phonenumbers p";
$q = Doctrine_Query::create()->parseQuery($dql);

Or you can just execute them by using the query() method of Doctrine_Query.

[php]
$dql = "FROM User u, u.Phonenumbers p";
$q = Doctrine_Query::create()->query($dql);

Executing Queries

In all the above examples we show you how you can create the queries, but what about executing them? Doctrine offers a few different ways to execute the queries and a few different ways to hydrate the data. It can hydrate the data as objects, php arrays which is much much faster than using objects or it can simply skip the hydration process all together.

Array Hydration

Here are a few examples of how you can execute array hydration.

[php]
$results = $q->execute($params, Doctrine::HYDRATE_ARRAY);

A convenience method exists called fetchArray().

[php]
$results = $q->fetchArray($params);

Record Hydration

[php]
$results = $q->execute($params, Doctrine::HYDRATE_RECORD);

Record hydration is the default so you can omit the 2nd argument if you like.

No Hydration

We can simply skip the hydration process completely and return what PDO gives us. This is only useful in very few cases, like when you only have one row and one column of data. The data is returned as an array with numeric keys so it is not very useful in any other cases.

[php]
$results = $q->execute($params, Doctrine::HYDRATE_NONE);

Fetching one Record

You can use the fetchOne() convenience method to automatically add a limit of one and return a single result instead of multiple.

[php]
$result = $q->fetchOne($params, Doctrine::HYDRATE_ARRAY);

That is all for today. In the next article we will demonstrate how to work with the objects defined in the relationships and retrieved in the above queries.