Doctrine2 ninja with the HIDDEN keyword

Doctrine2 ninja with the HIDDEN keyword

I recently came to find a nice hidden feature of Doctrine2, the well named HIDDEN keyword.
I first needed to find the feature because I wanted to use functions in an order by clause.

The problem

Let’s say you want to get your user ordered by sum of comments and likes. You will write this kind of DQL:

SELECT u
FROM BCCModelBundle:User u
ORDER BY u.commentNumber + u.likeNumber

Sadly, for interoperability reasons, such a statement is not possible using doctrine2. You cannot have an arithmetic expression in the ORDER BY clause. This goes also with the GROUP BY

The solution: the HIDDEN keyword

Hopefully, you can use the HIDDEN keyword. This idea is to declare a new field in the SELECT clause and mark it hidden to avoid the ORM fetching it:

SELECT
	u,
	u.commentNumber + u.likeNumber AS HIDDEN score
FROM BCCModelBundle:User u
ORDER BY score

Now, you have a score field which is compatible with the ORDER BY clause. I am sure you will find it very useful, especially when you will starting to have heavy DQL with complex ORDER BY clause.

EDIT: Please note that this feature is available on Doctrine 2.2+.

Create your own constraint validator in symfony2 : A Doctrine unique validator

Create your own constraint validator in symfony2 : A Doctrine unique validator

The new symfony2 form framework is definitely one of my favorites. It is quite simple, and very powerful and straightforward. Moreover it is very extensible.

One of the most common tasks you need to perform when validating a form is to check the unity of a field in your database. Unfortunately, there is no build-in validator coupled with doctrine. Here is my implementation.

Create the unique validator

The first thing to do when implementing a new validator is to create the constraint class:
[cc lang=”php”]
namespace MyAppMyBundleValidator;

use SymfonyComponentValidatorConstraint;

class Unique extends Constraint
{
public $message = ‘This value is already used’;
public $entity;
public $property;

public function validatedBy()
{
return ‘validator.unique’;
}

public function requiredOptions()
{
return array(‘entity’, ‘property’);
}

public function targets()
{
return self::PROPERTY_CONSTRAINT;
}
}
[/cc]

As you can see, this definition is pretty simple. We define the error message and two more required options which are the Entity that doctrine with check and its property. The validatedBy() method returns the service’s name that is in charge to validate the constraint (we will declare it just after). Finally, the targets() function limits the use of the constraint only to class’ property.

Now that our constraint is set, let’s create the constraint’s validator:
[cc lang=”php”]
namespace MyAppMyBundleValidator;

use DoctrineORMEntityManager;
use SymfonyComponentValidatorConstraint;
use SymfonyComponentValidatorConstraintValidator;

class UniqueValidator extends ConstraintValidator
{
private $entityManager;

public function __construct(EntityManager $entityManager)
{
$this->entityManager = $entityManager;
}

public function isValid($value, Constraint $constraint) {
// try to get one entity that matches the constraint
$user = $this->entityManager->getRepository($constraint->entity)
->findOneBy(array($constraint->property => $value));
// if there is already an entity
if($user != null){
// the constraint does not pass
$this->setMessage($constraint->message);
return false;
}
// the constraint passes
return true;
}
}
[/cc]

The main function here is isValid(). It checks the validity of the constraint and consequently returns true/false and may add some error message. Here our job is to check is the value is already used in the database. The simplest way to do that is to try to get one matching entity. If Doctrine finds an entity the constraints does not pass, if there is no entity, the value can be used and the constraint passes.

One thing to know is that the Unique constraint instance is pass to the isValid() function, so that we can get the entity et property values.

Finally, our UniqueValidator has a dependency to the Doctrine manager. We will need to declare it in our services:

[cc lang=”yaml”]
# MyApp/MyBundle/Resources/config/services.yml
parameters:
my.validator.unique.class: MyAppMyBundleValidatorUniqueValidator

services:
my.validator.unique:
class: %my.validator.unique.class%
arguments: [@doctrine.orm.entity_manager]
tags:
– { name: validator.constraint_validator, alias: validator.unique }
[/cc]

We defined here the way to instantiate our UniqueValidator with its dependencies (arguments line). The tag line is very important. The name specifies that it is load as a constraint validator, and the alias name is used to link the constraint validator to its constraint (remember the Unique::validatedBy() function).
One last thing is to load our services.yml file. We can do it in the dependency injection configuration :
[cc lang=”php”]
load(‘services.yml’);
}

public function getAlias() {
return ‘my’;
}
}
[/cc]

The load() function is launched at the configuration of the container. Here we simple retrieve the services.yml file and load it to the current configuration.

That’s it you know have a working constraint that you can use to check the unity of a field at form’s submission. Don’t forget to set the entity and property values.

Support annotation

Actually I prefer setting my constraints using annotations. To do so, we will need some more work.

Let’s say we want to use our constraint like that:
[cc lang=”php”]
/**
* @orm:Entity
*/
class User{
/**
* @orm:Column(length=255, unique=”TRUE”)
* @myvalidation:Unique(entity=”MyBundle:User”, property=”username”)
*/
protected $username;

//…
}
[/cc]

It won’t work right away. This is because the myvalidation is a shortcut to a namespace (just like validation or orm). Because we did not declare this shortcut, symfony cannot find the Unique class and simply ignores it.

To declare the myvalidation shortcut you can do it directly in the app/config/config.yml. I chose to do it in the bundle because I don’t like to play so much with the app configuration. One of the benefits of bundle is that they decoupled the application, so we don’t want our general configuration to become dependent to our bundle.

To declare the shortcut in our bundle we can do it in the dependency injection configuration:
[cc lang=”php”]
load(‘services.yml’);

// get the existing registered namespaces for validator annotations
$namespaces = $container->getParameter(‘validator.annotations.namespaces’);
// add our namespace under the alias myvalidation
$namespaces[‘myvalidation’] = ‘MyApp\MyBundle\Validator\’;
// save it
$container->setParameter(‘validator.annotations.namespaces’, $namespaces);
}

public function getAlias() {
return ‘my’;
}
}
[/cc]

What we need to do is to retrieve the namespace aliases of the validators and then add our alias. Don’t forget the trailing backslashes.

And voila! Our Unique constraint works also with annotations.

I hope that this tutorial will help you to build new powerful constraints and share them to the community.

How to use filters on custom fields with symfony 1.4

How to use filters on custom fields with symfony 1.4

I recently continued to use my particular relationship that embeds some properties. This time I wanted to add some filters to my student request using the pre-generated filter used in the backend.

Here again, is our example:

N to N relationship with properties
N to N relationship with properties

And the pre-coded filter is here:
[php]
// lib/filter/doctrine/StudentFormFilter.class.php
class StudentFormFilter extends BaseStudentFormFilter
{
public function configure()
{
}
}
[/php]

Here the filter already includes fields for every field on Student objects (id and name). It is a regular form, so you can override the predefined widgets and validator or even unset some of them. Let’s unset the id:

[php]
// lib/filter/doctrine/StudentFormFilter.class.php
class StudentFormFilter extends BaseStudentFormFilter
{
public function configure(){
unset($this[‘id’]);
}
}
[/php]

Now we can use our filter like this in our controller:
[php]
// app/frontend/module/student/actions/actions.class.php
class studentActions extends sfActions {
public function executeIndex(sfWebRequest $request) {
//create a default query
$query = Doctrine::getTable(‘Student’)->getQuery();

//obtain the filter values that could have been submited
$filterValues = $request->getParameter(‘student_filters’);
//create the StudentFormFilter using the values, and giving it the query
$this->formFilter = new StudentFormFilter($filterValues);
$this->formFilter->setQuery($query);
//if the form have been submited
if($filterValues){
//bind the values
$this->formFilter->bind($filterValues);
//if valid, specialise the request using the form
if($this->formFilter->isValid())
$query = $this->formFilter->getQuery();
}

//finally get the students
$this->students = $query->execute();
}
}
[/php]

This code could seem a little weird but it is finally all you need to know in order to make a basic use of the auto generated filters. Thus, your template can look like this:

[php]
<?php echo form_tag(url_for(‘student_index’)) ; ?>
<table>
<?php echo $formFilter ; ?>
</table>
<input type="submit"/>
<?php echo ‘</form>’ ?>
// …
<ul>
<?php foreach($students as $student): ?>
<li><?php echo $student->getName(); ?></li>
<?php endforeach; ?>
</ul>
[/php]

Ok, everything is working quite well. But, let’s getting our filter trickier. I want to add a filter that permits me to select students that have a specific enrolment date.

First of all, we need to add a widget and a validator:
[php]
// lib/filter/doctrine/StudentFormFilter.class.php
class StudentFormFilter extends BaseStudentFormFilter
{
public function configure()
unset($this[‘id’]);

$this->setWidget(‘EnrolmentDate’, new sfWidgetFormFilterDate( array(
‘from_date’ => new sfWidgetFormDate(),
‘to_date’ => new sfWidgetFormDate(),
‘with_empty’ => false)));
$this->setValidator(‘EnrolmentDate’, new sfValidatorDateRange(array(
‘required’ => false,
‘from_date’ => new sfValidatorDateTime(array(
‘required’ => false,
‘datetime_output’ => ‘Y-m-d 00:00:00’)
),
‘to_date’ => new sfValidatorDateTime(array(
‘required’ => false,
‘datetime_output’ => ‘Y-m-d 23:59:59’)))));
}
}
[/php]

Here, our form will display properly, but the getQuery method used in the will simply ignore our new EnrolmentDate field.

Now the things are getting really uncommon. Symfony is not really built for this kind of behavior, and we will need to override some behavior in our StudentFormFilter to get thing to work. In order to do that I read the sfFormFilterDOctrine.class.php file and more specifically the doBuildQuery function where the request is built. Some of the things I’ll do are a bit ugly but it is the price to pay if we want not to spread our modifications into the core framework, thus I managed to keep everything in the StudentFormFilter.class.php file. This way our ugliness remains contained into a single file and does not impact the framework. So let’s go for it!

First of all we need to add the EnrolmentDate as a new field of the form, to do that we need to override the getFields function:
[php]
// lib/filter/doctrine/StudentFormFilter.class.php
class StudentFormFilter extends BaseStudentFormFilter
{
// …

public function getFields() {
return array_merge(parrent::getFields(), array(‘EnrolmentDate’=>’EnrolmentDate’));
}
}
[/php]

The very important thing here is to know that I added a field EnrolmentDate of type EnrolmentDate. It will be crucial later.

Also, the doBuildQuery method will get the Student table and check for the presence of the field EnrolmentDate. The workaround here is to make a stub table and override the getTable function in order to return this stub table.

[php]
// lib/filter/doctrine/StudentFormFilter.class.php
class StudentFormFilter extends BaseStudentFormFilter
{
// …

public function getTable() {
return new StudentStubTable();
}
}

// my stub table
class StudentStubTable extends StudentTable{
public function hasField($field) {
if($field == ‘EnrolmentDate’)
return true;
else
return parent::hasField($field);
}
public function getFieldName($field){
if($field == ‘EnrolmentDate’)
return ‘EnrolmentDate’;
else
return parent::getFieldName($field);
}
}
[/php]

As you can see, two methods need to be overridden in my StudentStubTable class. Those two functions are used by the doBuildQuery function. Here i make the field EnrolmentDate exist and create his fieldName in the table is EnrolmentDate.

From here, all we need to know is that the doBuildQuery function will look for an addEnrolmentDateQuery function in our StudentFormFilter class. This function is called based on the type of the fields that I defined earlier in the getFields method. This call is made by the concatenation of ‘add’ + type of the field + ‘Query’.

So, let’s code the function and make it do our join with the Enrolment table.

[php]
// lib/filter/doctrine/StudentFormFilter.class.php
class StudentFormFilter extends BaseStudentFormFilter
{
// …

public function addEnrolmentDateQuery(Doctrine_Query $query, $field, $values) {
//add our join part
$query->leftJoin(sprintf(‘%s.Enrolment enrolment’,$query->getRootAlias()));

//set the where part depending on the value
if (isset($values[‘is_empty’]) && $values[‘is_empty’])
{
$query->addWhere(‘enrolment.EnrolmentDate IS NULL’);
}
else
{
if (null !== $values[‘from’] && null !== $values[‘to’])
{
$query->andWhere(‘enrolment.EnrolmentDate >= ?’, $values[‘from’]);
$query->andWhere(‘enrolment.EnrolmentDate <= ?’, $values[‘to’]);
}
else if (null !== $values[‘from’])
{
$query->andWhere(‘enrolment.EnrolmentDate >= ?’, $values[‘from’]);
}
else if (null !== $values[‘to’])
{
$query->andWhere(‘enrolment.EnrolmentDate <= ?’, $values[‘to’]);
}
}
}
}
[/php]

In this method, we retrieve the query as a parameter and we just have to build our method based on the values we receive. Everything here is about building a proper DQL query.

Now we have a nice filter that can filter on our property embedded into a relationship.

Here is the final code:

[php]
// lib/filter/doctrine/StudentFormFilter.class.php
class StudentFormFilter extends BaseStudentFormFilter
{
public function configure()
unset($this[‘id’]);

$this->setWidget(‘EnrolmentDate’, new sfWidgetFormFilterDate( array(
‘from_date’ => new sfWidgetFormDate(),
‘to_date’ => new sfWidgetFormDate(),
‘with_empty’ => false)));
$this->setValidator(‘EnrolmentDate’, new sfValidatorDateRange(array(
‘required’ => false,
‘from_date’ => new sfValidatorDateTime(array(
‘required’ => false,
‘datetime_output’ => ‘Y-m-d 00:00:00’)
),
‘to_date’ => new sfValidatorDateTime(array(
‘required’ => false,
‘datetime_output’ => ‘Y-m-d 23:59:59’)))));
}

public function addEnrolmentDateQuery(Doctrine_Query $query, $field, $values) {
//add our join part
$query->leftJoin(sprintf(‘%s.Enrolment enrolment’,$query->getRootAlias()));

//set the where part depending on the value
if (isset($values[‘is_empty’]) && $values[‘is_empty’])
{
$query->addWhere(‘enrolment.EnrolmentDate IS NULL’);
}
else
{
if (null !== $values[‘from’] && null !== $values[‘to’])
{
$query->andWhere(‘enrolment.EnrolmentDate >= ?’, $values[‘from’]);
$query->andWhere(‘enrolment.EnrolmentDate <= ?’, $values[‘to’]);
}
else if (null !== $values[‘from’])
{
$query->andWhere(‘enrolment.EnrolmentDate >= ?’, $values[‘from’]);
}
else if (null !== $values[‘to’])
{
$query->andWhere(‘enrolment.EnrolmentDate <= ?’, $values[‘to’]);
}
}
}

public function getFields() {
return array_merge(parrent::getFields(), array(‘EnrolmentDate’=>’EnrolmentDate’));
}

public function getTable() {
return new StudentStubTable();
}
}

// my stub table
class StudentStubTable extends StudentTable{
public function hasField($field) {
if($field == ‘EnrolmentDate’)
return true;
else
return parent::hasField($field);
}
public function getFieldName($field){
if($field == ‘EnrolmentDate’)
return ‘EnrolmentDate’;
else
return parent::getFieldName($field);
}
}
[/php]

If you have any remarks about my implementation (I am sure you have) don’t hesitate to tell me about, I think this code can still be improved.

Doctrine 1.2: Add properties to a relationship

Doctrine 1.2: Add properties to a relationship

I recentry had the need to had properties to a relationship using Doctrine. In fact this use case is not taken care of by the ORM (and ORMs in general) and there is no really good solution. Nevertheless, I found a not so bad way to implement such a pattern.

Here is what I’ll do:

As you can see, a very common school example.

We can already describe our objects and the N to N relationship.

[yml]
# Shema.yml
Student:
columns:
id:
type: integer
primary: true
autoincrement: true
name:
type: string
relations:
Courses:
class: Course
local: student_id
foreign: course_id
foreignAlias: Students
refClass: Enrolment

Course:
columns:
id:
type: integer
primary: true
autoincrement: true
name:
type: string

Enrolment:
columns:
student_id:
type: integer
primary: true
course_id:
type: integer
primary: true
[/yml]

In can now load the Doctrine task Doctrine_Core::generateModelsFromYaml to generate my model.

A good way to see I the generation went well is to open the generated base files and read the class comments. Let’s have a look:

[php]
/**
* BaseCourse
*
* @property integer $id
* @property string $name
* @property Doctrine_Collection $Students
*/
abstract class BaseCourse extends Doctrine_Record
{
// …
}

/**
* BaseEnrolment
*
* @property integer $student_id
* @property integer $course_id
*/
abstract class BaseEnrolment extends Doctrine_Record
{
// …
}

/**
* BaseStudent
*
* @property integer $id
* @property string $name
* @property Doctrine_Collection $Courses
*/
abstract class BaseStudent extends Doctrine_Record
{
// …
}
[/php]

You can see that all my classes have been load with the right properties. The interestig part here is to see that the student and Course classes is agnostic from the Enrolment class. Thus the couse already has a collection of students and the student a collection of courses.

So we have here a perfectly working N to N relationship. Doctrine takes care it self of the join operation when accessing the database.

Now, let’s had our properties on the enrolment relationship.

The idea here is to provide Doctrine a way to acces the enrolment class. The most simplier way to do that is to add the following relationships to the enrolment definition:

[yml]
Enrolment:
columns:
student_id:
type: integer
primary: true
course_id:
type: integer
primary: true
enrolementDate:
type: date
notnull: true
relations:
Course:
local: course_id
foreign: id
foreignAlias: Enrolments
Student:
local: student_id
foreign: id
foreignAlias: Enrolments
[/yml]

You can notice that I added the One to N relationships and the enrolmentDate property.

After a class generation we obtain :

[php]
/**
* BaseCourse
*
* @property integer $id
* @property string $name
* @property Doctrine_Collection $Students
* @property Doctrine_Collection $Enrolments
*/
abstract class BaseCourse extends Doctrine_Record
{
// …
}

/**
* BaseEnrolment
*
* @property integer $student_id
* @property integer $course_id
* @property date $enrolmentDate
* @property Course $Course
* @property Student $Student
*/
abstract class BaseEnrolment extends Doctrine_Record
{
// …
}

/**
* BaseStudent
*
* @property integer $id
* @property string $name
* @property Doctrine_Collection $Courses
* @property Doctrine_Collection $Enrolments
*/
abstract class BaseStudent extends Doctrine_Record
{
// …
}
[/php]

I now have the additionnal relationships and I can access my relationship class which has a new property: enrolementDate.

But, such a schem force us to change some way of working with the model.

Working with the model

The first thing that we need to keep in mind, is that if we add a student to a course or a course to a student by using the Course::Students and the Student::Courses collections, Doctrine will autogenerate the Enrolement object and save it. Thus we won’t have access to the enrolementDate field.

So instead of using the common way of adding element to a collection we should instantiate a Enrolement object, associate it with the course and student, and then save it. During this process we so have access to the enrolementDate.

[php]
$student = new Student();
$student->name = "Michel";
$student->save();

$course = new Course();
$course->name = "Programming 101";
$course->save();

//create the relationship
$enrolment = new Enrolment();
$enrolment->enrolmentDate = date(‘c’);
$enrolment->Course = $course;
$enrolment->Student = $student;
$enrolment->save();
[/php]

After that, I have a correct set of data in my database. I can also do some access:

[php]
//retrieve the courses
$courses = $student->Courses;

//retrieve the enrolments
$enrolments = $student->Enrolments;

//retrieve the course passing through and enrolment
$course = $student->Enrolments[0]->Course;
[/php]

The only matter is that I cannot access an enrolment given a student and a course.

Here is what I did to pass this issue. I simply added a function to my Student class that will generate the

[php]
class Student extends BaseStudent
{
public function getEnrolmentForCourse($course){
return Doctrine::getTable(‘Enrolment’)
->findOneByCourseIdAndStudentId(
$course->id,
$this->id
);
}
}
[/php]

As you can see, I simply use a magic function of doctrine. Even if the findOneByCourseIdAndStudentId function does not exist, Doctrine uses its name to generate the appropriate SQL query; I just need to fill the blanks with the ids of both objects.

Now I have a complete access to my enrolment object:
[php]
// find Michel
$student = Doctrine::getTable(‘Student’)->findOneByName(‘Michel’);
// take his first course
$course = $student->Courses[0];

// retrieve the enrolment data from it
$enrolment = $student->getEnrolmentForCourse($course);
[/php]

We saw here how to implement a full access to a relationship containing properties. It involves some schema description and a simple additional method in the classes’ code, but it imposes a specific way to create N to N relations. Thus it is not perfect; I hope this solution will help you working better with Doctrine.