journal
Table Relationships in CI
July 31, 2006
For those of you who work with the PHP framework, Code Igniter, Glossopteris has been working on an addition to the framework that will allow for complex table inter-relationships to be assigned and simple CRUD actions to be completed. This is a pretty close mirror to the model class of CakePHP although only the idea has been taken, the code is all custom.
The code was originally developed as a model, but given the issues with loading multiple instances of models in CI then it worked more successfully as library. The advantage of these features each table you wish to include in this needs to be fully annotated in the relevant function within the table library, an example is below;
function xml_streams($action, $conditions=false)
{
$table = 'xml_streams';
$primary_key = 'id';
$has_one = array(
'parent_page' => array(
'key' => 'parent_page',
'assoc_table' => 'pages',
'join_key' => 'page_id',
'return' => 'page_id,page_title',
'conditions'=> ' '));
$has_many = array(
'pages_related'=> array(
'assoc_table'=> 'pages',
'assoc_field'=> 'page_id',
'join_table' => 'pages_related',
'join_main' => 'page_id',
'join_assoc' => 'related_page_id',
'return' => 'page_id',
'dependant' => true,
'conditions' => ''));
$belongs = array(
'pages' => array(
'assoc_table' => 'pages',
'join_key' => 'bolinfonet_stream',
'dependant' => false,
'conditions' => ' '));
$fields = array(
'stream' => array(
'label' => 'Title',
'type' => 'text',
'rules' => 'trim|alphanumeric|maxlength[100]'),
'layout' => array(
'label' => 'Title',
'type' => 'text',
'rules' => 'trim|alphanumeric|maxlength[100]'),
'xml_file' => array(
'label' => 'Title',
'type' => 'text',
'rules' => 'trim|alphanumeric|maxlength[100]') );
$dropdown = array('order'=>'stream','key_field'=>'id','value_field'=>'stream');
return $this->_action_switch($action, $table, $conditions, $primary_key, $has_one, $has_many, $fields, $dropdown, $belongs);
}
So each table would need to be filled out in this fashion, and then the CRUD actions would be called like this;$this->table->xml_stream('get_all');
$this->table->xml_stream('get_one',1);
$this->table->xml_stream('generate_list');
$this->table->xml_stream('insert');
$this->table->xml_stream('update',3);
$this->table->xml_stream('delete',3);
There are more actions that could be worked on but these seem to cover most of the common needs. So this is still in a development phase but already it has reduced the amount of code some of our more recent apps have needed and made life that little bit easier.
The code for the library can be seen below or if you would like the files that have a sample DB table and examples of the outputs as well as the library, please click here.<?php
if (!defined('BASEPATH')) exit('No direct script access allowed');
class Table {
function Table()// Call the Class constructor
{
$this->obj =& get_instance();
$this->obj->load->library('validation');
}
//Database actions common to all table functions
function _get_all($table, $has_one, $has_many, $primary_key)
{
$query = $this->obj->db->get($table); //get all db records from table
$i=0;
foreach($query->result_array() as $row)
{
$array[$table][$i] = $row; //insert entire row into $array calling it table name
foreach($has_one as $key=>$value)
{
unset($array[$table][$i][$value['key']]); //remove the integer representation of single relationship field
$table_name = $value['assoc_table'];
if(isset($value['return']))
{
$sub_array = $this->$table_name('get_one_fields', array($value['join_key'], $row[$value['key']], $value['return']));
$array[$table][$i][$key] = $sub_array[0];
}
else
{
$sub_array = $this->$table_name('get_one', array($value['join_key'], $row[$value['key']]));
$array[$table][$i][$key] = $sub_array[$table_name];
}
}
foreach($has_many as $key=>$value)
{
$table_name = $value['assoc_table'];
//$array[$table][$i][$key] = $this->$table_name('get_some', array($has_many[$key], $row[$primary_key])); //add to array the returned data
$array[$table][$i][$key] = $this->_get_some($table_name, $has_many[$key], $row[$primary_key]);
}
$i++;
}
return $array;
}
function _get_one($table, $conditions, $has_one, $has_many, $primary_key)
{
$array = array();
if(is_array($conditions))
{
$primary_key = $conditions[0];
$id = $conditions[1];
}
else
{ $id = $conditions; }
if(!is_numeric($id) OR $id==0){ return $array; }
$this->obj->db->where($primary_key, $id);
$query = $this->obj->db->get($table); //get all db records from table
foreach($query->result_array() as $row)
{
$array[$table] = $row; //insert entire row into $array calling it table name
foreach($has_one as $key=>$value)
{
unset($array[$table][$value['key']]); //remove the integer representation of single relationship field
$table_name = $value['assoc_table'];
if(isset($value['return']))
{
$sub_array = $this->$table_name('get_one_fields', array($value['join_key'], $row[$value['key']], $value['return']));
$array[$table][$key] = $sub_array[0];
}
else
{
$sub_array = $this->$table_name('get_one', array($value['join_key'], $row[$value['key']]));
$array[$table][$key] = $sub_array[$table_name];
}
}
foreach($has_many as $key=>$value)
{
$table_name = $value['assoc_table'];
//$array[$table][$key] = $this->$table_name('get_some', array($has_many[$key], $row[$primary_key])); //add to array the returned data
$array[$table][$key] = $this->_get_some($table_name, $has_many[$key], $row[$primary_key]);
}
}
return $array;
}
function _get_one_fields($table, $conditions, $primary_key)
{
$array = array();
$primary_key = $conditions[0];
$id = $conditions[1];
$return = $conditions[2];
if(!is_numeric($id) OR $id==0){ return $array; }
$this->obj->db->select($return);
$this->obj->db->where($primary_key, $id);
$query = $this->obj->db->get($table);
return $query->result_array();
}
function _get_some($table, $has_many, $id)
{
$this->obj->db->select($has_many['join_assoc']);
$this->obj->db->from($has_many['join_table']);
$this->obj->db->where($has_many['join_main'], $id);
if(is_array($has_many['conditions']))
{ $this->obj->db->where($has_many['conditions']); }
$query = $this->obj->db->get();
foreach($query->result_array() as $key=>$value)
{
if(isset($has_many['return']))
{
$sub_array = $this->$table('get_one_fields', array($has_many['assoc_field'], $value[$has_many['join_assoc']], $has_many['return']));
$array[$key] = $sub_array[0];
}
else
{
$sub_array = $this->$table('get_one', array($has_many['assoc_field'], $value[$has_many['join_assoc']]));
$array[$key] = $sub_array[$table];
}
}
return $array;
}
function _generate_list($table, $conditions)
{
$order = $conditions['order'];
$key_field = $conditions['key_field'];
if(!is_array($conditions['value_field']))
{ $value_field[0] = $conditions['value_field']; }
else
{ $value_field = $conditions['value_field']; }
$this->obj->db->orderby($order);
$query = $this->obj->db->get($table);
foreach($query->result_array() as $row)
{
foreach($value_field as $item)
{ $array[$row[$key_field]] .= $row[$item] . ' '; }
$array[$row[$key_field]] = trim($array[$row[$key_field]]);
}
return $array;
}
function _update($table, $conditions)
{
$id = $conditions;
if($id == ''){ return false; } // If id is missing stop function
// Grab some of the relationship data from the relevant table function
$fields = $this->$table('fields');
$has_many = $this->$table('has_many');
$primary_key = $this->$table('primary_key');
//Put the has_many POST items into page variables, the validation routine affects the array information!
foreach($has_many as $key=>$value)
{ $post_array[$key] = $_POST[$key]; }
//Retrieve and set all the validation rules layed out in table fn
foreach($fields as $key => $value)
{
$rules[$key] = $value['rules'];
$fields[$key] = $value['label'];
}
//Set all the validation rules and field names for the has_many fields
foreach($has_many as $key=>$value)
{
$field_names[$key] = ucwords(str_replace('_', ' ', $key));
}
$this->obj->validation->set_error_delimiters('<p id="fail_message">', '</p>');
$this->obj->validation->set_rules($rules);
$this->obj->validation->set_fields($field_names);
if ($this->obj->validation->run() == FALSE) { echo $this->obj->validation->error_string; return false; } //If routine fails validation return false.
foreach($fields as $key=>$value)
{ $post[$key] = $this->obj->input->post($key); } //Import all fields from post array into a variable.
$this->obj->db->where($primary_key, $id);
$this->obj->db->set($post);
$this->obj->db->update($table); //Update main table with post data
foreach($post_array as $key=>$value)
{
//Remove existing join table entries for this has many relationship.
$this->obj->db->where($has_many[$key]['join_main'], $id);
if(is_array($has_many[$key]['conditions']))
{ $this->obj->db->where($has_many[$key]['conditions']); }
$this->obj->db->delete($has_many[$key]['join_table']);
//Loop through all the new entries from the POST array
foreach ($post_array[$key] as $k=>$v)
{
$this->obj->db->set($has_many[$key]['join_main'], $id); //Set the main table id
$this->obj->db->set($has_many[$key]['join_assoc'], $v); //Set the assoc table id
if(is_array($has_many[$key]['conditions']))
{
$cond = $has_many[$key]['conditions'];
$set_field = explode(' ', key($cond));
$this->obj->db->set($set_field[0], $cond[key($cond)]);
}
$this->obj->db->insert($has_many[$key]['join_table']); //Insert new entry into join table
}
}
return true;
}
function _insert($table)
{
// Grab some of the relationnship data from the relevant table function
$fields = $this->$table('fields');
$has_many = $this->$table('has_many');
$primary_key = $this->$table('primary_key');
//Put the has_many POST items into page variables, the validation routine affects the array information!
foreach($has_many as $key=>$value)
{ $post_array[$key] = $_POST[$key]; }
//Retrieve and set all the validation rules layed out in table fn
foreach($fields as $key => $value)
{
$rules[$key] = $value['rules'];
$fields[$key] = $value['label'];
}
//Set all the validation rules and field names for the has_many fields
foreach($has_many as $key=>$value)
{
$field_names[$key] = ucwords(str_replace('_', ' ', $key));
}
$this->obj->validation->set_error_delimiters('<p id="fail_message">', '</p>');
$this->obj->validation->set_rules($rules);
$this->obj->validation->set_fields($field_names);
if ($this->obj->validation->run() == FALSE) { echo $this->obj->validation->error_string; return false; } //If routine fails validation return false.
foreach($fields as $key=>$value)
{ $post[$key] = $this->obj->input->post($key); } //Import all fields from post array into a variable.
$this->obj->db->where($primary_key, $id);
$this->obj->db->set($post);
$this->obj->db->insert($table); //Insert into main table with post data
$id = $this->obj->db->insert_id();
foreach($post_array as $key=>$value)
{
//Remove existing join table entries for this has many relationship.
$this->obj->db->where($has_many[$key]['join_main'], $id);
if(is_array($has_many[$key]['conditions']))
{ $this->obj->db->where($has_many[$key]['conditions']); }
$this->obj->db->delete($has_many[$key]['join_table']);
//Loop through all the new entries from the POST array
foreach ($post_array[$key] as $k=>$v)
{
$this->obj->db->set($has_many[$key]['join_main'], $id); //Set the main table id
$this->obj->db->set($has_many[$key]['join_assoc'], $v); //Set the assoc table id
if(is_array($has_many[$key]['conditions']))
{
$cond = $has_many[$key]['conditions'];
$set_field = explode(' ', key($cond));
$this->obj->db->set($set_field[0], $cond[key($cond)]);
}
$this->obj->db->insert($has_many[$key]['join_table']); //Insert new entry into join table
}
}
return $id;
}
function _delete($table, $conditions)
{
$id = $conditions;
if($id == ''){ return false; } // If id is missing stop function
// Grab some of the relationnship data from the relevant table function
$belongs = $this->$table('belongs');
$has_many = $this->$table('has_many');
$primary_key = $this->$table('primary_key');
//Delete entries in has many join tables
foreach($has_many as $key=>$value)
{
if($has_many[$key]['dependant'])
{
$this->obj->db->where($has_many[$key]['join_main'], $id);
if(is_array($has_many[$key]['conditions']))
{ $this->obj->db->where($has_many[$key]['conditions']); }
$this->obj->db->delete($has_many[$key]['join_table']);
}
}
//Loop through the belongs entries and delete dependant entries, and nullify the fields of the non-dependants
foreach($belongs as $key=>$value)
{
if($belongs[$key]['dependant'])
{
$this->obj->db->where($value['join_key'], $id);
if(is_array($value['conditions']))
{ $this->obj->db->where($value['conditions']); }
$this->obj->db->delete($value['assoc_table']);
}
else
{
$this->obj->db->where($value['join_key'], $id);
$this->obj->db->set($value['join_key'], NULL);
$this->obj->db->update($value['assoc_table']);
}
}
//Delete main table information according to id
$this->obj->db->where($primary_key, $id);
$this->obj->db->delete($table);
return true;
}
//Switch case that directs the table data request to the appropriate function. Common to all tables
function _action_switch($action, $table, $conditions, $primary_key, $has_one, $has_many, $fields, $dropdown, $belongs)
{
switch($action)
{
case 'get_all' :
return $this->_get_all($table, $has_one, $has_many, $primary_key);
break;
case 'get_one' :
return $this->_get_one($table, $conditions, $has_one, $has_many, $primary_key);
break;
case 'get_one_fields' :
return $this->_get_one_fields($table, $conditions, $primary_key);
break;
case 'generate_list' :
return $this->_generate_list($table, $dropdown);
case 'has_many' :
return $has_many;
break;
case 'has_one' :
return $has_one;
break;
case 'fields' :
return $fields;
break;
case 'primary_key' :
return $primary_key;
break;
case 'dropdown' :
return $dropdown;
break;
case 'belongs' :
return $belongs;
break;
case 'update' :
return $this->_update($table, $conditions);
break;
case 'insert' :
return $this->_insert($table);
break;
case 'delete' :
return $this->_delete($table, $conditions);
break;
}
}
// Table function with relationships layed out. Actions included.