Skip to content

Data Mapper


NOTE

These components have been heavily influenced by Aura PHP and Atlas PHP

NOTE

The full implementation of a DataMapper is not yet complete. There are however a few components that can be used in any project, such as the Connection and Query/Select

Overview

The Data Mapper pattern as described by Martin Fowler in Patterns of Enterprise Application Architecture is:

NOTE

A layer of Mappers that moves data between objects and a database while keeping them independent of each other and the mapper itself.

The Phalcon\DataMapper namespace contains components to help with accessing your data source, with the Data Mapper.

PDO

Connection

One of the components required by this implementation is a PDO connector. The Phalcon\DataMapper\Pdo\Connection offers a wrapper to PHP's PDO implementation, making it easier to maintain connections.

Connecting to a source

Connecting to a database requires the DSN string as well as the username and the password of the account with permission to access the database we need to connect to.

The DSN is as follows:

Engine DSN
Mysql mysql:host=<host>;dbname=<database name>;charset=<charset>;port=<port>
Postgresql pgsql:host=<host>;dbname=<database name>
Sqlite sqlite:<file>

You will only need to substitute the values in <> with the respective values for your environment. The charset and port are optional for Mysql. For Sqlite you can use memory as the <file> but the database will not persist. A file name in an appropriate location will create the necessary storage file for Sqlite.

<?php

use Phalcon\DataMapper\Connection;

$host     = '127.0.0.1';
$database = 'phalon_test';
$charset  = 'utf8mb4';
$port     = 3306;
$username = 'phalcon';
$password = 'secret';

$dsn = sprintf(
    "mysql:host=%s;dbname=%s;charset=%s;port=%s",
    $host,
    $database,
    $charset,
    $port
);

$connection = new Connection($dsn, $username, $password);

$sql = '
    SELECT 
        inv_id, 
        inv_title 
    FROM 
        co_invoices 
    WHERE 
        inv_cst_id = :cst_id
';

$bind = [
    'cst_id' => 1
];

$result = $connection->fetchAll($statement, $bind);

Methods

public function __construct(
    string $dsn,
    string $username = null,
    string $password = null,
    array $options = [],
    array $queries = [],
    ProfilerInterface $profiler = null
)
Constructs the object. The $dsn, $username and $password are used to connect to the source. The $options allows for additional PDO options to be specified. The $queries array contains a list of queries that will be executed when the connection is established. The $profiler is an optional object implementing the ProfilerInterface interface, used to profile the connection.

public function __debugInfo():  array
The purpose of this method is to hide sensitive data from stack traces (such as usernames, passwords).

public function beginTransaction(): bool
Begins a transaction. If the profiler is enabled, the operation will be recorded.

public function commit(): bool
Commits the existing transaction. If the profiler is enabled, the operation will be recorded.

abstract public function connect(): void;
Connects to the database.

abstract public function disconnect(): void;
Disconnects from the database.

public function errorCode(): string | null
Gets the most recent error code.

public function errorInfo(): array
Gets the most recent error info.

public function exec(string $statement): int
Executes an SQL statement and returns the number of affected rows. If the profiler is enabled, the operation will be recorded.

public function fetchAffected(string $statement, array $values = []): int
Performs a statement and returns the number of affected rows.

public function fetchAll(string $statement, array $values = []): array
Fetches a sequential array of rows from the database; the rows are returned as associative arrays.

public function fetchAssoc(string $statement, array $values = []): array
Fetches an associative array of rows from the database; the rows are returned as associative arrays, and the array of rows is keyed on the first column of each row.

If multiple rows have the same first column value, the last row with that value will overwrite earlier rows. This method is more resource intensive and should be avoided if possible.

public function fetchColumn(
    string $statement,
    array $values = [],
    int $column = 0
): array
Fetches a column of rows as a sequential array (default first one).

public function fetchGroup(
    string $statement,
    array $values = [],
    int $flags = \PDO::FETCH_ASSOC
): array 
Fetches multiple from the database as an associative array. The first column will be the index key. The default flags are PDO::FETCH_ASSOC | PDO::FETCH_GROUP

public function fetchObject(
    string $statement,
    array $values = [],
    string $className = "stdClass",
    array $arguments = []
): object 
Fetches one row from the database as an object where the column values are mapped to object properties.

Since PDO injects property values before invoking the constructor, any initializations for defaults that you potentially have in your object's constructor, will override the values that have been injected by fetchObject. The default object returned is \stdClass

public function fetchObjects(
    string $statement,
    array $values = [],
    string $className = "stdClass",
    array $arguments = []
): array {
Fetches a sequential array of rows from the database; the rows are returned as objects where the column values are mapped to object properties.

Since PDO injects property values before invoking the constructor, any initializations for defaults that you potentially have in your object's constructor, will override the values that have been injected by fetchObject. The default object returned is \stdClass

public function fetchOne(string $statement, array $values = []): array
Fetches one row from the database as an associative array.

public function fetchPairs(string $statement, array $values = []): array
Fetches an associative array of rows as key-value pairs (first column is the key, second column is the value).

public function fetchValue(string $statement, array $values = [])
Fetches the very first value (i.e., first column of the first row).

public function getAdapter(): \PDO
Return the inner PDO (if any)

public function getAttribute(int $attribute): var
Retrieve a database connection attribute

public static function getAvailableDrivers(): array
Return an array of available PDO drivers (empty array if none available)

public function getDriverName(): string
Return the driver name

public function getProfiler(): <ProfilerInterface>
Returns the Profiler instance.

public function getQuoteNames(string $driver = ""): array
Gets the quote parameters based on the driver

public function inTransaction(): bool
Is a transaction currently active? If the profiler is enabled, the operation will be recorded. If the profiler is enabled, the operation will be recorded.

public function isConnected(): bool
Is the PDO connection active?

public function lastInsertId(string $name = null): string
Returns the last inserted autoincrement sequence value. If the profiler is enabled, the operation will be recorded.

public function perform(
    string $statement,
    array $values = []
): \PDOStatement
Performs a query with bound values and returns the resulting PDOStatement; array $values will be passed through quote() and their respective placeholders will be replaced in the query string. If the profiler is enabled, the operation will be recorded.

public function prepare(
    string $statement,
    array $options = []
): \PDOStatement
Prepares an SQL statement for execution.

public function query(string $statement, ...$fetch): <\PDOStatement> | bool
Queries the database and returns a PDOStatement. If the profiler is enabled, the operation will be recorded.

public function quote(mixed $value, int $type = \PDO::PARAM_STR): string
Quotes a value for use in an SQL statement. This differs from PDO::quote() in that it will convert an array into a string of comma-separated quoted values. The default type is PDO::PARAM_STR

public function rollBack(): bool
Rolls back the current transaction, and restores autocommit mode. If the profiler is enabled, the operation will be recorded.

public function setAttribute(int $attribute, mixed $value): bool
Set a database connection attribute

public function setProfiler(ProfilerInterface $profiler)
Sets the Profiler instance.

protected function fetchData(
    string $method,
    array $arguments,
    string $statement,
    array $values = []
): array
Helper method to get data from PDO based on the method passed

protected function performBind(
    \PDOStatement $statement,
    mixed $name,
    mixed $arguments
): void
Bind a value using the proper PDO::PARAM_* type.

Connection - Decorated

ConnectionLocator

Applications with high traffic may utilize multiple database servers. For instance, one could employ a high-powered database server for writes, while smaller ones with memory based tables for reads.

The Phalcon\DataMapper\ConnectionLocator allows you to define multiple Phalcon\DataMapper\Pdo\Connection objects for reading and writing. All these objects are lazy-loaded, instantiated only when necessary.

Instantiation

The easiest way to create a Phalcon\DataMapper\ConnectionLocator to instantiate it and pass a Phalcon\DataMapper\Pdo\Connection object to it. Additionally, the constructor can optionally receive two arrays, one for the write connections and one for the read connections. The first connection is always the master one.

$host     = '127.0.0.1';
$database = 'phalon_test';
$charset  = 'utf8mb4';
$port     = 3306;
$username = 'phalcon';
$password = 'secret';

$dsn = sprintf(
    "mysql:host=%s;dbname=%s;charset=%s;port=%s",
    $host,
    $database,
    $charset,
    $port
);

$connection = new Connection($dsn, $username, $password);

$locator = new ConnectionLocator($connection);

Methods

public function __construct(
    ConnectionInterface $master,
    array $read = [],
    array $write = []
)
Constructor.

public function getMaster():  ConnectionInterface
Returns the default connection object.

public function getRead(string $name = ""):  ConnectionInterface
Returns a read connection by name; if no name is given, picks a random connection; if no read connections are present, returns the default connection.

public function getWrite(string $name = ""):  ConnectionInterface
Returns a write connection by name; if no name is given, picks a random connection; if no write connections are present, returns the default connection.

public function setMaster(ConnectionInterface $callableObject):  ConnectionLocatorInterface
Sets the default connection factory.

public function setRead(
    string $name,
    callable $callableObject
):  ConnectionLocatorInterface
Sets a read connection factory by name.

public function setWrite(
    string $name,
    callable $callableObject
): ConnectionLocatorInterface
Sets a write connection factory by name.

protected function getConnection(
    string $type,
    string $name = ""
):  ConnectionInterface
Returns a connection by name.

Configuration

Once the Phalcon\DataMapper\ConnectionLocator is created, you can add as many additional read or write connections as required. You can do so either during the construction of the locator or at runtime.

Runtime

First, you create the Phalcon\DataMapper\ConnectionLocator object with the master connection. The master connection is the connection that will be used when read or write connections are not defined.

<?php

$locator = new ConnectionLocator(
    function () use ($options) {
        return new Connection(
            'mysql:host=10.4.6.1;dbname=phalcon_db;charset=utf8mb4;port=3306',
            'username', 
            'password'
        );
    }
);

Now you can add as many read and write servers as required

<?php

// Write: master
$locator->addRead(
    'master',
    function () {
        return new Connection(
            'mysql:host=10.4.4.1;dbname=phalcon_db;charset=utf8mb4;port=3306',
            'username', 
            'password'
        );
    }
);

// Read: slave01
$locator->addRead(
    'slave01',
    function () {
        return new Connection(
            'mysql:host=10.4.8.1;dbname=phalcon_db;charset=utf8mb4;port=3306',
            'username', 
            'password'
        );
    }
);

// Read: slave02
$locator->addRead(
    'slave02',
    function () {
        return new Connection(
            'mysql:host=10.4.8.2;dbname=phalcon_db;charset=utf8mb4;port=3306',
            'username', 
            'password'
        );
    }
);

// Read: slave03
$locator->addRead(
    'slave03',
    function () {
        return new Connection(
            'mysql:host=10.4.8.3;dbname=phalcon_db;charset=utf8mb4;port=3306',
            'username', 
            'password'
        );
    }
);

On construction

You can also set everything up when the locator is being constructed. This is particularly useful when setting up the locator as a service in a DI container.

<?php

// Set up write connections
$write = [
    'master' => function () {
        return new Connection(
            'mysql:host=10.4.4.1;dbname=phalcon_db;charset=utf8mb4;port=3306',
            'username', 
            'password'
        );
    }
];

// Set up read connections
$read = [
    'slave01' => function () {
        return new Connection(
            'mysql:host=10.4.8.1;dbname=phalcon_db;charset=utf8mb4;port=3306',
            'username', 
            'password'
        );
    },
    'slave02' => function () {
        return new Connection(
            'mysql:host=10.4.8.2;dbname=phalcon_db;charset=utf8mb4;port=3306',
            'username', 
            'password'
        );
    },
    'slave03' => function () {
        return new Connection(
            'mysql:host=10.4.8.3;dbname=phalcon_db;charset=utf8mb4;port=3306',
            'username', 
            'password'
        );
    }
];

$locator = new ConnectionLocator(
    function () use ($options) {
        return new Connection(
            'mysql:host=10.4.6.1;dbname=phalcon_db;charset=utf8mb4;port=3306',
            'username', 
            'password'
        );
    },
    $read,
    $write
);

Getting Connections

Getting a connection from the locator will instantiate the object if it is not instantiated yet and then return it.

You can retrieve a specific read or write connection by passing its name (as it was registered), to the getRead() or getWrite() methods.

Profiler

The Phalcon\DataMapper\Profiler\Profiler is a component that allows you to profile database connections. That entails logging which queries have been executed and where they came from in the codebase, as well as what their execution time is. The Phalcon\DataMapper\Profiler\Profiler accepts a Phalcon\Logger\Logger object to log all the information collected to a file. By default, the Phalcon\DataMapper\Profiler\MemoryLogger is used.

The Phalcon\DataMapper\Profiler\Profiler can be activated by calling the setActive() method. The method accepts a boolean flag, which serves also as the deactivation method. Data is only logged when the profiler is active.

<?php

use Phalcon\DataMapper\Connection;
use Phalcon\DataMapper\Profiler\MemoryLogger;
use Phalcon\DataMapper\Profiler\Profiler;

$host     = '127.0.0.1';
$database = 'phalon_test';
$charset  = 'utf8mb4';
$port     = 3306;
$username = 'phalcon';
$password = 'secret';

$dsn = sprintf(
    "mysql:host=%s;dbname=%s;charset=%s;port=%s",
    $host,
    $database,
    $charset,
    $port
);

$profiler   = new Profiler(new MemoryLogger());
$connection = new Connection(
    $dsn, 
    $username, 
    $password,
    [
        PDO::ATTR_EMULATE_PREPARES => true, // PDO options
    ],
    [
        'SET NAMES utf8mb4', // startup queries
    ],
    $profiler
);

// Same profiler as the one we created above
$profiler = $connection->getProfiler();
$profiler->setActive(true)
and to retrieve the data stored:

<?php

$data = $connection->getProfiler()->getLogger()->getMessages();

var_dump($messages);

The messages are logged by default according to this pattern:

"{method} ({duration}s): {statement} {backtrace}"

You can customize the message format using the setLogFormat() on the profiler

<?php

$connection
    ->getProfiler()
    ->setLogFormat("{duration}: {method} {statement}{values}")

The parameters available are:

Parameter Description
{backtrace} The backtrace of where the query was executed
{duration} The execution duration for the query
{finish} The microtime when the profile finished
{method} The method that was called the connection
{start} The microtime when the profile began
{statement} The query executed
{values} Any values passed to the query

NOTE

The parameters must be enclosed in curly brackets {}

Query

Factory

The Phalcon\DataMapper\Query namespace offers a handy factory, which allows for a quick and easy creation of query objects, whether this is select, insert, update or `delete. The methods exposed by the Phalcon\DataMapper\Query\QueryFactory accept a Phalcon\DataMapper\Connection, binding the resulting object with the connection.

Methods

public function __construct(string selectClass = "")
QueryFactory constructor. Optionally accepts the name of a class that can be used for Select statements. By default, it is Phalcon\DataMapper\Query\Select.

public function newBind(): Bind
Create a new Bind object

public function newDelete(Connection $connection): Delete
Create a new Delete object

public function newInsert(Connection $connection): Insert
Create a new Insert object

public function newSelect(Connection $connection): Select
Create a new Select object

public function newUpdate(Connection $connection): Update
Create a new Update object

<?php

use Phalcon\DataMapper\Connection;
use Phalcon\DataMapper\Query\QueryFactory;

$host     = '127.0.0.1';
$database = 'phalon_test';
$charset  = 'utf8mb4';
$port     = 3306;
$username = 'phalcon';
$password = 'secret';

$dsn = sprintf(
    "mysql:host=%s;dbname=%s;charset=%s;port=%s",
    $host,
    $database,
    $charset,
    $port
);

$connection = new Connection($dsn, $username, $password);
$factory    = new QueryFactory();
$select     = $factory->newSelect($connection);

Delete

Methods

public function __construct(Connection $connection, Bind $bind)
Delete constructor.

public function andWhere(
    string $condition, 
    mixed $value = null, 
    int $type = -1
): Delete
Sets a AND for a WHERE condition

public function appendWhere(
    string $condition, 
    mixed $value = null, 
    int $type = -1
): Delete
Concatenates to the most recent WHERE clause

public function bindInline(mixed $value, int $type = -1): string
Binds a value inline

public function bindValue(string key, mixed $value, int $type = -1): Delete
Binds a value - auto-detects the type if necessary

public function bindValues(array values): Delete
Binds an array of values

public function from(string table): Delete
Adds table(s) in the query

public function getBindValues(): array
Returns all the bound values

public function getStatement(): string
@return string

public function limit(int $limit): Delete
Sets the LIMIT clause

public function offset(int $offset): Delete
Sets the OFFSET clause

public function orderBy(var $orderBy): Delete
Sets the ORDER BY

public function orWhere(
    string $condition, 
    mixed $value = null, 
    int $type = -1
): Delete
Sets a OR for a WHERE condition

public function perform()
Performs a statement in the connection

public function quoteIdentifier(
    string $name, 
    int $type = \PDO::PARAM_STR
): string 
Quotes the identifier

public function reset(): Delete
Resets the internal array

public function resetColumns(): Delete
Resets the columns

public function resetFlags(): Delete
Resets the flags

public function resetFrom(): Delete
Resets the from

public function resetGroupBy(): Delete
Resets the group by

public function resetHaving(): Delete
Resets the having

public function resetLimit(): Delete
Resets the limit and offset

public function resetOrderBy(): Delete
Resets the order by

public function resetWhere(): Delete
Resets the where

public function returning(array $columns): Delete
Adds the RETURNING clause

public function setFlag(string $flag, bool $enable = true): void
Sets a flag for the query such as "DISTINCT"

public function where(
    string $condition, 
    mixed $value = null, 
    int $type = -1
): Delete
Sets a WHERE condition

public function whereEquals(array $columnsValues): Delete
sw

protected function addCondition(
    string $store, 
    string $andor, 
    string $condition, 
    mixed $value = null, 
    int $type = -1
): void 
Appends a conditional

protected function appendCondition(
    string $store, 
    string $condition, 
    mixed $value = null, 
    int $type = -1
): void 
Concatenates a conditional

protected function buildBy(string $type): string
Builds a BY list

protected function buildCondition(string $type): string
Builds the conditional string

protected function buildFlags()
Builds the flags statement(s)

protected function buildLimitEarly(): string
Builds the early LIMIT clause - MS SQLServer

protected function buildLimit(): string
Builds the LIMIT clause

protected function buildLimitCommon(): string
Builds the LIMIT clause for all drivers

protected function buildLimitSqlsrv(): string
Builds the LIMIT clause for MSSQLServer

protected function buildReturning(): string
Builds the RETURNING clause

protected function indent(array $collection, string $glue = ""): string
Indents a collection

protected function processValue(string $store, mixed $data): void
Processes a value (array or string) and merges it with the store

1.2.7. DELETE 1.2.7.1. Building The Statement 1.2.7.1.1. FROM Use the from() method to specify FROM expression.

$delete->from('foo'); 1.2.7.1.2. WHERE (All WHERE methods support implicit and sprintf() inline value binding.)

The Delete WHERE methods work just like their equivalent Select methods:

where() and andWhere() AND a WHERE condition orWhere() ORs a WHERE condition catWhere() concatenates onto the end of the most-recent WHERE condition whereSprintf() and andWhereSprintf() AND a WHERE condition with sprintf() orWhereSprintf() ORs a WHERE condition with sprintf() catWhereSprintf() concatenates onto the end of the most-recent WHERE condition with sprintf() 1.2.7.1.3. ORDER BY Some databases (notably MySQL) recognize an ORDER BY clause. You can add one to the Delete with the orderBy() method; pass each expression as a variadic argument.

// DELETE ... ORDER BY foo, bar, baz $delete ->orderBy('foo') ->orderBy('bar', 'baz'); 1.2.7.1.4. LIMIT and OFFSET Some databases (notably MySQL and SQLite) recognize a LIMIT clause; others (notably SQLite) recognize an additional OFFSET. You can add these to the Delete with the limit() and offset() methods:

// LIMIT 10 OFFSET 40 $delete ->limit(10) ->offset(40); 1.2.7.1.5. RETURNING Some databases (notably PostgreSQL) recognize a RETURNING clause. You can add one to the Delete using the returning() method, specifying columns as variadic arguments.

// DELETE ... RETURNING foo, bar, baz $delete ->returning('foo') ->returning('bar', 'baz'); 1.2.7.1.6. Flags You can set flags recognized by your database server using the setFlag() method. For example, you can set a MySQL LOW_PRIORITY flag like so:

// DELETE LOW_PRIORITY foo WHERE baz = :1_1 $delete ->from('foo') ->where('baz = ', $baz_value) ->setFlag('LOW_PRIORITY');

Insert

Methods

public function __construct(Connection $connection, Bind $bind)
Insert constructor.

public function bindInline(mixed $value, int $type = -1): string
Binds a value inline

public function bindValue(string $key, mixed $value, int $type = -1): Insert
Binds a value - auto-detects the type if necessary

public function bindValues(array $values): Insert
Binds an array of values

public function column(string $column, mixed $value = null, int $type = -1): Insert
Sets a column for the INSERT query

public function columns(array $columns): Insert
Mass sets columns and values for the INSERT

public function getBindValues(): array
Returns all the bound values

public function getLastInsertId(string $name = null): string
Returns the id of the last inserted record

public function getStatement(): string
Returns the statement produced

public function into(string $table): Insert
Adds table(s) in the query

public function perform()
Performs a statement in the connection

public function quoteIdentifier(string $name, int $type = \PDO::PARAM_STR): string {
Quotes the identifier

public function reset(): Insert
Resets the internal array

public function resetColumns(): Insert
Resets the columns

public function resetFlags(): Insert
Resets the flags

public function resetFrom(): Insert
Resets the from

public function resetGroupBy(): Insert
Resets the group by

public function resetHaving(): Insert
Resets the having

public function resetLimit(): Insert
Resets the limit and offset

public function resetOrderBy(): Insert
Resets the order by

public function resetWhere(): Insert
Resets the where

public function returning(array $columns): Insert
Adds the RETURNING clause

public function set(string $column, mixed $value = null): Insert
Sets a column = value condition

public function setFlag(string $flag, bool $enable = true): void
Sets a flag for the query such as DISTINCT

protected function buildFlags()
Builds the flags statement(s)

protected function buildReturning(): string
Builds the RETURNING clause

protected function indent(array $collection, string $glue = ""): string
Indents a collection

Activation

To instantiate a Phalcon\DataMapper\Query\Insert builder, you can use the Phalcon\DataMapper\Query\QueryFactory with a Phalcon\DataMapper\Connection.

<?php

use Phalcon\DataMapper\Connection;
use Phalcon\DataMapper\Query\QueryFactory;

$host     = '127.0.0.1';
$database = 'phalon_test';
$charset  = 'utf8mb4';
$port     = 3306;
$username = 'phalcon';
$password = 'secret';

$dsn = sprintf(
    "mysql:host=%s;dbname=%s;charset=%s;port=%s",
    $host,
    $database,
    $charset,
    $port
);

$connection = new Connection($dsn, $username, $password);
$factory    = new QueryFactory();
$insert     = $factory->newInsert($connection);

Build

The into() method is used to specify the table to insert data to.

$insert->into('co_invoices');
Columns

You can use the column() method to specify a column and its bound value. The last optional parameter is the bind type used by PDO. This is set automatically for string, integer, float and null values.

$insert
    ->into('co_invoices')
    ->column('inv_total', 100.12)
;
// INSERT INTO co_invoices (inv_total) VALUES (:inv_total)

The columns() method returns the object back, thus offering a fluent interface:

$insert
    ->into('co_invoices')
    ->column('inv_cst_id', 2)
    ->column('inv_total', 100.12);
    ->column('inv_status_flag', 0, PDO::PARAM_BOOL)
;
// INSERT INTO co_invoices (
//      inv_cst_id,
//      inv_total,
//      inv_status_flag
// ) VALUES (
//      :inv_cst_id,
//      :inv_total,
//      :inv_status_flag
// )

You can also use the columns() method which accepts an array of elements. If the key is a string it is considered the field name, and its value will be the value of the field. Alternatively, for an array element with a numeric key, the value of that element will be the field name.

$insert
    ->into('co_invoices')
    ->columns(
        [
            'inv_cst_id', 
            'inv_total' => 100.12
        ]
    )
;
// INSERT INTO co_invoices (
//      inv_cst_id,
//      inv_total
// ) VALUES (
//      :inv_cst_id,
//      :inv_total
// )

NOTE

When using the columns() method, you cannot define the PDO type of each field/value pair.

Values

Bound values are automatically quoted and escaped. There are however cases, where we need to set a specific value to a field without it being escaped. A common example is to utilize the NOW() keyword assigned to a date field. For that purpose, we can use the set() method.

$insert
    ->into('co_invoices')
    ->column('inv_total', 100.12)
    ->set('inv_created_date', 'NOW()')
;
// INSERT INTO co_invoices (
//      inv_total,
//      inv_created_date
// ) VALUES (
//      :inv_total,
//      NOW()
// )
Statement

The object can return the constructed statement by calling the getStatement() method.

$insert
    ->into('co_invoices')
    ->column('inv_total', 100.12)
    ->set('inv_created_date', 'NOW()')
;

echo $insert->getStatement();
// INSERT INTO co_invoices (
//      inv_total,
//      inv_created_date
// ) VALUES (
//      :inv_total,
//      NOW()
// )
Returning

Some databases (notably PostgreSQL) recognize a RETURNING clause. You can use the returning() method to do so, passing an array of fields to be returned.

$insert
    ->into('co_invoices')
    ->columns(
        [
            'inv_cst_id', 
            'inv_total' => 100.12
        ]
    )
    ->set('inv_id', null)
    ->set('inv_status_flag', 1)
    ->set('inv_created_date', 'NOW()')
    ->columns(
        [
            'inv_cst_id' => 1
        ]
    )
    ->returning(
        [
            'inv_id', 
            'inv_cst_id'
        ]
    )
    ->returning(
        [
            'inv_total'
        ]
    )
    ->set('inv_created_date', 'NOW()')
;

echo $insert->getStatement();
// INSERT INTO co_invoices (
//      inv_cst_id, 
//      inv_total, 
//      inv_id, 
//      inv_status_flag, 
//      inv_created_date
// ) VALUES (
//      :inv_cst_id, 
//      :inv_total, 
//      NULL, 
//      1, 
//      NOW()
// ) 
// RETURNING inv_id, inv_cst_id, inv_total
Flags

You can set flags recognized by your database server using the setFlag() method. For example, you can set a MySQL LOW_PRIORITY flag as follows:

$insert
    ->into('co_invoices')
    ->column('inv_total', 100.12)
    ->set('inv_created_date', 'NOW()')
    ->setFlag('LOW_PRIORITY')
;

echo $insert->getStatement();
// INSERT LOW_PRIORITY INTO co_invoices (
//      inv_total,
//      inv_created_date
// ) VALUES (
//      :inv_total,
//      NOW()
// )

Select

Activation

To instantiate a Phalcon\DataMapper\Query\Select builder, you can use the Phalcon\DataMapper\Query\QueryFactory with a Phalcon\DataMapper\Connection.

<?php

use Phalcon\DataMapper\Connection;
use Phalcon\DataMapper\Query\QueryFactory;

$host     = '127.0.0.1';
$database = 'phalon_test';
$charset  = 'utf8mb4';
$port     = 3306;
$username = 'phalcon';
$password = 'secret';

$dsn = sprintf(
    "mysql:host=%s;dbname=%s;charset=%s;port=%s",
    $host,
    $database,
    $charset,
    $port
);

$connection = new Connection($dsn, $username, $password);
$factory    = new QueryFactory();
$select     = $factory->newSelect($connection);

Build

Columns

To add columns to the Select, use the columns() method and pass the columns as an array. If a key is defined as a string, it will be used as an alias for the column.

Column Names

<?php

$columns = [
    'inv_id', 
    'inv_cst_id', 
    'inv_status_flag', 
    'inv_title', 
    'inv_total', 
    'inv_created_at',
];

$select->columns($columns);

// SELECT
//      inv_id,
//      inv_cst_id,
//      inv_status_flag,
//      inv_title,
//      inv_total,
//      inv_created_at

Aliases

<?php

$columns = [
    'id'         => 'inv_id', 
    'customerId' => 'inv_cst_id', 
    'status'     => 'inv_status_flag', 
    'title'      => 'inv_title', 
    'total'      => 'inv_total', 
    'createdAt'  => 'inv_created_at',
];

$select->columns($columns);

// SELECT 
//      id, 
//      customerId, 
//      status, 
//      title, 
//      total, 
//      createdAt

Count

<?php

$columns = [
    'customerId' => 'inv_cst_id', 
    'totalCount' => 'COUNT(inv_total)'
];

$select->columns($columns);

// SELECT 
//      customerId, 
//      COUNT(inv_total) AS totalCount
FROM

To add a FROM clause, use the from() method:

Direct

<?php

$select
    ->from('co_invoices')
;

// SELECT * FROM co_invoices

Alias

<?php

$select
    ->from('co_invoices AS i')
;

// SELECT * FROM co_invoices i
JOIN

To add a JOIN clause, use the join() method:

LEFT

<?php

$select
    ->from('co_invoices')
    ->join($select::JOIN_LEFT, 'co_customers', 'inv_cst_id = cst_id')
;

// SELECT * FROM co_invoices 
//  LEFT JOIN co_customers ON inv_cst_id = cst_id

RIGHT

<?php

$select
    ->from('co_invoices')
    ->join($select::JOIN_RIGHT, 'co_customers', 'inv_cst_id = cst_id')
;

// SELECT * FROM co_invoices 
//  RIGHT JOIN co_customers ON inv_cst_id = cst_id

INNER

<?php

$select
    ->from('co_invoices')
    ->join($select::JOIN_INNER, 'co_customers', 'inv_cst_id = cst_id')
;

// SELECT * FROM co_invoices 
//  INNER JOIN co_customers ON inv_cst_id = cst_id

NATURAL

<?php

$select
    ->from('co_invoices AS i')
    ->join($select::JOIN_NATURAL, 'co_customers', 'inv_cst_id = cst_id')
;

// SELECT * FROM co_invoices 
//  NATURAL JOIN co_customers ON inv_cst_id = cst_id

With Bind

<?php

$status = 1;
$select
    ->from('co_invoices')
    ->join(
        $select::JOIN_LEFT, 
        'co_customers', 
        'inv_cst_id = cst_id AND cst_status_flag = ',
        $status
    )
    ->appendJoin(' AND cst_name LIKE ', '%john%')
;

// SELECT * FROM co_invoices 
//  LEFT JOIN co_customers ON inv_cst_id = cst_id 
//      AND cst_status_flag = :__1__
//      AND cst_name LIKE :__2__
WHERE

To add WHERE conditions, use the where() method. Additional calls to where() will implicitly AND the subsequent condition.

Single

<?php

$invoiceId = 1;
$select
    ->from('co_invoices')
    ->where('inv_id > ', $invoiceId)
;

// SELECT * FROM co_invoices 
//  WHERE inv_id > :__1__

andWhere

<?php

$customerIds = [1, 2, 3];
$status      = 1;
$totalValue  = 100;
$select
    ->from('co_invoices')
    ->where('inv_id > 1')
    ->andWhere('inv_total > :total')
    ->andWhere('inv_cst_id IN ', $customerIds)
    ->appendWhere(' AND inv_status_flag = ' . $select->bindInline($status))
    ->bindValue('total', $totalValue)
;

// SELECT * FROM co_invoices 
//  WHERE inv_id > 1
//      AND inv_total > :total 
//      AND inv_cst_id IN (:__1__, :__2__, :__3__) 
//      AND inv_status_flag = :__4__

orWhere

<?php

$status      = 1;
$totalValue  = 100;
$select
    ->from('co_invoices')
    ->appendWhere('inv_total > ', $totalValue)
    ->orWhere("inv_status_flag = :status")
    ->bindValue('status', $status)
;

// SELECT * FROM co_invoices 
//  WHERE inv_total > :__1__ "
//      OR inv_status_flag = :status

whereEquals

There is an additional whereEquals() convenience method that adds a series of AND equality conditions for you based on an array of key-value pairs:

  • Given an array value, the condition will be IN ().
  • Given an empty array, the condition will be FALSE (which means the query will return no results).
  • Given a null value, the condition will be IS NULL.
  • For all other values, the condition will be =.
  • If you pass a key without a value, that key will be used as a raw unescaped condition.
<?php

$invoiceIds = [1, 2, 3];
$select
    ->from('co_invoices')
    ->whereEquals(
        [
            'inv_id'     => $invoiceIds,
            'inv_cst_id' => null,
            'inv_title'  => 'ACME',
            'inv_created_at = NOW()',
        ]
    )
;

// SELECT * FROM co_invoices 
//  WHERE inv_id IN (:__1__, :__2__, :__3__)
//      AND inv_cst_id IS NULL 
//      AND inv_title = :__4__ 
//      AND inv_created_at = NOW()
GROUP BY

To add GROUP BY expressions, use the groupBy() method and pass each expression as a variadic argument.

<?php

$select
    ->from('co_invoices')
    ->groupBy('inv_cst_id')
    ->groupBy('inv_status_flag')
;

// SELECT * FROM co_invoices 
//  GROUP BY inv_cst_id, inv_status_flag
HAVING

The HAVING methods work just like their equivalent WHERE methods:

  • having() and andHaving() AND a HAVING condition
  • orHaving() ORs a HAVING condition
  • appendHaving() concatenates onto the end of the most recent HAVING condition
ORDER BY

To add ORDER BY expressions, use the orderBy() method and pass each expression an element of an array.

<?php

$select
    ->from('co_invoices')
    ->orderBy(
        [
            'inv_cst_id',
            'UPPER(inv_title) DESC',
        ]
    )
;

// SELECT * FROM co_invoices 
//  ORDER BY inv_cst_id, UPPER(inv_title) DESC
LIMIT, OFFSET, Pagination

To set a LIMIT and OFFSET, use the limit() and offset() methods.

<?php

$select
    ->from('co_invoices')
    ->limit(10)
;

// SELECT * FROM co_invoices 
//  LIMIT 10

$select
    ->from('co_invoices')
    ->limit(10)
    ->offset(50)
;

// SELECT * FROM co_invoices 
//  LIMIT 10 OFFSET 50

Pagination

Alternatively, you can limit by "pages" using the page() and perPage() methods:

<?php

$select
    ->from('co_invoices')
    ->page(5)
    ->perPage(10)
;

// SELECT * FROM co_invoices 
//  LIMIT 10 OFFSET 5
DISTINCT

You can set the DISTINCT clause as follows:

<?php

$select
    ->distinct()
    ->from('co_invoices')
    ->columns(
        [
            'inv_id', 
            'inc_cst_id'
        ]
    )
;

// SELECT DISTINCT inv_id, inc_cst_id
// FROM co_invoices

NOTE

The method accepts an optional boolean parameter to enable (true) or disable (false) the flag.

FOR UPDATE

You can set the FOR UPDATE clause as follows:

<?php

$select
    ->from('co_invoices')
    ->forUpdate()
;

// SELECT * FROM co_invoices FOR UPDATE

$select
    ->from('co_invoices')
    ->forUpdate()
    ->forUpdate(false)
;

// SELECT * FROM co_invoices

NOTE

The method accepts an optional boolean parameter to enable (true) or disable (false) the flag.

Flags

You can set flags recognized by your database server using the setFlag() method. For example, you can set a MySQL HIGH_PRIORITY flag like so:

<?php

$select
    ->from('co_invoices')
    ->setFlag('HIGH_PRIORITY')
;

// SELECT HIGH_PRIORITY * FROM co_invoices
UNION

To UNION or UNION ALL the current Select with a followup statement, call one the union*() methods:

<?php

$select
    ->from('co_invoices')
    ->where('inv_id = 1')
    ->union()
    ->from('co_invoices')
    ->where('inv_id = 2')
    ->union()
    ->from('co_invoices')
    ->where('inv_id = 3')
;

// SELECT * FROM co_invoices WHERE inv_id = 1 UNION 
// SELECT * FROM co_invoices WHERE inv_id = 2 UNION
// SELECT * FROM co_invoices WHERE inv_id = 3

$select
    ->from('co_invoices')
    ->where('inv_id = 1')
    ->unionAll()
    ->from('co_invoices')
    ->where('inv_id = 2')
;

// SELECT * FROM co_invoices WHERE inv_id = 1 UNION ALL 
// SELECT * FROM co_invoices WHERE inv_id = 2 

Reset

The Select class exposes the reset() method, that allows you to reset the object to its original state and reuse it (e.g., to re-issue a statement to get a COUNT(*) without a LIMIT, to find the total number of rows to be paginated over).

Additionally, the following methods allow you to reset specific areas of the query:

  • resetColumns() - Resets the columns
  • resetFrom() - Resets the from
  • resetWhere() - Resets the where
  • resetGroupBy() - Resets the group by
  • resetHaving() - Resets the having
  • resetOrderBy() - Resets the order by
  • resetLimit() - Resets the limit and offset
  • resetFlags() - Resets the flags

Subselect Objects

If you want to create a subselect, call the subSelect() method. When you are done building the subselect, give it an alias using the asAlias() method; the object itself can be used in the desired condition or expression.

<?php

$select
    ->from(
        $select
            ->subSelect()
            ->columns("inv_id")
            ->from('co_invoices')
            ->asAlias('inv')
            ->getStatement()
    )
;

// SELECT *
// FROM (SELECT inv_id FROM co_invoices) AS inv 

Update