« Back to list view

doctrine cheat sheet / ullright code conventions

Introduction

Doctrine provides many equal ways to do queries. To be consistent troughout ullright we aim to use only the following "styles".

 

Schema

Default path: config/doctrine/mySchema.yml

For plugins: plugins/myPlugin/config/doctrine/mySchema.yml

 

Example schema for a plugin

(Note the "package" path, which builds the models to plugins/myPlugin/lib/model/doctrine/)

 

UllUser:  
  package: ullCorePlugin.lib.model.doctrine
  columns:
    name:
      type: string(64)
    location_id:
      type: integer
    type:
      type: enum
      values: [dumb, bright]
      default: bright
      notnull: true
    description:
      type: string(4000) # results in a MySQL "text" field type
    birthday:
      type: date
    active:
      type: boolean
      default: true 
    updated_at:
      type: timestamp
    some_unique_stuff:
      type: integer
      notnull: true
  indexes:
    some_unique_stuff_index:
      fields: [some_unique_stuff]
      type: unique


Phonenumber:
  columns:
    number: 
      type: string(128)
    ull_user_id:
      type: integer
  relations:
    UllUser:
      # When a user is deleted all phonenumbers are deleted as well
      onDelete: CASCADE


#m:n unique
UllUserGroup:
  columns:
    ull_user_id:
      type: integer
      notnull: true
    ull_group_id:
      type: integer
      notnull: true  
  indexes:
    ull_user_group_unique:
      fields: [ 'ull_user_id', 'ull_group_id']
      type: unique

 Always assign a default value (true or false) to booleans whenever possible. Reason: if you set nothing, "null" is used which is not catched by $q->addWhere('my_boolean = ?', false)

String lengths with MySql

string(>=255) - TINYTEXT 256 bytes
string(>65536) - TEXT 65,535 bytes ~64kb
??? - MEDIUMTEXT 16,777,215 bytes ~16MB
clob - LONGTEXT 4,294,967,295 bytes ~4GB

Queries

Shortcuts using Doctrine::getTable()

Find all

$users = Doctrine::getTable('User')->findAll();
echo $users->getFirst()->username;

 

Find by Id

$user = Doctrine::getTable('User')->find(1);
echo $user->username;

 

Find by any field

$users = Doctrine::getTable('User')->findByFirstName('Klemens');
echo $users->getFirst()->username;

 

Find one by any field

$user = Doctrine::getTable('User')->findOneByFirstName('Klemens');
echo $user->username;

 

 

 

Note: "findByFirstName()" etc are magic methods.

Custom Finders

Are usually put into PluginUllXxxTable.class.php

If one of the global Doctrine::getTable() finders is overwritten, it must return a compatible output.

If cashing is used the convention is e.g. findOneByEmailCached()

 

Full DQL

 

Defining the query:

$q = new Doctrine_Query;
 
$q
  ->select('u.name, u.email')
  ->from('User u')
  ->where('u.country = ?', $country)
;
 
$users = $q->execute();

Index by:

->from('User u INDEXBY u.username');

 

Conditional Join

$q
  ->from('UllUser u')
  ->leftJoin('u.UllEntityGroup ep WITH ep.ull_group_id = ?', 123)
;

 

Order by with MySQL "IFNULL"

$q
  ->from('UllEntityGroup ep')
  // convert NULL to a high integer. therefore NULL entries are ordered last
  ->select('IFNULL(ep.position, 999999999) as postition_int');
  ->addOrderBy('postition_int asc')
;

 

Fetch as array:

$users = $q->fetchArray();

Fetch the first record only:

$user = $q->fetchOne();

Get value:

$name = $user->name;

Custom hydration option

$users = $q->execute(array(), Doctrine::HYDRATE_NONE)

Set current date

$user->date = new Doctrine_Expression('NOW()');

 Single scalar

      $q = new Doctrine_Query();
      $q
        ->select('name')
        ->from('UllDepartment')
        ->orderBy('name')
      ;
     
      $result = $q->execute(array(), Doctrine::HYDRATE_SINGLE_SCALAR);

array (size=3)
  0    (length=6)
  1    (length=5)
  2    (length=13)

Delete

Example for many to many:

      $q = new Doctrine_Query;
      $q
        ->delete('ParentChild pc')
        ->where('pc.parent_ull_user_id = ?', $parent_ull_user_id)
        ->addWhere('pc.child_ull_user_id = ?', $child_ull_user_id)
      ;
     
      $q->execute();

Hydration

You should always use array hydration when you only need data for access-only purposes, whereas you should use the
record hydration when you need to change the fetched data.

 

Debug query:

var_dump($q->getSql());
var_dump($q->getParams());

Update:

$q = new Doctrine_Query;
$q
  ->update('I18nMsgTransUnit t')
  ->set('t.target', '?', $target)
  ->set('t.comments', '?', $comments)
  ->set('t.date_modified', '?', 'NOW()')
  ->where('t.cat_id = ?', $cat_id)
  ->addWhere('t.source = ?', $text);

$num = $q->execute(); //$num = number of updated rows

Hint: "update" ignores the behaviours. When you want to preserve e.g. the original updated_at date use "update".

 

Use "->set('ull_location_id', 'NULL')" to create db NULL entries !?!

 

 subselect:

  • SELECT u.id FROM User u WHERE u.id IN (SELECT DISTINCT p.user_id FROM
    Post p WHERE p.user_id = u.id AND COUNT(p.id) > 50 GROUP BY p.id)

 direct pdo query

Clear runtime cache and force reload from database

        $models = Doctrine::getLoadedModels();
        foreach($models as $model)
        {
          $table = Doctrine::getTable($model)->clear();
        }