The symfony and Doctrine book

Introduction

Doctrine offers the ability to load small sets of sample test data by using a simple YAML syntax for specifying data to be loaded in to your object relationship hierarchy. It supports easily creating information for your tables and linking foreign keys between records.

The examples demonstrated in this chapter use the following simple User and Phonenumber schema which should be placed in config/doctrine/schema.yml.

User:
  columns:
    username: string(255)
    password: string(255)

Phonenumber:
  columns:
    user_id: integer
    phonenumber: string(25)
  relations:
    User:
      foreignAlias: Phonenumbers

Profile:
  columns:
    name: string(255)
    about: string(500)
    user_id: integer
  relations:
    User:
      foreignType: one

In sfDoctrinePlugin, when linking records in data fixtures you use the relationship name, unlike sfPropelPlugin where you use the foreign key name. You also have the ability to specify the data fixtures inline. Meaning, a block of YAML that represents a Doctrine_Record instance can have nested data structures that define the relationship graph for that Doctrine_Record child. Later in this chapter will demonstrate both the original and inline style data fixtures.

Original

Create data/fixtures/user.yml and load the following YAML code.

User:
  User_1:
    username: jwage
    password: changeme
  User_2:
    username: fabpot
    password: changeme
  User_3:
    username: dwhittle
    password: changeme

Run the following commands to rebuild the database.

$ ./symfony doctrine:build-all-reload

Now run a simple DQL query to inspect that the data was loaded properly.

$ ./symfony doctrine:dql "FROM User u"
>> doctrine  executing dql query
DQL: FROM User u
found 3 results
-
  id: '1'
  username: jwage
  password: changeme
-
  id: '2'
  username: fabpot
  password: changeme
-
  id: '3'
  username: dwhittle
  password: changeme

Setting Date Values in Data Fixtures

The sfYaml parser will automatically convert valid dates in to unix timestamps unless you specifically wrap it in single quotes forcing it to be a string type as far as the parser is concerned. If you do not use single quotes when setting date or timestamp column types the Doctrine validation will fail because of the value being passed to the Doctrine_Record being a unix timestamp.

Here is an example of how you can set the created_at column of a User model.

User:
  User_1:
    username: jwage
    password: changeme
    created_at: '2008-12-17 00:01:00'

Linking Relationships

Create data/fixtures/phonenumber.yml and load the following YAML data fixtures.

Phonenumber:
  Phonenumber_1:
    phonenumber: 6155139185
    User: User_1
  Phonenumber_2:
    phonenumber: 1234567890
    User: User_2
  Phonenumber_3:
    phonenumber: 0987654321
    User: User_3

Rebuild the database and run another DQL query to inspect the loaded data fixtures.

$ ./symfony doctrine:build-all-reload

Now inspect the data with a more complex query that joins the User Phonenumber records.

$ ./symfony doctrine:dql "FROM User u, u.Phonenumbers p"
>> doctrine  executing dql query
DQL: FROM User u, u.Phonenumbers p
found 3 results
-
  id: '1'
  username: jwage
  password: changeme
  Phonenumbers:
    -
      id: '1'
      phonenumber: 6155139185
      user_id: '1'
-
  id: '2'
  username: fabpot
  password: changeme
  Phonenumbers:
    -
      id: '2'
      phonenumber: 1234567890
      user_id: '2'
-
  id: '3'
  username: dwhittle
  password: changeme
  Phonenumbers:
    -
      id: '3'
      phonenumber: 0987654321
      user_id: '3'

Many to Many

Use the following YAML schema file in config/doctrine/schema.yml with the example data fixtures.

BlogPost:
  columns:
    title: string(255)
    body: clob
  relations:
    Tags:
      class: Tag
      refClass: BlogPostTag
      foreignAlias: BlogPosts

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

Tag:
  columns:
    name: string(255)

Load the below data fixtures in to data/fixtures/data.yml

BlogPost:
  BlogPost_1:
    title: Test Blog Post
    body: This is the body of the test blog post
    Tags: [test, php, doctrine, orm]

Tag:
  test:
    name: test
  php:
    name: php
  doctrine:
    name: doctrine
  orm:
    name: orm

Rebuild the database again and run a DQL query to see the loaded data.

$ ./symfony doctrine:build-all-reload

Now inspect the data with another DQL query that fetches all BlogPost records and the related Tags

$ ./symfony doctrine:dql "FROM BlogPost p, p.Tags"
>> doctrine  executing dql query
DQL: FROM BlogPost p, p.Tags
found 1 results
-
  id: '1'
  title: 'Test Blog Post'
  body: 'This is the body of the test blog post'
  Tags:
    -
      id: '1'
      name: test
    -
      id: '2'
      name: php
    -
      id: '3'
      name: doctrine
    -
      id: '4'
      name: orm

Inline

Doctrine offers the ability to specify data fixture relationships inline like below.

User:
  User_1:
    username: jwage
    password: changeme
    Phonenumbers:
      Phonenumber_1:
        6155139185

BlogPost:
  BlogPost_1:
    title: Test Blog Post
    body: This is the body of the test blog post
    Tags:
      test:
        name: test
      php:
        name: php
      doctrine:
        name: doctrine
      orm:
        name: orm

This alternative syntax can greatly reduce the length and complexity of your data fixtures.

More can be read about data fixtures in the Doctrine Manual here.