MY_Model base CRUD

[eluser]Mark Croxton[/eluser]
A basic limitation with many of the existing MY_Model classes that provide a base CRUD pattern are that they don't support multiple tables and joins. If you ever need to work with more than one table per model, and want some magical CRUD methods without the overhead or abstraction of an ORM, then this MY_Model might be for you.

My_Model can handle joins between a primary table and one or more others, including self joins (using aliases). For more complex queries the 'primary' table the model operates on can be changed to allow joins between secondary tables and their related tables.

The class is PHP 5 only. Most of the methods return $this and so are chainable. CIs Active Record methods can also be chained with the custom MY Model methods.

Usage
Place in your application/libraries folder and extend your models from it.

Simple join example
Let's say you have an 'offices' table and a 'countries' table.

Offices:
- id (primary key)
- office name
- city
- country_iso (foreign key)

Countries:
- iso (primary key)
- country_name


The model

Code:

class Office extends MY_Model {
        
    public function __construct()
    {
        parent::__construct();    
    
        // load tables this model operates on
        // $this->load_table('[table_name'], '[alias'], '[fields]', '[primary key]');
        $this->load_table('offices');
        $this->load_table('countries');
        
        // describe relationships
        // $this->table1->related('table2', array('table1_key', 'table2_key'));
        $this->offices->related('countries', array('country_iso', 'iso'));
    }
}

In your controller you can now grab offices in a variety of ways using the get() method and it's variants (get_one, get_list, get_column, get_field)

Code:

// load the model
$this->load->model('office');

// get a single office by id
$office = $this->office->get_one(
            array(
                'id'  => $office_id,
                'fields' => array('id', 'office_name', 'city', 'country_name')
            )
        );
// get a list of offices in $country_name
$list = $this->office->get_list('id', 'office_name', array(
            'country_name' => $country_name
        ));

You can also update, insert and delete your offices with ease:

Code:

// insert a new record
$this->office->insert($data);

// update a record
$this->office->update($data, $office_id);

// delete a record
$this->office->delete($office_id);

Other notable methods accessible to the controller are count(), count_all(), get_insert_id(), get_num_rows() and get_affected_rows().


Chaining joins

The get() method can handle any selecting task that operates on a single table with one or more tables joined to it (one-to-one or one-to-many). But what if you need to chain your joins (one-to-many or many-to-many)? Create a new method in your model:

Code:

public function get_users_in_group($group_id)
{
    $this->with("$this->users_groups")
         ->distinct()
         ->from()
         ->where(array('group_id' => $group_id)) // in `users_groups` table  
         ->join("$this->users", 'left', true) // join `users` to `users_groups` and set as primary
         ->where(array('activated' => 1)) // in `users` table
         ->join("$this->user_profiles", 'left', true) // join `user_profiles` to `users`, set as primary
         ->select('name') // in `user_profiles` table
         ->order_by('name', 'asc');
    
    return $this->get();
}

Note that passing TRUE as the third parameter of join() causes the primary table the model operates on to be changed to the newly joined table. This is just a convenience; you can also explicitly set the primary table by starting your statement $this->with('my_table_object').

v1.0.1 - Updated 20 April 2010:

* changed set() to with() to avoid confusion with $this->db->set() - make sure you update your models if you've used set()
* made table alias handling more robust; takes account of CI's current inablity to use aliases with $this->db->delete()
* $fields parameter of get() can now be a string, tables names will not be escaped


v1.0.3 - Updated 19 May 2010:
* select() and where() can now accept hard coded table aliases (table.column); the table specified will be automatically joined if required.
* new functions: set_count() and get_count(), for multiple queries where you need to store the count of each query, optionally using a key.
* get() can now be passed a third boolean argument to determine if the query will be initialised.
* various other subtle improvements designed to make the class more robust when working with complex joins.
* Moved to Github


v1.0.4 - Updated 23 August 2010:
* primary key default (if not specified when using load_table()) is now the first column in a table rather than 'id'

Download or fork:
http://github.com/croxton/MY_Model

[eluser]Mark Croxton[/eluser]
Self joins

My_Model uses aliases to facilitate self joins. Let's say you want to generate a nested list of 'services' using the adjacency list model. Services may be nested to 3 levels.

SQL

Code:

CREATE TABLE `services` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `parent_id` int(11) unsigned default NULL,
  `service_name` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `parent_id` (`parent_id`)
) ENGINE=InnoDB DEFAULT;

ALTER TABLE `services`
  ADD CONSTRAINT `services_ibfk` FOREIGN KEY (`parent_id`) REFERENCES `services` (`id`) ON DELETE CASCADE;

Back to the model - after loading the services table we can create a couple aliases of it, s2 and s3; alias values are passed as the second parameter of load_table. The aliased tables will reuse the table structure of the services table, so we pass its fields as the third parameter of load_table();

Code:

public function __construct()
    {
        parent::__construct();
        
        $this->load_table('services');
        
        // load self joined table as aliases
        $this->load_table('services', 's2', $this->services->fields);
        $this->load_table('services', 's3', $this->services->fields);
        
        // describe relationships
        $this->services->related('s2', array('id', 'parent_id'));
        $this->s2->related('s3', array('id', 'parent_id'));
    }

    /**
     * generate a hierarchical tree of services using the adjacency list model
     *
     * @return    array
     */    
    public function get_tree()
    {
        $this->with("$this->services")
            ->from()
            ->select(  'services.service_name AS l1_name,
                         services.id as l1_id,
                         s2.service_name as l2_name,
                         s2.id as l2_id,
                         s3.service_name as l3_name,
                         s3.id as l3_id',
                        FALSE) // pass FALSE as the second parameter to disable table name escaping
            ->where(array('parent_id' => NULL))
            ->join("$this->s2", 'left', true) // join `s2` to `services`
            ->join("$this->s3") // join `s3` to `s2`
            ->order_by('services.service_name, s2.service_name, s3.service_name');
    
        // run the query
           $result = $this->get();
    
        if ($this->get_num_rows() == 0)
        {
            return false;
        }
        return $this->_make_tree($result);
    }

    /**
     * this function is incidental to the example, but included for completeness
     *
     * @param    $tree  array
     * @return    array
     */    
    private function _make_tree($tree)
    {
        $services = array();
        
        foreach($tree as $l1)
        {
            if (isset($l1['l2_name']))
            {
                if (!isset($services[$l1['l1_name'].'|'.$l1['l1_id']][$l1['l2_name'].'|'.$l1['l2_id']]))
                {
                    $services[$l1['l1_name'].'|'.$l1['l1_id']][$l1['l2_name'].'|'.$l1['l2_id']] = '';
                }
            }
            if (isset($l1['l3_name']))
            {
                if (!isset($services[$l1['l1_name'].'|'.$l1['l1_id']][$l1['l2_name'].'|'.$l1['l2_id']][$l1['l3_name'].'|'.$l1['l3_id']]))
                {
                    $services[$l1['l1_name'].'|'.$l1['l1_id']][$l1['l2_name'].'|'.$l1['l2_id']][$l1['l3_name'].'|'.$l1['l3_id']] = '';
                }
            }
        }
        return $services;
    }

[eluser]Mark Croxton[/eluser]
Caching

My_Model looks up the structure of loaded tables using SHOW COLUMNS. This information is used to ensure that the data passed to INSERT, UPDATE, SELECT and WHERE maps to the fields of the table you are working with.

If you'd like to cache the table column queries I would recommend using a general purpose caching library. I've included a function for working with caching which expects the caching class to have public methods 'get' and 'write' which accept a single key.

Here's an example using the excellent MP Cache; assuming you've autoloaded mp_cache, and created a folder called 'table_structure' in your designated cache directory. In the model constructor:

Code:

public function __construct()
    {
        parent::__construct();
        
        // load the tables this model operates on    
        $tables = array('clients', 'countries', 'stock_exchanges', 'clients_industries', 'industries');
        
        // get/set cached table structure for this model
        $fields = $this->get_table_fields($tables, &$this->mp_cache, __CLASS__);
        
        // load the tables this model operates on
        foreach ($tables as $table)
        {
            $this->load_table($table, '', $fields[$table]);
        }
        
    }

[eluser]thinkigniter[/eluser]
This looks very interesting.

Thanks for sharing.

[eluser]flash_lover[/eluser]
Hi, I followed your tutorial to a T (well i believe so), but i keep getting,

_________________________

Severity: Notice

Message: Undefined variable: office_id

Filename: controllers/datalinker.php

Line Number: 24
_________________________________

which refers to -'id' => $office_id- line below.


// get a single asset by id
$office = $this->office->get_one(
array(
'id' => $office_id,
'fields' => array('id', 'office_name', 'city', 'country_name')
)
);


Maybe I missed something, any ideals?

Thanks

[eluser]Mark Croxton[/eluser]
$office_id would be the id the office you want to retrieve

So your controller might look like:

Code:

public function index($office_id=null)
{
    // load the model
    $this->load->model('office');

    if (is_null($office_id))
    {
        // generate a list of offices
        $list = $this->office->get_list('id', 'office_name');
    
        // send to your view...
    }
    else
    {
        // show a single office by id
        $office = $this->office->get_one(
                array(
                    'id'  => $office_id,
                    'fields' => array('id', 'office_name', 'city', 'country_name')
                )
            );
    
        // send to your view...
    }
}

[eluser]flash_lover[/eluser]
Thanks for the reply, now i get at

parse error on this line

$list = $this->office->get_list('id', 'office_name'));

Could something in my database, be throwing it off?

thanks

[eluser]Mark Croxton[/eluser]
You've got an extra bracket there for some reason:

$list = $this->office->get_list(‘id’, ‘office_name’));

[eluser]n0xie[/eluser]
Looks interesting.

[eluser]Mark Croxton[/eluser]
Updated to version 1.0.1 - download from the first post.