composer.json

{
    "require": {
        "aura/sql": "~2.0.0",
        "aura/sqlquery": "~2.0.0"
    }
}

experiments.php

<?php
require __DIR__ . '/vendor/autoload.php';
use Aura\SqlQuery\QueryFactory;
use Aura\Sql\ExtendedPdo;
use Aura\Sql\Profiler;

class Post
{
    private $id;
    private $title;
    private $body;
    private $author;

    public function __construct($id, $title, $body, $author)
    {
        $this->id = $id;
        $this->title = $title;
        $this->body = $body;
        $this->author = $author;
    }

    public function id()
    {
        return $this->id;
    }

    public function title()
    {
        return $this->title;
    }

    public function body()
    {
        return $this->body;
    }

    public function author()
    {
        if ($this->author instanceof Closure) {
            $author = $this->author;
            $this->author = $author();
        }
        return $this->author;
    }
}

class Author
{
    private $name;
    private $id;

    public function __construct($id, $name)
    {
        $this->id = $id;
        $this->name = $name;
    }

    public function name()
    {
        return $this->name;
    }

    public function __tostring()
    {
        return $this->name();
    }
}

class PostRepository
{
    private $author_repository;
    private $table = 'posts';
    private $select;
    private $hydrate;
    private $connection;
    private $query_factory;

    public function __construct($connection, $query_factory, $author_repository)
    {
        $this->connection = $connection;
        $this->query_factory = $query_factory;
        $this->author_repository = $author_repository;
    }

    public function find($id, $hydrate = false)
    {
        $this->hydrate = $hydrate;
        $select = $this->query_factory->newSelect();
        $select->from($this->table . ' as p')
            ->bindValues(array('id' => $id));
        if (is_array($id)) {
            $select->where('p.id IN :id');
        } else {
            $select->where('p.id = :id');
        }
        if ($hydrate) {
            $select->cols(array('a.name', 'p.title', 'p.id', 'p.body', 'p.author_id'));
            $select->join(
                'LEFT',
                $this->author_repository->getTable() . ' as a',
                'p.author_id = a.id'
            );
        } else {
            $select->cols(array('p.title', 'p.id', 'p.body', 'p.author_id'));
        }
        $this->select = $select;
        return $this;
    }

    public function fetchOne()
    {
        if (! $this->select) {
            throw Exception("No query to execute");
        }
        $result = $this->connection->fetchOne($this->select->__toString(), $this->select->getBindValues());
        return $this->createObject($result);
    }

    protected function createObject($result)
    {
        if ($this->hydrate) {
            $author = new Author($result['author_id'], $result['name']);
        } else {
            $author_id = $result['author_id'];
            $author = function () use ($author_id) {
                return $this->author_repository->find($author_id)->fetchOne();
            };
        }
        return new Post($result['id'], $result['title'], $result['body'], $author);
    }

    public function getTable()
    {
        return $this->table;
    }
}

class AuthorRepository
{
    private $table = 'authors';
    private $select;
    private $connection;
    private $query_factory;

    public function __construct($connection, $query_factory)
    {
        $this->connection = $connection;
        $this->query_factory = $query_factory;
    }

    public function find($id)
    {
        $select = $this->query_factory->newSelect();
        $select->from($this->table)
            ->cols(array('id', 'name'))
            ->bindValues(array('id' => $id));
        if (is_array($id)) {
            $select->where('id IN :id');
        } else {
            $select->where('id = :id');
        }
        $this->select = $select;
        return $this;
    }

    public function fetchOne()
    {
        if (! $this->select) {
            throw Exception("No query to execute");
        }
        $result = $this->connection->fetchOne($this->select->__toString(), $this->select->getBindValues());
        return $this->createObject($result);
    }

    protected function createObject($result)
    {
        return new Author($result['id'], $result['name']);
    }

    public function getTable()
    {
        return $this->table;
    }
}

$query_factory = new QueryFactory('mysql');

// replace with your dbname, username and password
$pdo = new ExtendedPdo(
    'mysql:host=localhost;dbname=dbname',
    'username',
    'password',
    array(), // driver options as key-value pairs
    array()  // attributes as key-value pairs
);
$pdo->setProfiler(new Profiler);
$pdo->getProfiler()->setActive(true);

$author_repository = new AuthorRepository($pdo, $query_factory);
$post_repository = new PostRepository($pdo, $query_factory, $author_repository);
$post = $post_repository->find(1, true)->fetchOne();
echo " Post id " . $post->id() . PHP_EOL;
echo " Post title " . $post->title() . PHP_EOL;
echo " Post content " . $post->body() . PHP_EOL;
echo " Post author " . $post->author() . PHP_EOL;

// no joins
$post = $post_repository->find(2)->fetchOne();
echo " Post id " . $post->id() . PHP_EOL;
echo " Post title " . $post->title() . PHP_EOL;
echo " Post content " . $post->body() . PHP_EOL;
echo " Post author " . $post->author() . PHP_EOL;
$profiles = $pdo->getProfiler()->getProfiles();

foreach ($profiles as $profile) {
    echo " Function " . $profile['function'] . PHP_EOL;
    echo " Statement " . $profile['statement'] . PHP_EOL;
    echo " Bind values " . var_export($profile['bind_values'], true) . PHP_EOL;
}

sample.sql

-- Adminer 4.1.0 MySQL dump

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DROP TABLE IF EXISTS `authors`;
CREATE TABLE `authors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `authors` (`id`, `name`) VALUES
(1,	'Hari KT'),
(2,	'Paul M Jones');

DROP TABLE IF EXISTS `posts`;
CREATE TABLE `posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `body` text NOT NULL,
  `author_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `author_id` (`author_id`),
  CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `posts` (`id`, `title`, `body`, `author_id`) VALUES
(1,	'Hello World',	'Hello World Body',	1),
(2,	'Something else',	'Something else body',	2);

-- 2014-07-24 05:16:43

If you execute you will get something like

Post id 1
Post title Hello World
Post content Hello World Body
Post author Hari KT

Post id 2
Post title Something else
Post content Something else body
Post author Paul M Jones

Function : connect

Statement :

Bind values : array (
)

Function : perform

Statement :

SELECT
    a.name,
    p.title,
    p.id,
    p.body,
    p.author_id
FROM
    `posts` AS `p`
LEFT JOIN `authors` AS `a` ON p.author_id = a.id
WHERE
    p.id = :id

Bind values : array (
  'id' => 1,
)

Function : perform

Statement :

SELECT
    p.title,
    p.id,
    p.body,
    p.author_id
FROM
    `posts` AS `p`
WHERE
    p.id = :id
 Bind values array (
  'id' => 2,
)

Function : perform

Statement :

SELECT
    id,
    name
FROM
    `authors`
WHERE
    id = :id

Bind values : array (
  'id' => '2',
)

See one used the join and the other fetch without join. And only called when the call for author() is made.

I like it, it could make a interesting package.

Some thoughts:

Use the domain objects for the find*, create, update and delete methods.
Example:

$user = new User;
$user->name = 'Hari';

$user_rep->find($user); 

$hydrate on the find method doesn't seem quite right maybe $lazy_load = true?

Hey @galactic-void ,

I missed to notice your comments. I didn't get any notification. I have started to work on https://github.com/harikt/experiments .

Interested to hear your thoughts on the changes I made from here.