with dot in bind

[harikt]

Hi Paul,

I was trying something lately. The where was having a p.id = :p.id , but getting error like

PHP Notice:  Undefined index: p in /var/www/github.com/harikt/experiments/vendor/aura/sql/src/ExtendedPdo.php on line 1037
PHP Stack trace:
PHP   1. {main}() /var/www/github.com/harikt/experiments/closure-class.php:0
PHP   2. PostRepository->fetchOne() /var/www/github.com/harikt/experiments/closure-class.php:255
PHP   3. Aura\Sql\ExtendedPdo->fetchOne() /var/www/github.com/harikt/experiments/closure-class.php:124
PHP   4. Aura\Sql\ExtendedPdo->perform() /var/www/github.com/harikt/experiments/vendor/aura/sql/src/ExtendedPdo.php:451
PHP   5. Aura\Sql\ExtendedPdo->prepareWithValues() /var/www/github.com/harikt/experiments/vendor/aura/sql/src/ExtendedPdo.php:628
PHP   6. Aura\Sql\ExtendedPdo->rebuild() /var/www/github.com/harikt/experiments/vendor/aura/sql/src/ExtendedPdo.php:864
PHP   7. Aura\Sql\ExtendedPdo->prepareValuePlaceholders() /var/www/github.com/harikt/experiments/vendor/aura/sql/src/ExtendedPdo.php:943
PHP   8. Aura\Sql\ExtendedPdo->prepareNamedPlaceholder() /var/www/github.com/harikt/experiments/vendor/aura/sql/src/ExtendedPdo.php:976
PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'NULL.id' in 'where clause'' in /var/www/github.com/harikt/experiments/vendor/aura/sql/src/ExtendedPdo.php:630
Stack trace:
#0 /var/www/github.com/harikt/experiments/vendor/aura/sql/src/ExtendedPdo.php(630): PDOStatement->execute()
#1 /var/www/github.com/harikt/experiments/vendor/aura/sql/src/ExtendedPdo.php(451): Aura\Sql\ExtendedPdo->perform('SELECT\n    a.na...', Array)
#2 /var/www/github.com/harikt/experiments/closure-class.php(124): Aura\Sql\ExtendedPdo->fetchOne('SELECT\n    a.na...', Array)
#3 /var/www/github.com/harikt/experiments/closure-class.php(255): PostRepository->fetchOne()
#4 {main}
  thrown in /var/www/github.com/harikt/experiments/vendor/aura/sql/src/ExtendedPdo.php on line 630

I had a look into the problem and it is happening at rebuild

      for ($i = 0; $i <= $k; $i += 3) {

            // split into subparts by ":name" and "?"
            $subs = preg_split(
                "/(:[a-zA-Z_][a-zA-Z0-9_]*)|(\?)/m",
                $parts[$i],
                -1,
                PREG_SPLIT_DELIM_CAPTURE
            );

            // check subparts to convert bound arrays to quoted CSV strings
            $subs = $this->prepareValuePlaceholders($subs, $bind);

            // reassemble
            $parts[$i] = implode('', $subs);
        }

The full stuff what I am experimenting is

<?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 select($hydrate = false)
    {
        $this->hydrate = $hydrate;
        $this->select = $this->query_factory->newSelect();
        $this->select->from($this->table . ' as p');
        if ($hydrate) {
            $this->select->cols(array('a.name', 'p.title', 'p.id', 'p.body', 'p.author_id'));
            $this->select->join(
                'LEFT',
                $this->author_repository->getTable() . ' as a',
                'p.author_id = a.id'
            );
        } else {
            $this->select->cols(array('p.title', 'p.id', 'p.body', 'p.author_id'));
        }
        return $this;
    }

    public function where($where = array())
    {        
        if (! $this->select) {
            throw Exception("No query to execute");
        }
        foreach ($where as $col => $value) {
            if (is_array($value)) {
                $this->select->where("$col IN :$col");
            } else {
                $this->select->where("$col = :$col");
            }
            $this->select->bindValue($col, $value);
        }
        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);
    }

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

    protected function createObjects($results)
    {
        $objects = array();
        foreach ($results as $result) {
            $objects[] = $this->createObject($result);
        }
        return $objects;
    }

    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 select($id)
    {
        $this->select = $this->query_factory->newSelect();
        $this->select->from($this->table)
            ->cols(array('id', 'name'));        
        return $this;
    }

    public function where($where = array())
    {        
        if (! $this->select) {
            throw Exception("No query to execute");
        }
        foreach ($where as $col => $value) {
            if (is_array($value)) {
                $this->select->where("$col IN :$col");
            } else {
                $this->select->where("$col = :$col");
            }
            $this->select->bindValue($col, $value);
        }
        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);
    }

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

    protected function createObjects($results)
    {
        $objects = array();
        foreach ($results as $result) {
            $objects[] = $this->createObject($result);
        }
        return $objects;
    }

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

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

$query_factory = new QueryFactory('mysql');
$pdo = new ExtendedPdo(
    'mysql:host=localhost;dbname=closure',
    'root',
    'mysqlroot',
    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->select(true)
            ->where(array('p.id' => 1))
            ->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->select()
    ->where(array('p.id' => 2))
    ->fetchOne();
echo PHP_EOL . "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;

$post = $post_repository->select()
    ->fetchAll();

$profiles = $pdo->getProfiler()->getProfiles();

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

I don't know whether I should call it as repository or something else.

Interested to hear your thoughts. This is slightly modified than the one in gist https://gist.github.com/harikt/26d244f87db8061eff97 .

Somethings in mind are may be use a __call which can handle the select query instance , so can use the limit etc can be called or may be provide an abstract class.

Looking forward to hear your thoughts.

[harikt]

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
    `post`.`id` = :`post`.`id`

[pmjones]

this code:

:`post`.`id`

won't work either, I don't think. the : is only for named placeholders.

[harikt]

So @pmjones ,

I wonder what will you do to make the where dynamic ?

[pmjones]

p.id AS my_dynamic_value
WHERE my_dynamic_value = :foo

[harikt]

Closing this in favour of our discussion.
Thank you.