How do you work with a relational database in Zend

I'm using the zend framework to write an app and I'm having lots of problems getting my relational database models to work. I've read the quickstart and the docs several times and I'm still not sure how to do this. I post a picture of the relationships between the different tables involved to avoid having to explain everything since English is not my first language and I tend not to make myself clear when I try to explain...well complex things.

The tables press_releases, social_networking, blog_posts, rss_feed, directorios, users and articulos are all set as foreign keys in the table named planilla_users. I coded my table models the way the quickstart shows:

class Application_Model_DbTable_PlanillaUsers extends Zend_Db_Table_Abstract
{

protected $_name = 'planilla_users';
protected $_referenceMap = array(
    'User' => array(
        'columns' => 'users_id',
        'refTableClass' => 'Application_Model_DbTable_Users',
        'refColumns' => 'id'
    ),
    'Articulo' => array(
        'columns' => 'art_id',
        'refTableClass' => 'Application_Model_DbTable_Articulos',
        'refColumns' => 'id'
    ),...etc

...and the rest following the format of:

class Application_Model_DbTable_Users extends Zend_Db_Table_Abstract
{

protected $_name = 'users';
protected $_dependentTables = array('Application_Model_DbTable_PlanillaUsers');

I also have a model Planilla.php with all the setters and getters for the information to be stored/updated/retrieved/deleted,...however...I'm completely blank on what to do with the mapper. I don't know how it's supposed to work and I honestly haven't found a good example on how to do something like this yet. So any help would be mostly appreciated.

My advice to you would be to have a look at Doctrine, it is an object relational mapper which can intergrate quite nicely with ZF. I personally use version 1.2 with ZF 1.11.4 with no problems.

There is a nice screen cast here which explains how to intergate Doctrine into ZF. Doctrine can be a bit of a pig to to learn but once you understand it, it will save you time in the long run. Doctrine also comes with a command line script which can be used to reverse engineer databases into Doctrine classes. If you have the relations set on the database then Doctrine can pick that up too.

I also hear that ZF 2 will have Doctrine 2.0 included.

The method I use to create the Doctrine classes is to first setup doctrine in your appication.ini file, add these lines somewhere under production header.

[production]

//...

; Doctrine settings
pluginpaths.Freedom_Zend_Application_Resource = "Freedom/Zend/Application/Resource"
resources.doctrine.connection_string = "mysql://username:password@localhost/database_name"
resources.doctrine.compiled = false ; use compiled version of Doctrine
resources.doctrine.cache = false ; use query cache

; Information required for models generator
resources.doctrine.models_path = APPLICATION_PATH "/modules/default/models/Doctrine"
resources.doctrine.module_directories[] = APPLICATION_PATH "/modules/default/models/Doctrine/base"  
resources.doctrine.module_directories[] = APPLICATION_PATH "/modules/default/models/Doctrine"

; Generator settings
resources.doctrine.generate_models_options.phpDocPackage = Your App Name
resources.doctrine.generate_models_options.phpDocSubpackage = Doctrine
resources.doctrine.generate_models_options.phpDocName = Your Company Name
resources.doctrine.generate_models_options.phpDocEmail = your@email.address
resources.doctrine.generate_models_options.pearStyle = true
resources.doctrine.generate_models_options.generateTableClasses = true
resources.doctrine.generate_models_options.generateBaseClasses = true
resources.doctrine.generate_models_options.classPrefix = "Model_Doctrine_"
resources.doctrine.generate_models_options.baseClassPrefix = "Base_"
resources.doctrine.generate_models_options.baseClassesDirectory =
resources.doctrine.generate_models_options.classPrefixFiles = false
resources.doctrine.generate_models_options.generateAccessors = false
//...

You will notice a line at the top pluginpaths.Freedom_Zend_Application_Resource Freedom is my generic namespace in my library (see folder tree below). I here I have a Zend folder where I can place my extra ZF code, this includes overiding existing ZF functions if required. Freedom is my company name, yours will obviously differ. This line will need to change to your company's name, for example pluginpaths.Yourcompany_Zend_Application_Resource = "Yourcompany/Zend/Application/Resource"

The next line is where you place your database connection settings resources.doctrine.connection_string = "mysql://username:password@localhost/database_name"

The next three lines:

resources.doctrine.models_path = APPLICATION_PATH "/modules/default/models/Doctrine"
resources.doctrine.module_directories[] = APPLICATION_PATH "/modules/default/models/Doctrine/Base"  
resources.doctrine.module_directories[] = APPLICATION_PATH "/modules/default/models/Doctrine"

tell Doctrine where to place the classes generated, as I am using a modular setup these go into my application/modules/default/models/Doctrine and application/modules/default/models/Doctrine/Base respectively (see folder tree below).

There are some other lines that will need changing, these should be self evident.

You will also need to add the following lines under the development heading in your application.ini.

[development : production]

//...

; phpSettings
resources.doctrine.compiled = false ; use compiled version of Doctrine
resources.doctrine.cache = false ; use query cache

//...

You will also need the resource file Doctrine.php. Where this is located depends on your setup, mine is as follows.

-Application
  -configs
  -layouts
  -modules
    -default // ZF default controller
      -controllers
      -models
        -Doctrine // folder for you generated classes
          -Base // Do not change the files in this folder
    -views
      -scripts
-Library
  -Doctrine // the doctrine application as downloaded
   doctrine-cli.php // you will need to create this (see below)
   Doctrine.php // come with the doctrine application
  -Freedom // my generic namespace, shared across multiple apps
    -Zend // place to overide/add ZF classes
      -Application
        -Resource
          *Docrine.php // the file below
  -Zend // ZF 1.11.4 (yours may differ)
  -ZendX // ZF extras

The *Doctrine.php file is as follows (obviously ignore the *!!)

/**
* Doctrine application resource
*
* @author Juozas Kaziukenas (juozas@juokaz.com)
*/
class Freedom_Zend_Application_Resource_Doctrine extends Zend_Application_Resource_ResourceAbstract
{
    /**
* Initialize
*/
    public function init()
    {
        $doctrineConfig = $this->getOptions();

        if (isset($doctrineConfig['compiled']) && $doctrineConfig['compiled'] == true &&
            file_exists(APPLICATION_PATH . '/../library/Doctrine.compiled.php'))
        {
            require_once 'Doctrine.compiled.php';
        }
        else
        {
            require_once 'Doctrine.php';
        }
        $loader = Zend_Loader_Autoloader::getInstance();
        $loader->pushAutoloader(array('Doctrine_Core', 'autoload'), 'Doctrine');

        $manager = Doctrine_Manager::getInstance();

        // set models to be autoloaded and not included (Doctrine_Core::MODEL_LOADING_AGGRESSIVE)
        $manager->setAttribute(
            Doctrine_Core::ATTR_MODEL_LOADING,
            Doctrine_Core::MODEL_LOADING_CONSERVATIVE
        );

        // enable ModelTable classes to be loaded automatically
        $manager->setAttribute(
            Doctrine_Core::ATTR_AUTOLOAD_TABLE_CLASSES,
            true
        );

        // enable validation on save()
        $manager->setAttribute(
            Doctrine_Core::ATTR_VALIDATE,
            Doctrine_Core::VALIDATE_ALL
        );

        // enable accessor override
        $manager->setAttribute(
            Doctrine_Core::ATTR_AUTO_ACCESSOR_OVERRIDE,
            true
        );

        // enable sql callbacks to make SoftDelete and other behaviours work transparently
        $manager->setAttribute(
            Doctrine_Core::ATTR_USE_DQL_CALLBACKS,
            true
        );

        // enable automatic queries resource freeing
        $manager->setAttribute(
            Doctrine_Core::ATTR_AUTO_FREE_QUERY_OBJECTS,
            true
        );

        // connect to database
        $manager->openConnection($doctrineConfig['connection_string']);

        // set to utf8
        $manager->connection()->setCharset('utf8');

        if (isset($doctrineConfig['cache']) && $doctrineConfig['cache'] == true)
        {
            $cacheDriver = new Doctrine_Cache_Apc();

            $manager->setAttribute(
                Doctrine_Core::ATTR_QUERY_CACHE,
                $cacheDriver
            );
        }

        return $manager;
    }
}

Next you will need to create the doctrine-cli.php file to bootstrap your application this should be located in the root of your library file (see tree above), mine is as follows.

/**
 * Doctrine CLI script
 *
 * @author Juozas Kaziukenas (juozas@juokaz.com)
 */

define('APPLICATION_ENV', 'development');
define('APPLICATION_PATH', realpath(dirname(__FILE__) . '/../../your/application'));

set_include_path(implode(PATH_SEPARATOR, array(
    realpath(APPLICATION_PATH . '/../library'),
    './',
    get_include_path(),
)));

require_once 'Zend/Application.php';

// Create application, bootstrap, and run
$application = new Zend_Application(
    APPLICATION_ENV,
    APPLICATION_PATH . '/configs/application.ini'
);

$application->getBootstrap()
        ->bootstrap('doctrine')
        ->bootstrap('autoload');

// set aggressive loading to make sure migrations are working
Doctrine_Manager::getInstance()->setAttribute(
    Doctrine::ATTR_MODEL_LOADING,
    Doctrine_Core::MODEL_LOADING_AGGRESSIVE
);

$options = $application->getBootstrap()->getOptions();

$cli = new Doctrine_Cli($options['resources']['doctrine']);

$cli->run($_SERVER['argv']);

The only line you should need to change is

define('APPLICATION_PATH', realpath(dirname(__FILE__) . '/../../your/application'));

so it points to your application's root folder, similar to the line in your index.php file in your public folder.

Now hopefully you should be ready to generate your database class files. Goto a terminal window and entrer the following.

cd /home/path/to/library
php doctrine-cli.php generate-models-db

If all has gone well the folders application/modules/default/models/Doctrine and application/modules/default/models/Doctrine/Base should contain the classes for your database. As I mentioned above do not change the files in the Base folder, you can use the classes in the parent Doctrine folder to make changes. You will also notice that there are two classes per database table in the Doctrine folder, one is suffixed with Table. Here I tend to place my DQL code to keep it away from my models/controllers. The other classes can be used to make changes to the classes in the base folder, also you can add hooks and listeners here to add table specific code such as adding password encryption or presetting dates etc.

I hope I have explained it clearly enough as it is a pig of a job to get working but this is how mine is set up.

I hope this helps.