<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

/*===== Subversion ====================================================================
	Rev  : $ $Date: 2007-05-14 22:13:55 +1000 (Mon, 14 May 2007) $ $
	Date : $ $Rev: 19 $ $
=====================================================================================*/

/**
 * Object Relational Mapper
 * Maps database tables with associations to programming objects
 * Please read the requirements below carefully!
 *
 * Database table requirements:
 * - there should be a table called 'orm_schemas' for storing table schema data
 * - MySQL query for the 'orm_schemas' table:
 *       CREATE TABLE orm_schemas (
 *        id         mediumint(3) unsigned NOT NULL auto_increment,
 *        model      varchar(20)  NOT NULL default '',
 *        recursion  tinyint(1)   unsigned default NULL,
 *        conjTables text,
 *        schemaData text,
 *        PRIMARY KEY  (id)
 *       ) ENGINE=MyISAM;
 * - delete the model records if the corresponding table structure have been altered
 *
 * Database table convention requirements:
 * - table name should be one plural word
 * - conjunction table name should be one table name followed by an underscore (_) followed by another table name
 * - model name is the singularised form of the table name
 * 
 * Database fields convention requirements:
 * - primary key should be the model name followed by an underscore (_) followed by 'id', e.g. PK of 'posts' should be 'post_id'
 * - other fields should be the model name followed by an underscore (_) followed by descriptive words, e.g. 'post_date' and 'post_updated'
 * - foreign keys should be the foreign model name followed by an underscore (_) followed by 'id', e.g. FK of 'posts' could be 'user_id' and 'category_id'
 * - self reference keys should be one single word (noun) followed by an underscore (_) followed by 'id', e.g. SRK of 'posts' could be 'parent_id' or 'thread_id'
 *
 * Shortcut find methods:
 * $this->find() has two convenient shortcut methods: $this->findField() and $this->searchField()
 * where 'Field' is the actual name of the table field, with OR without the preceding model name and underscore
 * e.g. if you want to find 'post_title' from 'posts': $this->findTitle('post title to look for', 'posts')
 * same for $this->searchTitle(), the difference between these two methods is the former looks for exact matches, the latter look for partial matches
 * you can think of the former as 'something = value' and the latter as 'something LIKE value' in SQL queries
 * Use $this->find() with $this->where() if you want to search by foreign keys
 *
 * Shortened field names (optional):
 * When performing CRUD actions (e.g. $this->findField() and $this->modelName->field = 'field value')
 * field names can be shortened if and only if they're NOT foreign keys
 * e.g. 'post_id' and 'post_title' in 'posts' table can be shortened as 'id' and 'title'
 * however, since this is optional, you can always refer them by their full names
 * foreign key fields can NOT be shortened !!
 *
 * Active record proxies:
 * You may use CodeIginiter's built in active record methods before calling $this->find() or the shortcut methods
 * e.g. 
 *      $this->where('post_title', 'the post I want');
 *      $this->find('posts');
 * equals
 *      $this->findTitle('the post I want', 'posts');
 *
 * @package CodeIgniter
 * @subpackage library
 * @author Fred Wu
 * @version 0.1.8
 * @license GNU Lesser General Public License (LGPL) http://www.gnu.org/licenses/lgpl.html
 **/
class ORM {
	
	/**
	 * CI instance
	 *
	 * @access protected
	 * @var object
	 **/
	var $_CI;
	
	/**
	 * Current table name
	 *
	 * @access public
	 * @var string
	 **/
	var $table;
	
	/**
	 * Conjunction table names
	 * we need it because one conjunction table has two potential name conventions
	 * array['parentTableName']['tableName']
	 *
	 * @access private
	 * @var array
	 **/
	var $__conjunctionTables;
	
	/**
	 * Conditional queries
	 *
	 * @access private
	 * @var array
	 **/
	var $__conditions;
	
	/**
	 * Record offset number
	 *
	 * @access private
	 * @var integer
	 **/
	var $__offset;
	
	/**
	 * Record limit number
	 *
	 * @access private
	 * @var integer
	 **/
	var $__limit;
	
	/**
	 * Total number of records in a table
	 *
	 * @access public
	 * @var integer
	 **/
	var $tableCount;
	
	/**
	 * Number of records by find condition
	 *
	 * @access public
	 * @var integer
	 **/
	var $findCount;
	
	/**
	 * Modified model
	 *
	 * @access private
	 * @var string
	 **/
	var $__modifiedModel;
	
	/**
	 * Modified model record id
	 *
	 * @access private
	 * @var integer
	 **/
	var $__modifiedModelId;
	
	/**
	 * Constructor
	 **/
	function ORM()
	{
		$this->_CI =& get_instance();
		$this->_CI->load->helper('inflector');
		if (phpversion() < 5)
		{
			overload(strtoupper(get_class($this)));
		}
	}
	
	/**
	 * Find
	 * finds the ORMed data of the specified table
	 *
	 * @access public
	 * @param  string $table table name
	 * @param  integer $limit record limit number
	 * @param  integer $page page number (for pagination, must be positive)
	 * @param  integer $recursion recursion level (0 for none)
	 * @return array table with associated data
	 **/
	function find($table, $limit = null, $page = 1, $recursion = null)
	{
		$this->table = $table;
		
		$model = singular($table);
		
		if (empty($page) || !is_numeric($page) || intval($page) < 1)
		{
			$page = 1;
		}
		
		if (!isset($recursion))
		{
			$recursion = 1;
		}
		
		$this->__limit  = $limit;
		$this->__offset = $limit * ($page - 1);
		
		// builds table relationship structure
		$schemaQuery     = $this->_CI->db->getwhere('orm_schemas', array('model' => $model, 'recursion' => $recursion));
		$schemaResult    = $schemaQuery->result_array();
		
		if (!$schemaResult)
		{
			$tableStructure[$table] = $this->__build_table_structure($table, $recursion);
			
			// stores model schema
			$this->_CI->db->set('model', $model);
			$this->_CI->db->set('recursion', $recursion);
			$this->_CI->db->set('conjTables', serialize($this->__conjunctionTables));
			$this->_CI->db->set('schemaData', serialize($tableStructure[$table]));
			$this->_CI->db->insert('orm_schemas');
		}
		else
		{
			$this->__conjunctionTables = unserialize($schemaResult[0]['conjTables']);
			$tableStructure[$table]    = unserialize($schemaResult[0]['schemaData']);
		}
		
		// populates data according to association
		$tableData = $this->__populate_table_data($tableStructure);
		
		// record count
		$this->tableCount = $this->_CI->db->count_all($table);
		$this->findCount  = count($tableData[$table]);
		
		return $tableData[$table];
	}
	
	/**
	 * Number of records
	 * can use proxy active record methods before calling
	 * e.g. $this->orm->where('post_title', 'my_post');
	 *
	 * @access public
	 * @param  string $table table name
	 * @return integer number of records
	 **/
	function recordsCount($table)
	{
		$this->_CI->db->select('COUNT(*)');
		$this->__query_conditions();
		$query  = $this->_CI->db->get($table);
		$result = $query->result_array();
		
		return $result[0]['COUNT(*)'];
	}
	
	/**
	 * Build table structure
	 * constructs an array according to the table associations
	 *
	 * @access private
	 * @param  string $table table name
	 * @param  integer $recursion recursion level (0 for none)
	 * @return array table associations (structure only)
	 **/
	function __build_table_structure($table, $recursion)
	{
		$model = singular($table);
		
		// table names
		$tables = $this->_CI->db->list_tables();
		// fields in each table
		$fields = $this->_CI->db->list_fields($table);
		
		$structure = null;
		
		if ($recursion > 0)
		{
			// determines associations according to the fields
			foreach ($fields as $field)
			{
				$fieldModel = substr($field, 0, -3);
				$fieldTable = plural($fieldModel);
				
				// relationships based on foreign keys
				if (!preg_match("/\b{$model}_/", $field))
				{
					// belongsTo
					if (in_array($fieldTable, $tables))
					{
						$structure['belongsTo'][$fieldTable] = $this->__build_table_structure($fieldTable, $recursion - 1);
					}
					// belongsToSelf (is_xxxxx_of)
					else
					{
						$structure['belongsToSelf']["is_{$fieldModel}_of"] = $this->__build_table_structure($table, $recursion - 1);
					}
				}
			}
			
			// relationships based on other tables
			foreach ($tables as $tableName)
			{
				// hasOneMany
				if (!preg_match("/{$table}/", $tableName))
				{
					$tableFields = $this->_CI->db->list_fields($tableName);
					foreach ($tableFields as $tableField)
					{
						if (preg_match("/{$model}_id/", $tableField))
						{
							$structure['hasOneMany'][$tableName] = $this->__build_table_structure($tableName, $recursion - 1);
						}
					}
				}
				// hasAndBelongsToMany
				elseif (preg_match("/\b{$table}_/", $tableName))
				{
					$assocTable = substr($tableName, strlen($table)+1);
					$this->__conjunctionTables[$table][$assocTable] = $tableName;
					$structure['hasAndBelongsToMany'][$assocTable]  = $this->__build_table_structure($assocTable, $recursion - 1);
				}
				elseif (preg_match("/_{$table}\b/", $tableName))
				{
					$assocTable = substr($tableName, 0, -strlen($table)-1);
					$this->__conjunctionTables[$table][$assocTable] = $tableName;
					$structure['hasAndBelongsToMany'][$assocTable]  = $this->__build_table_structure($assocTable, $recursion - 1);
				}
			}
		}
		
		return $structure;
	}
	
	/**
	 * Populate table data
	 * populates table data according to the table structure
	 *
	 * @access private
	 * @param  array $tableStructure table structure returned by $this->__build_table_structure() *OR* to be generated in the function
	 * @param  string $parentTable table name of the parent
	 * @param  string $relationship (belongsTo | belongsToSelf | hasOneMany | hasAndBelongsToMany)
	 * @return array table with associated data
	 **/
	function __populate_table_data($tableStructure, $parentTable = null, $relationship = null, $parentIDValues = null)
	{
		// for some reason this part has to be separated from the same foreach followed by, a PHP bug perhaps?
		foreach ($tableStructure as $table => $relationships)
		{
			$tableData[$table] = $this->__select_table_data($table, $parentTable, $relationship, $parentIDValues);
		}
		
		foreach ($tableStructure as $table => $relationships)
		{
			if ($relationships)
			{
				for ($i = 0; $i < count($tableData[$table]); $i++)
				{
					$tableData[$table][$i]['assocData'] = array();
					
					// primary and foreign key values of the parent table record
					// this is for use in the associated tables
					if (preg_match("/\bis_/", $table))
					{
						$fields = $this->_CI->db->list_fields($parentTable);
					}
					else
					{
						$fields = $this->_CI->db->list_fields($table);
					}
					foreach ($fields as $field)
					{
						if (preg_match("/_id\b/", $field))
						{
							$fieldModel = substr($field, 0, -3);
							$fieldTable = plural($fieldModel);
							
							$parentIDValues[$fieldTable] = $tableData[$table][$i][$field];
						}
					}
					
					// iterates through relationships
					foreach ($relationships as $relationship => $assocTables)
					{
						// always passes the parent table name to all of the belongsToSelf associated tables (on every recursive level)
						if (preg_match("/\bis_/", $table))
						{
							$tableData[$table][$i]['assocData'] += $this->__populate_table_data($assocTables, $parentTable, $relationship, $parentIDValues);
						}
						else
						{
							$tableData[$table][$i]['assocData'] += $this->__populate_table_data($assocTables, $table, $relationship, $parentIDValues);
						}
						
					}
				}
			}
		}
		
		return $tableData;
	}
	
	/**
	 * Select table data
	 * retrieves individual table data (to be used in $this->__populate_table_data())
	 *
	 * @access private
	 * @param  string $table table name
	 * @param  string $parentTable table name of the parent
	 * @param  string $relationship (belongsTo | belongsToSelf | hasOneMany | hasAndBelongsToMany)
	 * @return array table data
	 **/
	function __select_table_data($table, $parentTable = null, $relationship = null, $parentIDValues = null)
	{
		$offset = $this->__offset;
		$limit  = $this->__limit;
		
		// initial table data (whatever the user specifies in $this->find())
		if (!$parentTable)
		{
			$this->__query_conditions();
			
			$query = $this->_CI->db->get($table, $limit, $offset);
		}
		else
		{
			// changes the table name to its parent table name if it's belongsToSelf (is_xxxxx_of)
			if (preg_match("/\bis_/", $table))
			{
				// model name used in the array (e.g. 'parent' for 'is_parent_of')
				$isOfName = substr($table, 3, -3);
				$table    = $parentTable;
			}
		
			$model       = singular($table);
			$parentModel = singular($parentTable);
		
			switch ($relationship)
			{
				case 'belongsToSelf':
					$query = $this->_CI->db->getwhere($table, array("{$isOfName}_id" => $parentIDValues[$table]), $limit, $offset);
					break;
				case 'belongsTo':
					$query = $this->_CI->db->getwhere($table, array("{$model}_id" => $parentIDValues[$table]), $limit, $offset);
					break;
				case 'hasOneMany':
					$query = $this->_CI->db->getwhere($table, array("{$parentModel}_id" => $parentIDValues[$parentTable]), $limit, $offset);
					break;
				case 'hasAndBelongsToMany':
					$conjTable = $this->__conjunctionTables[$parentTable][$table];
					$this->_CI->db->join($conjTable, "{$conjTable}.{$model}_id = {$table}.{$model}_id", 'left');
					$query = $this->_CI->db->getwhere($table, array("{$conjTable}.{$parentModel}_id" => $parentIDValues[$parentTable]), $limit, $offset);
					break;
				default:
					break;
			}
		}
		
		$result = $query->result_array();
		return $result;
	}
	
	/**
	 * Find neighbours
	 * finds the specified record data with its neighbours
	 * useful for pagination (previous, next), etc
	 *
	 * @access public
	 * @param  integer $id record id
	 * @param  string $table table name
	 * @param  integer $deep record limit number (both sides, so $deep = 1 means fetching 3 records all together)
	 * @param  mixed $matchingFields array: fields to be matched OR string: the field to be matched
	 * @param  integer $assocLevel recursion level (0 for none) for the records
	 * @return array table with associated data
	 **/
	function findNeighbours($id, $table, $deep = 1, $matchingFields = null, $assocLevel = 0)
	{
		$model = singular($table);
		
		// self (source record data)
		$self = $this->findId($id, $table, $deep, 1, $assocLevel);
		
		// neighbours on the left side
		$this->_CI->db->where($model.'_id <', $id);
		$this->__matchingFields($matchingFields, $table, $self);
		$this->_CI->db->orderby($model.'_id', 'DESC');
		$neighboursLeft  = $this->find($table, $deep, 1, $assocLevel);
		
		// neighbours on the right side
		$this->_CI->db->where($model.'_id >', $id);
		$this->__matchingFields($matchingFields, $table, $self);
		$neighboursRight = $this->find($table, $deep, 1, $assocLevel);
		
		// resorts data order
		if ($deep > 1)
		{
			$neighboursLeft[$table] = array_reverse($neighboursLeft[$table]);
		}
		
		$result[$table] = array_merge($neighboursLeft[$table], $self[$table], $neighboursRight[$table]);
		return $result;
	}
	
	/**
	 * Query conditions
	 *
	 * @access private
	 * @return void
	 **/
	function __query_conditions()
	{
		if (isset($this->__conditions))
		{
			foreach ($this->__conditions as $condition)
			{
				foreach ($condition as $method => $args)
				{
					@$this->_CI->db->$method($args[0], $args[1]);
				}
			}
		}
		
		unset($this->__conditions);
	}
	
	/**
	 * Matching fields
	 * takes the fields to be matched and executes 'where' queries
	 *
	 * @access private
	 * @param  mixed $matchingFields array: fields to be matched OR string: the field to be matched
	 * @param  string $table table name
	 * @param  array $self source record data
	 * @return void
	 **/
	function __matchingFields($matchingFields, $table, $self)
	{
		if ($matchingFields)
		{
			if (is_array($matchingFields))
			{
				foreach ($matchingFields as $matchingField)
				{
					$this->_CI->db->where($matchingField, $self[$table][0][$matchingField]);
				}
			}
			else
			{
				$this->_CI->db->where($matchingFields, $self[$table][0][$matchingFields]);
			}
		}
	}
	
	/**
	 * Create
	 * inserts a new record to the specified table
	 *
	 * @access public
	 * @param  string $model model name
	 * @return void
	 **/
	function create($model = null)
	{
		if (!isset($model))
		{
			$table = $this->table;
			$model = singular($table);
		}
		else
		{
			$table = plural($model);
		}
		
		$this->__rebuildFieldNames($table);
		
		$this->_CI->db->insert($table, $this->$model);
		
		// removes the object variable
		unset($this->$model);
	}
	
	/**
	 * Delete
	 * deletes a record
	 *
	 * @access public
	 * @param  integer $id record id
	 * @param  string $model model name
	 * @return void
	 **/
	function delete($id, $model = null)
	{
		if (!isset($model))
		{
			$table = $this->table;
			$model = singular($table);
		}
		else
		{
			$table = plural($model);
		}
		
		$this->_CI->db->delete($table, array($model.'_id' => $id));
	}
	
	/**
	 * Edit
	 * passes $id and $model data to $this->save()
	 *
	 * @access public
	 * @param  integer $id record id
	 * @param  string $model model name
	 * @return void
	 **/
	function edit($id, $model = null)
	{
		if (!isset($model))
		{
			$table = $this->table;
			$model = singular($table);
		}
		
		$this->__modifiedModel   = $model;
		$this->__modifiedModelId = $id;
	}
	
	/**
	 * Save
	 * saves / updates data!
	 *
	 * @access public
	 * @return object modified fields
	 **/
	function save()
	{
		$model = $this->__modifiedModel;
		$id    = $this->__modifiedModelId;
		
		$table = plural($model);
		
		$this->__rebuildFieldNames($table);
		
		$this->_CI->db->where($model.'_id', $id);
		$this->_CI->db->update($table, $this->$model);
		
		$return = $this->$model;
		
		// removes the object variable
		unset($this->$model);
		
		return $return;
	}
	
	/**
	 * Rebuild field names
	 * rebuilds shortened field names to full field names
	 *
	 * @access private
	 * @return void
	 **/
	function __rebuildFieldNames($table = null)
	{
		$model = singular($table);
		
		foreach ($this->$model as $field => $value)
		{
			// processes shortcut field names
			if (!preg_match("/_id\b/", $field) AND !preg_match("/\b{$model}/", $field))
			{
				// removes old field name
				$fieldOld = $field;
				unset($this->$model->$fieldOld);
				// builds new field name
				$field = $model.'_'.$field;
				$this->$model->$field = $value;
			}
		}
	}
	
	/**
	 * Method overloading
	 *
	 * @access private
	 * @param  string $method method name
	 * @param  array $args method arguments in array
	 * @return void
	 **/
	function __call($method, $args)
	{
		// finds data according to a field value
		if (preg_match("/\bfind/", $method) OR preg_match("/\bsearch/", $method))
		{
			// $args[0] = matching content
			// $args[1] = table name
			// $args[2] and onwards check $this->find();
			
			$model = singular($args[1]);
			
			// $substrlen is the string length of the keywords (find, search, etc)
			// find (WHERE)
			if (preg_match("/\bfind/", $method))
			{
				$substrlen = 4; // strlen('find')
				$function  = 'where';
			}
			// search (LIKE)
			else
			{
				$substrlen = 6; // strlen('search')
				$function  = 'like';
			}
			
			// completes field name
			// foreign key retain its field name
			// otherwise prepend it with model name and underscore
			if (preg_match("/\b{$model}/", $method) OR preg_match("/_id\b/", $method))
			{
				$field = strtolower(substr($method, $substrlen));
			}
			else
			{
				$field = singular($args[1]) . '_' . strtolower(substr($method, $substrlen));
			}
			
			$argsCon[] = $field;
			$argsCon[] = $args[0];
			$this->__conditions[] = array($function => $argsCon);
			
			return @$this->find($args[1], $args[2], $args[3], $args[4]);
		}
		elseif (preg_match("/\badd/", $method))
		{
			$model = strtolower(substr($method, 3));
			$this->create($model);
		}
		elseif (preg_match("/\bcreate/", $method))
		{
			$model = strtolower(substr($method, 6));
			$this->create($model);
		}
		elseif (preg_match("/\bedit/", $method))
		{
			$model = strtolower(substr($method, 4));
			$this->create($args[0], $model);
		}
		elseif (preg_match("/\bdel/", $method))
		{
			$model = strtolower(substr($method, 3));
			$this->delete($args[0], $model);
		}
		elseif (preg_match("/\bdelete/", $method))
		{
			$model = strtolower(substr($method, 6));
			$this->delete($args[0], $model);
		}
		else
		{
			switch ($method)
			{
				// proxies to CI Active Record functions
				case 'where':
				case 'orwhere':
				case 'like':
				case 'orlike':
				case 'groupby':
				case 'having':
				case 'orderby':
				case 'limit':
					$this->__conditions[] = array($method => $args);
					break;
				default:
					break;
			}
		}
	}
}

?>

It's fun to read what I wrote ages ago. An ORM that was heavily influenced (or should I say misguided) by CakePHP, lol.

CodeIgniter ORM (dug out from the graveyard)

php, codeigniter

PHP