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

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

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.

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

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

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

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

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

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

The above code is equal to doing:

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

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

Update Query

In this example query we will update a users password.

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.

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.

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

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

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

A convenience method exists called fetchArray().

$results = $q->fetchArray($params);
 

Record Hydration

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

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

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

Comments

My first time seeing Doctrine_Query::create()->parseQuery($dql); I have been wondering about sub-queries too. Good Read.
In the DBMS functions section must add ->groupBy('p.id') for the example to work properly. Regards
So, Doctrine seems to go in a purely hash-based direction, entirely eschewing the capacity to leverage auto-completion of Model entities -- is this accurate?
@jL You can use both syntaxes, hash based and object style. The cool thing: the array syntax also works when objects are hydrated, so this syntax it will allow you to easily switch hydration modes (array mode is faster).
I always hace a question.

Why Fabien post examples with Propel, but we can see more and more great cases with Doctrine.

When Zaninotto, worked in DBFinder, he talk about performance, and he did say that PROPEL 1.3 is faster like anyone.

Today, which is more faster?
Can we have comparative?

Another thing, is hace to learn two type of schemas (porpel and doctrine) Can be more simple?
@puentesdiaz Doctrine is faster because it can retrieve more complex data sets in single queries. Propel 1.3 is faster because it uses PDO, Doctrine has always used PDO. You will just have to learn the Doctrine schema syntax, it is not that much different.
@puentesdiaz Also, fabien is switching literally right now :) The transition is happening as we speak so you will begin to see things change in the coming months.
Hi,
Pleaze, next time Jonathan, can you show us how to put primary data in DB, not fixture but real data, like a first user or a row list for a select purpose to populate a DB for real use.
Thx
@pppswing At the end of the article I say, "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."

My next article will continue with this schema and show you how to work with the objects and relationships with php code.
great work, Jonathan. Thank you
why the need to write

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

an not like we already write the update:

Doctrine_Query::create()
->delete('User u')
->where('u.username = ?', 'jwage')
->execute();
That is great. That is so helpful for me to understand and start working with doctrine.
@Markus.Staab Both ways are supported.
May i suggest that these doctrine related posts are either linked to in the book or added. There seems to be quite a bit of missing/outdated information in the book right now and with that being a main starting place for many people it would make sense.
Especially articles on nested forms etc, the form book isnt completed with many missing chapters yet most of the work that could go into the book seems to be appearing on this blog instead?
Not to criticise the effort that’s being put in, its definitely useful information, i just wish it could be seen by many more.

David
@David It is coming :)
would be nice to have more information on Doctrine_Collection. Why and where do we need this object..?

also would be nice to have a short description what hydration exactly refers to..

Comments are closed.

To ensure that comments stay relevant, they are closed for old posts.