DataMapper 1.6.0

[eluser]ntheorist[/eluser]
okay, so i've been attempting to hack datamapper's code to fit it to what i've been trying to do, and it seems so far i'm having some success.

While DM works great for organizing tables and data and providing a great object model for running queries, plus being a relatively tiny library (almost 1/5 the size of IgnitedRecord, which I've also been looking at), it doesn't yet seem to handle the relations beyond loading the model of a related item. When a datamap's related items are populated, it requires another entire query (double join) to load it, and if you're listing hundreds of rows in a query, each with possibly several relations all requiring separate queries to load their data, and you again multiply that by perhaps many users running these queries at the same time.. it worried me about performance and scalability.

So here's what i'm working on to improve performance in terms of relations. Keep in mind too, this is a work in progress.. I'm making edits and adding functions/properties to datamapper to allow this to work, and i still have to address a few things, such as method chaining, etc.. Right now i'm focused on manipulating the active record for get() calls.

When running a query with an item i know has related items, before calling get(), i call this function (which i'm just writing directly into datamapper to provide for all models)

Code:

function join_related()
{
    // Don't run a join_related directly on a related model (yet..)
    if ( ! empty($this->related))
    {
        return FALSE;
    }
    
    // Prevent Re-join if join has already been called
    if ( $this->tables_joined === TRUE)
    {
        return TRUE;
    }
    
    // Check if no select statements have been made yet, if not, the table
    
    if(empty($this->db->ar_select))
    {
        $this->db->select($this->table.'.*');
    }            
    
    // Join has_one models with their id and specified value(s)
    
    foreach($this->has_one as $model)
    {
        $model_class = ucfirst($model);
        
        // Instantiate new model
        $ho_model = new $model_class();
        
        $ho_table = $ho_model->table;
        $ho_value = empty($ho_model->display_value) ? 'id' : $ho_model->display_value;
        $ho_relationship_table = $this->_get_relationship_table($this->prefix, $ho_table, $model);
        
        // Select values as aliased columns
        $this->db->select($ho_table.'.id AS '.$model.'_id');
        $this->db->select($ho_table.'.'.$ho_value.' AS '.$model.'_value');
        
        // Add aliased columns to datamap field list
        $this->fields[] = $model.'_id';
        $this->fields[] = $model.'_value';
        
        // Double join relation table
        $this->db->join($ho_relationship_table, $ho_relationship_table.'.'.$this->model.'_id = '.$this->table.'.id','left');
        $this->db->join($ho_table, $ho_table.'.id = '.$ho_relationship_table.'.'.$model.'_id','left');
    
    }
    
    // Join has_many models with their counts for this model
    
    foreach($this->has_many as $model)
    {
        $model_class = ucfirst($model);
        
        $hm_model = new $model_class();
        $hm_table = $hm_model->table;
        $hm_relationship_table = $this->_get_relationship_table($this->prefix, $hm_table, $model);
        
        $this->db->select('(SELECT count(*) FROM (`'.$hm_relationship_table.'`) WHERE `'.$this->model.'_id` = '.$this->table.'.id) AS '.$model.'_count');
        
        $this->fields[] = $model.'_count';
    }
    
    $this->tables_joined = TRUE;

    return TRUE;

}

The basic idea here is that if you have a 'has_one' model, meaning there's one of those models for each of the current one you're using, you are asking it to include the alias columns 'model_id' and 'model_value', in every row of the result. The 'model_value' field is determined in a model's definition. For instance, a 'User' has_one 'Userclass' (Admin, Manager, Member, etc..), so when joining a userclass to a user, to set the data that populates in 'userclass_value' to the field 'name' of the userclass, its simply stated in the userclass model:

Code:

class Userclass extends DataMapper {

    var $table = 'userclasses';
    var $controller = 'userclasses';
    var $model = 'userclass';
    
    var $has_many = array('user');
    
    var $display_value = 'name';
    
    var $validation = array(
        array(
            'field' => 'name',
            'label' => 'Class Name',
            'rules' => array('required', 'trim', 'unique', 'alpha', 'min_length' => 3)
        )
    );
        

    function Userclass()
    {
        parent::DataMapper();
    }
}

if no $display_value is defined, it defaults to id.

thus, now i can call in my controller

Code:

$user = new User();
$user->join_related();
$user->get();

this produces only one query:

SELECT users.*, userclasses.id AS userclass_id, userclasses.name AS userclass_value FROM (`users`) LEFT JOIN `userclasses_users` ON userclasses_users.user_id = users.id LEFT JOIN `userclasses` ON userclasses.id

and i can continue in my controller:

Code:

foreach($user->all as $u) {
    echo $u->username . ' is a ' . $u->userclass_value . ' which is userclass ID #'.$u->userclass_id.br();
}

This is also handy for generating forms where you may want to have a drop down to select a userclass, and you want to set it's selected value to 'userclass_id'. Also, it works nicely for creating links in the loop, ie:

Code:

$link = anchor('userclasses/view/'.$u->userclass_id',$u->userclass_value);

and yes, multiple has_one and has_many models can be all combined into a single query. I'll probably have to implement table alias's as well, to allow for multiple self-joins if needed

more soon..