Jobeet - Day 3: The Data Model

This post was published as part of the symfony 2008 advent calendar. As this tutorial might have been updated since then, you are advised to read the last version from the symfony 1.2 documentation (for Propel or Doctrine).

Previously on Jobeet

Those of you itching to open your text editor and lay down some PHP will be happy to know today's tutorial will get us into some development. We will define the Jobeet data model, use an ORM to interact with the database, and build the first module of our application. But as symfony does a lot of the work for us, we will have a fully functional web module without writing too much PHP code.

The Relational Model

The user stories we have written yesterday describe the main objects of our project: jobs, affiliates, and categories. Here is the corresponding entity relationship diagram:

Entity relationship diagram

In addition to the columns described in the stories, we have also added a created_at field to some tables. Symfony recognizes such fields and sets the value to the current system time when a record is created. That's the same for updated_at fields: Their value is set to the system time whenever the record is updated.

The Schema

To store the jobs, affiliates, and categories, we obviously need a relational database.

But as symfony is an Object-Oriented framework, we like to manipulate objects whenever we can. For example, instead of writing SQL statements to retrieve records from the database, we'd rather prefer to use objects.

The relational database information must be mapped to an object model. This can be done with an ORM tool and thankfully, symfony comes bundled with two of them: Propel and Doctrine. In this tutorial, we will use Propel.

The ORM needs a description of the tables and their relationships to create the related classes. There are two ways to create this description schema: by introspecting an existing database or by creating it by hand.

Some tools allow you to build a database graphically (for instance Fabforce's Dbdesigner) and generate directly a schema.xml (with DB Designer 4 TO Propel Schema Converter).

As the database does not exist yet and as we want to keep Jobeet database agnostic, let's create the schema file by hand by editing the empty config/schema.yml file:

# config/schema.yml
    id:           ~
    name:         { type: varchar(255), required: true }
    id:           ~
    category_id:  { type: integer, foreignTable: jobeet_category, foreignReference: id, required: true }
    type:         { type: varchar(255) }
    company:      { type: varchar(255), required: true }
    logo:         { type: varchar(255) }
    url:          { type: varchar(255) }
    position:     { type: varchar(255), required: true }
    location:     { type: varchar(255), required: true }
    description:  { type: longvarchar, required: true }
    how_to_apply: { type: longvarchar, required: true }
    token:        { type: varchar(255), required: true, index: unique }
    is_public:    { type: boolean, required: true, default: 1 }
    is_activated: { type: boolean, required: true, default: 0 }
    email:        { type: varchar(255), required: true }
    expires_at:   { type: timestamp, required: true }
    created_at:   ~
    updated_at:   ~
    id:           ~
    url:          { type: varchar(255), required: true }
    email:        { type: varchar(255), required: true, index: unique }
    token:        { type: varchar(255), required: true }
    is_active:    { type: boolean, required: true, default: 0 }
    created_at:   ~
    category_id:  { type: integer, foreignTable: jobeet_category, foreignReference: id, required: true, primaryKey: true, onDelete: cascade }
    affiliate_id: { type: integer, foreignTable: jobeet_affiliate, foreignReference: id, required: true, primaryKey: true, onDelete: cascade }

If you have decided to create the tables by writing SQL statements, you can generate the corresponding schema.yml configuration file by running the propel:build-schema task.

The schema is the direct translation of the entity relationship diagram in the YAML format.

The schema.yml file contains the description of all tables and their columns. Each column is described with the following information:

  • type: The column type (boolean, tinyint, smallint, integer, bigint, double, float, real, decimal, char, varchar(size), longvarchar, date, time, timestamp, blob, and clob)
  • required: Set it to true if you want the column to be required
  • index: Set it to true if you want to create an index for the column or to unique if you want a unique index to be created on the column.

For columns set to ~ (id, created_at, and updated_at), symfony will guess the best configuration (primary key for id and timestamp for created_at and updated_at).

The onDelete attribute defines the ON DELETE behavior of foreign keys, and Propel supports CASCADE, SETNULL, and RESTRICT. For instance, when a job record is deleted, all the jobeet_category_affiliate related records will be automatically deleted by the database or by Propel if the underlying engine does not support this functionality.

The Database

The symfony framework supports all PDO-supported databases (MySQL, PostgreSQL, SQLite, Oracle, MSSQL, ...). PDO is the database abstraction layer bundled with PHP.

Let's use MySQL for this tutorial:

$ mysqladmin -uroot -pmYsEcret create jobeet

Feel free to choose another database engine if you want. It won't be difficult to adapt the code we will write as we will use the ORM will write the SQL for us.

We need to tell symfony to use this database for the Jobeet project:

$ php symfony configure:database "mysql:host=localhost;dbname=jobeet" root mYsEcret

The configure:database task takes three arguments: the PDO DSN, the username, and the password to access the database. If you don't have any password on your development server, just omit the third argument.

The configure:database task stores the database configuration into the config/databases.yml configuration file. Instead of using the task, you can edit this file by hand.


Thanks to the database description from the schema.yml file, we can use some Propel built-in tasks to generate the SQL statements needed to create the database tables:

$ php symfony propel:build-sql

The propel:build-sql task generates SQL statements in the data/sql directory, optimized for the database engine we have configured:

# snippet from data/sql/lib.model.schema.sql
CREATE TABLE `jobeet_category`
  `name` VARCHAR(255)  NOT NULL,
  PRIMARY KEY (`id`)

To actually create the tables in the database, you need to run the propel:insert-sql task:

$ php symfony propel:insert-sql

As the task drops the current tables before re-creating them, you are required to confirm the operation. You can also add the --no-confirmation option to bypass the question, which is useful if you want to run the task from within a non-interactive batch:

$ php symfony propel:insert-sql --no-confirmation

As for any command line tool, symfony tasks can take arguments and options. Each task comes with a built-in help message that can be displayed by running the help task:

$ php symfony help propel:insert-sql

The help message lists all the possible arguments and options, gives the default values for each of them, and provides some useful usage examples.

The ORM also generates PHP classes that map table records to objects:

$ php symfony propel:build-model

The propel:build-model task generates PHP files in the lib/model directory that can be used to interact with the database.

By browsing the generated files, you have probably noticed that Propel generates four classes per table. For the jobeet_job table:

  • JobeetJob: An object of this class represents a single record of the jobeet_job table. The class is empty by default.
  • BaseJobeetJob: The parent class of JobeetJob. Each time you run propel:build-model, this class is overridden, so all customizations must be done in the JobeetJob class.

  • JobeetJobPeer: The class defines static methods that mostly return collections of JobeetJob objects. The class is empty by default.

  • BaseJobeetJobPeer: The parent class of JobeetJobPeer. Each time you run propel:build-model, this class is overridden, so all customizations must be done in the JobeetJobPeer class.

The column values of a record can be manipulated with a model object by using some accessors (get*() methods) and mutators (set*() methods):

$job = new JobeetJob();
$job->setPosition('Web developer');
echo $job->getPosition();

You can also define foreign keys directly by linking objects together:

$category = new JobeetCategory();
$job = new JobeetJob();

The propel:build-all task is a shortcut for the tasks we have run in this section and some more. So, run this task now to generate forms and validators for the Jobeet model classes:

$ php symfony propel:build-all

You will see validators in action at the end of the day and forms will be explained in great details on day 10.

The propel:build-all-load task is a shortcut for the propel:build-all task followed by the propel:data-load task.

As you will see later on, symfony autoloads PHP classes for you, which means that you never need to use a require in your code. It is one of the numerous things that symfony automates for the developer but there is one downside: whenever you add a new class, you need to clear the symfony cache. As the propel:build-model has created a lot of new classes, let's clear the cache:

 $ php symfony cache:clear

A symfony task is made of a namespace and a task name. Each one can be shortened as much as there is no ambiguity with other tasks. So, the following command is equivalent to cache:clear:

$ php symfony cc

The Initial Data

The tables have been created in the database but there is no data in them. For any web application, there are three types of data:

  • Initial data: Initial data are needed for the application to work. For example, Jobeet needs some initial categories. If not, nobody will be able to submit a job. We also need an admin user to be able to login to the backend.

  • Test data: Test data are needed for the application to be tested. As a developer, you will write tests to ensure that Jobeet behaves as described in the user stories, and the best way is to write automated tests. So, each time you run your tests, you need a clean database with some fresh data to test on.

  • User data: User data are created by the users during the normal life of the application.

Each time symfony creates the tables in the database, all the data are lost. To populate the database with the initial data we could create a PHP script, or execute some SQL with the mysql program. But as the need is quite common, there is a better way with symfony: create YAML files in the data/fixtures/ directory and use the propel:data-load task to load them into the database:

# data/fixtures/010_categories.yml
  design:        { name: Design }
  programming:   { name: Programming }
  manager:       { name: Manager }
  administrator: { name: Administrator }
# data/fixtures/020_jobs.yml
    category_id:  programming
    type:         full-time
    company:      Sensio Labs
    logo:         /uploads/jobs/sensio_labs.png
    position:     Web Developer
    location:     Paris, France
    description:  |
      You've already developed websites with symfony and you want to work
      with Open-Source technologies. You have a minimum of 3 years
      experience in web development with PHP or Java and you wish to
      participate to development of Web 2.0 sites using the best
      frameworks available.
    how_to_apply: |
      Send your resume to fabien.potencier [at]
    is_public:    true
    is_activated: true
    token:        job_sensio_labs
    email:        [email protected]
    expires_at:   2010-10-10
    category_id:  design
    type:         part-time
    company:      Extreme Sensio
    logo:         /uploads/jobs/extreme_sensio.png
    position:     Web Designer
    location:     Paris, France
    description:  |
      Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do
      eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut
      enim ad minim veniam, quis nostrud exercitation ullamco laboris
      nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor
      in reprehenderit in.
      Voluptate velit esse cillum dolore eu fugiat nulla pariatur.
      Excepteur sint occaecat cupidatat non proident, sunt in culpa
      qui officia deserunt mollit anim id est laborum.
    how_to_apply: |
      Send your resume to fabien.potencier [at]
    is_public:    true
    is_activated: true
    token:        job_extreme_sensio
    email:        [email protected]
    expires_at:   2010-10-10

A fixtures file is written in YAML, and defines model objects, labelled with a unique name. This label is of great use to link related objects without having to define primary keys (which are often auto-incremented and cannot be set). For instance, the job_sensio_labs job category is programming, which is the label given to the 'Programming' category.

A fixture file can contain object from one or several models.

Notice the numbers prefixing the filenames. This is a simple way to control the order of data loading. Later in the project, if we need to insert some new fixture file, it will be easy as we have some free numbers between existing ones.

In a fixture file, you don't need to define all columns values. If not, symfony will use the default value defined in the database schema. And as symfony uses Propel to load the data into the database, all the built-in behaviors (like setting the created_at or updated_at columns), or the custom behaviors you might have added to the model classes are activated.

Loading the initial data into the database is as simple as running the propel:data-load task:

$ php symfony propel:data-load

See it in Action in the Browser

We have used the command line interface a lot but that's not really exciting, especially for a web project. We now have everything we need to create Web pages that interact with the database.

Let's see how to display the list of jobs, how to edit an existing job, and how to delete a job. As explained during day 1, a symfony project is made of applications. Each application is made of modules. A module is a self-contained set of PHP code that represents a feature of the application (the API module for example), or a set of manipulations the user can do on a model object (a job module for example).

Symfony is able to automatically generate a module for a given model that provides basic manipulation features:

$ php symfony propel:generate-module --with-show --non-verbose-templates frontend job JobeetJob

The propel:generate-module generates a job module in the frontend application for the JobeetJob model. As with most symfony tasks, some files and directories have been created for you under the apps/frontend/modules/job directory:

Directory Description
actions/ The module actions
templates/ The module templates

The actions/actions.class.php file defines all the available actions for the job module:

Action name Description
index Displays the records of the table
show Displays the fields of a given record
new Displays a form to create a new record
create Creates a new record
edit Displays a form to create edit an existing record
update Updates a record according to the user submitted values
delete Deletes a given record from the table

You can now test the job module in a browser:


Job module

If you try to edit a job, you will have an exception because symfony needs a text representation of a category. A PHP object representation can be defined with the PHP __toString() magic method. The text representation of a category record should be defined in the JobeetCategory model class:

// lib/model/JobeetCategory.php
class JobeetCategory extends BaseJobeetCategory
  public function __toString()
    return $this->getName();

Now each time symfony needs a text representation of a category, it calls the __toString() method which returns the category name. As we will need a text representation of all model classes at one point or another, let's define a __toString() method for every model class:

// lib/model/JobeetJob.php
class JobeetJob extends BaseJobeetJob
  public function __toString()
    return sprintf('%s at %s (%s)', $this->getPosition(), $this->getCompany(), $this->getLocation());
// lib/model/JobeetAffiliate.php
class JobeetAffiliate extends BaseJobeetAffiliate
  public function __toString()
    return $this->getUrl();

You can now create and edit jobs. Try to leave a required field blank, or try to enter an invalid date. That's right, symfony has created basic validation rules by introspecting the database schema.


See you Tomorrow

That's all for today. I have warned you in the introduction. Today, we have barely written PHP code but we have a working web module for the job model, ready to be tweaked and customized. Remember, no PHP code also means no bugs!

If you still have some energy left, feel free to read the generated code for the module and the model and try to understand how it works. It not, don't worry and sleep well, as tomorrow, we will talk about one of the most used paradigm in web frameworks, the MVC design pattern.

The code written today is available on the Jobeet SVN repository under the release_day_03 tag (

Help the Symfony project!

As with any Open-Source project, contributing code or documentation is the most common way to help, but we also have a wide range of sponsoring opportunities.


\o/ symfony 1.2 just rocks
Be carefull when you copy&paste yml code in your editor especially the schema.yml, the "new line" character is copied but became invisible in text editor (eclipse & pspad). Then I had error when using command like "symfony propel:build-model". I had to delete this character by using the "display/hide characters like space, line's end, ..."
It is this character which make problems : ➥ , don't copy&paste it, even it seems not to be copied.
I have some trouble using the propel:build-sql command. After correcting the special characters in the .yml file, I still get an error :

[phing] Error reading project file [wrapped: PDOTask depends on PDO feature being included in php]

I just don't get it. Maybe it is because I work on a Wamp server (yes, I know... I just don't have the choice). My actual configuration is Php 5.2.6 with all php_pdo_foo extensions enabled.

Note : the propel:schema-to-xml command is generating a correct xml file (or so it seems). I reverted it to a yml file with the propel:schema-to-yml command and tried to build it again. Didn't work either.

Does anybody has an idea? Thanks in advance!
PA: I think you have to activate PDO extension in php settings (php.ini).

You can check if you have this extension enabled with phpinfo()
Thanks Silvyo. As I just said, these extensions are all activated.

I just checked my phpinfo, and I can confirm that.
Good tutorial! Little bit disappointed with using Propel instead Doctrine in Jobeet; anyway, I created a separate project and planned to parallelly execute all steps for each tutorial using Doctrine. Time to dig into Doctrine book.

I am just wondering about complete development environment (OS, web server, any MySql IDE, SVN client, text editor and this dark theme) you have for preparing these tutorials.

Why did you choose Propel and not Doctrine for this Project?
Billy Galinos - maybe because symfony is still more propel oriented? See the symfony book - there is nothing about Doctrine, only in separated "book" ;)
@Billy Galinos: We had to make a choice. But let me say it once more: Propel and Doctrine are both supported in symfony 1.2 and there are on par as far as features are concerned.

We are working on a way to provide the Jobeet tutorial for Doctrine too, without too much additional work.

Well i'm pleased its in Propel :-)
Just wanted to leave a note and say that I too was a little disappointed to see that Propel was selected for this series instead of Doctrine. Nothing against Propel, it's just that we do not have as many great tutorials for Doctrine so I was looking forward to seeing it used on a full project such as this.
Hm-m, why Propel and not Doctrine? :-/
yea i'm little bit disappointed as well, I was hoping we would use Doctrine :) But we should respect that, propel is just easier.. for newcomers :)
Just 2 questions :
- no 'enum' type in schema.yml, rather sad, no ?
- propel:build-model says (last output on cli) "autoload reloading autoloading". Was just wondering what this might mean (thought it cleared the autoload cache, but no since we had to clear it anyway...) ?
Thx again for the great tuto
Hi. I seem to have an issue. when "php symfony propel:insert-sql" I get
"build-propel.xml:275:1: [wrapped: could not find driver]"
I have symfony 1.0 on the same box and my other projects are ok. Not sure what logs it refers too eight.
Any help will be appreciated.
Hi there!

When I check "http://jobeet.localhost/frontend_dev.php/job" I get a very awfull styleless page... it doesn't look like a form.

The only thing I did in a different way was install symfony outside from jobeet project, but I configured well on httpd.conf and /sf directory is reachable.

any idea about my mistake?
Ismael - it is meant to be styleless for now, though, luckly not for too long.

As it has been written on day 1 - there will be competition about Jobeet design from 4. to 21. of December.
@ Ismael
Have you tried to click on the numbers under the 'id' column? If you, you will see that it looks like a form and near exactly as shown on day 3 tutorial.
@Billy Galinos
Yes, I tried to click on numbers and the edit 'form' is not a form, it's plain, styless as well.

That would explain everything, so it's normal the plain edit form and datagrid?

Thanks both
oops! I was wrong... now I can view the edit form... and it's a real form.

The available jobs datagrid is still plain and awful, not like figure on tutorial, but message from 'And' would explain that.
There is a difference between the schema and the fixtures, in the database schema there should not be an "activated" field but a "is_activated" field for the Job table. :)
I was hoping for doctrine's day in the sun as well ;)
Why don't we use extended schema syntax?
It's natively supported from Symfony 1.1 and also it's closer to Doctrine syntax.

Let's move forward together with new Symfony features!
Have the same error:
[phing] Error reading project file [wrapped: PDOTask depends on PDO feature being included in php]

while try to execute php symfony propel:build-sql

PHP 5.2.6 and PDO enabled Have anyone some ideas?
@PA & Stanley : maybe u're using wamp like me.
You've 2 php.ini files, the one for apache and the other for php command line.
So you've to enable it twice :
I'm going to stick up for Propel too. Doctrine looks great, but folks - this is a tutorial intended to demonstrate *symfony*, not a particular ORM. Propel was the right choice as symfony has historically been linked to Propel, which reduces the learning curve for long-term users.
@Lilian & Stanley: ok, both php.ini were correctly initialized. To get php commands to work, I first copied the php.exe file in my windows/system32 folder but the result was that PDO was not implemented anymore (to check, use the php -r phpinfo() command line, do not trust the textual phpinfo() from the wamp admin [Thanks Somb for the tip]). I just deleted that php.exe and now use the one in my wamp/bin/php5.2.6 folder

Eventually, PDO was not an issue any more but a new XSLT bug appeared because in wamp, the php_xslt.dll is not packed. I downloaded it, added the extension in both my php.ini files and now it works perfectly.

I hope it will help the wamp users.

Thanks to anyone who tried to help.
Is it just me or did the schema change over night?
@Kevin: Based on user feedback, we have slightly changed the database schema. The changes are explained in day 4.
I think it would be better if all documentation will use the alternative syntax specified in the cookbook
2 Lilian:
Oh, thank you! :-) I really have two php.ini's one for my apache and second for php that I use in console enveronment.
I have the Ilia's problem.

"Some problems occurred when executing the task:
build-propel.xml:275:1: [wrapped: could not find driver]"

I am sorry but have too:

Execution of target "insert-sql" failed for the following reason: /var/www/jobeet/lib/vendor/symfony/lib/plugins/sfPropelPlugin/lib/vendor/propel-generator/build-propel.xml:275:1: [wrapped: could not find driver]

I have empty root password for MySQL. Can this be the reason?
Error message has text:

"Read the logs to fix them"

But I can't find it. My log directory is empty.
@Vic: A blank root password isn't a problem.
I am sorry. I recompile my php with:

and all done.
@Vic : How did you do that?
@All: Jobeet for Doctrine is now available:
Yay for Doctrine!

(Does Day 1 need to be modified to tell us how to load the plugin for doctrine, which I don't seem to have on day three?)

There are no tasks defined in the "doctrine" namespace.
@Bill Shirley:
You have to enable doctrine plugin, take a look at

Good luck!
(Thanks Jan. I fiddled a bit and got the doctrine commands working.)

When it came to displaying the page "at the end of day 3" it seemed I was missing some rss.

There are links in the web folder named sfPropelPlugin and sfProtoculousPlugin, but I suspect the page is referencing a sfDoctrinePlugin sym-link that hasn't been created.

Did I miss some configuration?

I tried to manually add a similar link to the Doctrine plugin, but that didn't seem to help.

I had to mod the config/ProjectConfiguration.class.php in the following manner:

// $this->enableAllPluginsExcept(array('sfDoctrinePlugin', 'sfCompat10Plugin'));
PHP (php_pdo_mysql.dll) does an illegal operation when I do propel:data-load. Already tried multiple php and mysql versions, and run out of ideas.
Checked out the svn day3 code, does the same.
Anyone else having the same problem, or maybe a clue?

Comments are closed.

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