auto_relation.php

<?php

define('START_TIME', microtime(TRUE));

register_shutdown_function(function() {
	print dump(round(microtime(TRUE) - START_TIME, 3) . ' seconds');
	print dump(round(memory_get_peak_usage() / 1024) . " kb peak usage");
	print dump(DB::$q);
});


function dump() {
	foreach (func_get_args() as $value) {
		print "\n\n<pre>" . ($value ? print_r($value, true) : var_export($value)) . "</pre>\n\n";
	}
}



$config = array(
	'db' => array(
		'dsn' => 'mysql:dbname=api;host=localhost',
		'user' => 'root',
		'pass' => '',
	)
);


$pdo = new PDO(
	$config['db']['dsn'],
	$config['db']['user'],
	$config['db']['pass'],
	array(
		PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
		PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_CLASS,
		// override what statement class to use, and provide constructor arguments (found out by trial and error)
		PDO::ATTR_STATEMENT_CLASS => array('PDOStatementWithClass', array('DB_Row'))

	)
);

require('DB.php');

DB::$c = $pdo;


// http://stackoverflow.com/a/7617462/99923
class PDOStatementWithClass extends PDOStatement
{
    // PHP complained when I tried to make this public
    protected function __construct( $fetch_class = 'StdClass' )
    {
        parent::setFetchMode(PDO::FETCH_CLASS, $fetch_class);
    }
}


class DB_Row
{
	public static $schema;
	public static $tables;

	protected function loadSchema()
	{
		if(static::$schema) {
			return static::$schema;
		}

		$sql = "SELECT * FROM information_schema.columns"
			. " WHERE table_schema = DATABASE()"
			. " ORDER BY table_name, ordinal_position";

		$columns = array();
		foreach(DB::fetch($sql) as $column) {

			if($column) {

				$columns[$column->TABLE_NAME][$column->COLUMN_NAME] = array(
					'default' => $column->COLUMN_DEFAULT ?: null,
					'nullable' => $column->IS_NULLABLE === 'YES',
					'type' => $column->DATA_TYPE,
					'length' => $column->CHARACTER_MAXIMUM_LENGTH ?: null,
					'precision' => $column->NUMERIC_PRECISION ?: null,
					'scale' => $column->NUMERIC_SCALE ?: null,
					'comment' => $column->COLUMN_COMMENT ?: null,
					// PRI, MUL, etc...
					'index' => $column->COLUMN_KEY ? true : null,
					'primary' => $column->COLUMN_KEY === 'PRI',
					'unique' => $column->COLUMN_KEY === 'UNI',
				);
			}
		}

		$sql = "SELECT * FROM information_schema.KEY_COLUMN_USAGE"
			. " WHERE table_schema = DATABASE() AND REFERENCED_TABLE_NAME IS NOT NULL"
			. " ORDER BY table_name, ordinal_position";

		foreach(DB::fetch($sql) as $row) {
			$columns[$row->TABLE_NAME][$row->COLUMN_NAME]['table'] = $row->REFERENCED_TABLE_NAME;
			$columns[$row->TABLE_NAME][$row->COLUMN_NAME]['column'] = $row->REFERENCED_COLUMN_NAME;
		}

		// Add the lookups
		foreach($columns as $table => $fields) {
			static::$tables[md5(join('', array_keys($fields)))] = $table;
		}

		return static::$schema = $columns;
	}

	public function getTableName()
	{
		static::loadSchema();
		$hash = md5(join('', array_keys((array) $this)));
		// print dump(array_keys((array) $row), $hash);

		if(isset(static::$tables[$hash])) {
			return static::$tables[$hash];
		}
	}

	public function get($table) {

		if(isset($this->{$table . '_id'})) {
			$column = 'id';
			$id = $this->{$table . '_id'};
		} else {
			$column = $this->getTableName() . '_id';
			$id = $this->id;
		}

		return DB::fetch("SELECT * FROM $table WHERE $column = ?", array($id));
	}
}


$user = DB::row('SELECT * FROM user WHERE id = 3 LIMIT 1');

print dump($user);

$topics = $user->get('topic');

foreach($topics as $topic) {
	print dump($topic);
	$replies = $topic->get('reply');
	print dump($replies);
}

/* Is hashing the keys costly? No, only takes .001 ms on my macbook
$start = microtime(TRUE);
for ($i=0; $i < 1000; $i++) { 
	$keys = array_keys((array) $row);
	$hash = md5(join('', $keys));
	if($i === 1) { print dump($keys, $hash); }
}
print dump((microtime(TRUE) - $start) . ' seconds');
*/

output.txt

DB_Row Object
(
    [id] => 3
    [email] => [email protected]
    [name] => Alex Smith
    [updated_at] => 2014-10-07 15:34:29
    [created_at] => 
    [admin] => 
)

DB_Row Object
(
    [id] => 4
    [body] => This is the content of my new topic
    [title] => Another topic
    [forum_id] => 25
    [user_id] => 3
    [created_at] => 
    [updated_at] => 
)

Array
(
    [0] => DB_Row Object
        (
            [id] => 3
            [body] => This is the reply
            [title] => 
            [topic_id] => 4
            [user_id] => 3
            [created_at] => 2014-10-03 17:55:59
            [updated_at] => 2014-10-03 17:55:59
        )

    [1] => DB_Row Object
        (
            [id] => 5
            [body] => And another one
            [title] => 
            [topic_id] => 4
            [user_id] => 3
            [created_at] => 2014-10-27 15:19:14
            [updated_at] => 2014-10-27 15:19:14
        )

)

DB_Row Object
(
    [id] => 5
    [body] => Recently, while visiting some friends I was ...
    [title] => When the time comes
    [forum_id] => 25
    [user_id] => 3
    [created_at] => 
    [updated_at] => 
)

Array
(
    [0] => DB_Row Object
        (
            [id] => 4
            [body] => This is my reply
            [title] => 
            [topic_id] => 5
            [user_id] => 3
            [created_at] => 2014-10-03 18:05:06
            [updated_at] => 2014-10-03 18:05:06
        )

)

DB_Row Object
(
    [id] => 6
    [body] => 

With some body stuff and what not.


Hello world!



    [title] => My Topic
    [forum_id] => 25
    [user_id] => 3
    [created_at] => 2014-10-27 15:06:18
    [updated_at] => 2014-10-27 15:24:26
)

Array
(
    [0] => DB_Row Object
        (
            [id] => 6
            [body] => Hello world!
            [title] => 
            [topic_id] => 6
            [user_id] => 8
            [created_at] => 2014-10-30 18:34:37
            [updated_at] => 2014-10-30 18:34:37
        )

)

0.007 seconds

696 kb peak usage

Array
(
    [0] => SELECT * FROM user WHERE id = 3 LIMIT 1
    [1] => SELECT * FROM information_schema.columns WHERE table_schema = DATABASE() ORDER BY table_name, ordinal_position
    [2] => SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE table_schema = DATABASE() AND REFERENCED_TABLE_NAME IS NOT NULL ORDER BY table_name, ordinal_position
    [3] => SELECT * FROM topic WHERE user_id = ?
    [4] => SELECT * FROM reply WHERE topic_id = ?
    [5] => SELECT * FROM reply WHERE topic_id = ?
    [6] => SELECT * FROM reply WHERE topic_id = ?
)