Model with Query Buidler

Model.php

<?php
/**
 * \ND\Model
 * Model.php
 *
 * Model
 *
 * @author      Nikos Dimopoulos <nikos@niden.net>
 * @since       2012-12-12
 * @category    Library
 *
 */

namespace ND;

use \Phalcon\DI\FactoryDefault as PhDi;
use \Phalcon\Mvc\Model as PhModel;

class Model extends PhModel
{
    protected $prefix = '';

    private $fields   = array();
    private $aliases  = array();
    private $data     = array();

    public function initialize()
    {
        // We skip these since they are handled by the RDBMS
        $this->skipAttributes(
            [
                $this->prefix . '_created_id',
                $this->prefix . '_created_date',
                $this->prefix . '_updated_id',
                $this->prefix . '_updated_date',
            ]
        );

    }

    /**
     * GETTERS
     */
    public function getActive()
    {
        $field = $this->prefix . '_active_flag';

        return $this->$field;
    }

    public function getDeleted()
    {
        $field = $this->prefix . '_delete_flag';

        return $this->$field;
    }

    public function getCreatedId()
    {
        $field = $this->prefix . '_created_id';

        return $this->$field;
    }

    public function getCreatedDate()
    {
        $field = $this->prefix . '_created_date';

        return $this->$field;
    }

    public function getUpdatedId()
    {
        $field = $this->prefix . '_updated_id';

        return $this->$field;
    }

    public function getUpdatedDate()
    {
        $field = $this->prefix . '_updated_date';

        return $this->$field;
    }

    /**
     * SETTERS
     */
    public function setActive($active = false)
    {
        $field = $this->prefix . '_active_flag';

        $this->$field = (bool) $active;
    }

    public function setDeleted($deleted = false)
    {
        $field = $this->prefix . '_delete_flag';

        $this->$field = (bool) $deleted;
    }

    public function setCreatedId($createdId)
    {
        $field = $this->prefix . '_created_id';

        $this->$field = intval($createdId);
    }

    public function setCreatedDate($createdDate)
    {
        $field = $this->prefix . '_created_date';

        $this->$field = $createdDate;
    }

    public function setUpdatedId($updatedId)
    {
        $field = $this->prefix . '_updated_id';

        $this->$field = intval($updatedId);
    }

    public function setUpdatedDate($updatedDate)
    {
        $field = $this->prefix . '_updated_date';

        $this->$field = $updatedDate;
    }

    /**
     * Fetches one record from the database based on the unique id of that
     * record
     *
     * @author      Nikos Dimopoulos <nikos@niden.net>
     * @since       2013-05-27
     *
     * @param $id
     * @param $fields
     *
     * @return array
     */
    public function fetchById($id, $fields = '*')
    {
        $results = null;
        $cols    = '';

        try {

            $cols = $this->getFieldList($fields);

            $builder = $this->getBuilder();

            $builder->columns($cols);

            $field = $this->prefix . '_id';

            $bind[$field] = $id;

            $builder->where("{$field} = :{$field}:");

            $query = $builder->getQuery();

            // One record is needed here so set the unique row
            $query->setUniqueRow(true);

            // Execute!
            $results[] = $query->execute($bind);

        } catch (\Exception $e) {

            $results = self::exceptionToArray($e);

        }

        return $results;
    }

    public function fetchCount($deleted = false, $conditions = '')
    {
        try {

            $builder = $this->getBuilder();

            $builder->columns('COUNT(*) AS row_count');

            // Exclude deleted
            if (!$deleted) {
                $this->excludeDeleted($builder);
            }
            if ($conditions) {
                $builder->where($conditions);
            }

            $query = $builder->getQuery();

            // One record is needed here so set the unique row
            $query->setUniqueRow(true);

            // Execute!
            $results = $query->execute();
            $results = $results->row_count;

        } catch (\Exception $e) {

            $results = $this->exceptionToArray($e);

        }

        return $results;
    }

    public function fetchAll($deleted = false)
    {
        try {

            $builder = $this->getBuilder();

            // Exclude deleted
            if (!$deleted) {
                $this->excludeDeleted($builder);
            }

            $query = $builder->getQuery();

            // Execute!
            $results = $query->execute();

        } catch (\Exception $e) {

            $results = $this->exceptionToArray($e);

        }

        return $results;
    }

    public function getAPIFields()
    {
        $data = [
            ['value' => '*', 'description' => 'Returns the common fields'],
            ['value' => 'all', 'description' => 'Returns all fields'],
        ];

        foreach ($this->data as $field) {
            $data[] = [
                'value'       => $field['alias'],
                'description' => $field['description']
            ];
        }

        return $data;
    }

    /**
     * -------------------------------------------------------------------------
     * PROTECTED METHODS
     * -------------------------------------------------------------------------
     */

    /**
     * Returns the query builder
     *
     * @author      Nikos Dimopoulos <nikos@niden.net>
     * @since       2013-05-27
     *
     * @return mixed
     */
    protected function getBuilder()
    {
        $di      = PhDi::getDefault();
        $manager = $di['modelsManager'];
        $builder = $manager->createBuilder();
        $builder->from(get_called_class());

        return $builder;
    }

    /**
     * Executes the query on a builder
     *
     * @author      Nikos Dimopoulos <nikos@niden.net>
     * @since       2013-05-27
     *
     * @param        $builder   The builder
     * @param string $columns   Columns needed
     * @param array  $bind      Bound parameters
     * @param bool   $unique    One record or many
     *
     * @return array
     */
    protected function execute($builder, $columns = '*', $bind = array(), $unique = false)
    {
        $builder->columns($columns);

        $query = $builder->getQuery();

        // One record is needed here so set the unique row
        $query->setUniqueRow($unique);

        // Execute!
        $results = $query->execute($bind);

        if (!$results || count($results) == 0) {
            $results = array();
        }

        return $results;
    }

    /**
     * Includes active records in the builder
     *
     * @author      Nikos Dimopoulos <nikos@niden.net>
     * @since       2013-05-27
     *
     * @param $builder
     */
    protected function includeActive($builder)
    {
        $builder->andWhere($this->prefix . '_active_flag = 1');
    }

    /**
     * Excludes active records from the builder
     *
     * @author      Nikos Dimopoulos <nikos@niden.net>
     * @since       2013-05-27
     *
     * @param $builder
     */
    protected function excludeActive($builder)
    {
        $builder->andWhere($this->prefix . '_active_flag = 0');
    }

    /**
     * Includes deleted records in the builder
     *
     * @author      Nikos Dimopoulos <nikos@niden.net>
     * @since       2013-05-27
     *
     * @param $builder
     */
    protected function includeDeleted($builder)
    {
        $builder->andWhere($this->prefix . '_delete_flag = 1');
    }

    /**
     * Excludes deleted records from the builder
     *
     * @author      Nikos Dimopoulos <nikos@niden.net>
     * @since       2013-05-27
     *
     * @param $builder
     */
    protected function excludeDeleted($builder)
    {
        $builder->andWhere($this->prefix . '_delete_flag = 0');
    }

    /**
     * Transforms an exception to an array for reporting
     *
     * @author      Nikos Dimopoulos <nikos@niden.net>
     * @since       2013-05-27
     *
     * @param $exception
     *
     * @return array
     */
    protected function exceptionToArray($exception)
    {
        $results['error'] = array(
            'code'            => $exception->getCode(),
            'file'            => $exception->getFile(),
            'line'            => $exception->getLine(),
            'message'         => $exception->getMessage(),
            'trace'           => $exception->getTrace(),
            'trace_as_string' => $exception->getTraceAsString()
        );

        return $results;
    }

    /**
     * Adds the metadata fields in the internal arrays in bulk.
     *
     * @author      Nikos Dimopoulos <nikos@niden.net>
     * @since       2013-06-08
     *
     * @param array $meta         The fields to add
     */
    protected function addMetaFields($meta)
    {
        foreach ($meta as $item) {
            $this->addMetaField($item[0], $item[1], $item[2], $item[3], $item[4]);
        }
    }

    /**
     * Adds the metadata fields in the internal arrays.
     *
     * @author      Nikos Dimopoulos <nikos@niden.net>
     * @since       2013-05-27
     *
     * @param string $name         The name of the field
     * @param string $alias        The field alias for the API
     * @param string $common       Common field or not
     * @param string $validator    The field's validator
     * @param string $description  The field's description
     */
    protected function addMetaField($name, $alias, $common, $validator, $description)
    {
        $key = uniqid();

        // Add the field in the data
        $this->data[$key] = array(
            'name'        => (string) $name,
            'alias'       => (string) $alias,
            'common'      => (bool) $common,
            'validator'   => (string) $validator,
            'description' => (string) $description,
        );

        // Now add the mapped data in the fields array
        $this->fields[$name] = $key;

        // And the mapped data in the alias array
        $this->aliases[$alias] = $key;
    }

    /**
     * Adds the common fields in the metadata collection
     *
     * @author      Nikos Dimopoulos <nikos@niden.net>
     * @since       2013-07-03
     */
    public function addMetaCommonFields()
    {
        $this->addMetaField(
            $this->prefix . '_delete_flag',
            'deleted',
            true,
            'bool',
            'Whether this record is deleted or not'
        );
        $this->addMetaField(
            $this->prefix . '_created_id',
            'createdby',
            false,
            'int',
            'The user id that created this record'
        );
        $this->addMetaField(
            $this->prefix . '_created_date',
            'created',
            false,
            'datetime',
            'The date and time this record was created'
        );
        $this->addMetaField(
            $this->prefix . '_updated_id',
            'updatedby',
            false,
            'int',
            'The user id that last updated this record'
        );
        $this->addMetaField(
            $this->prefix . '_updated_date',
            'updated',
            false,
            'datetime',
            'The date and time this record was last updated'
        );
    }


    /**
     * Gets the list of aliases passed and returns the proper field list
     * that corresponds to the ones in the database.
     *
     * @author      Nikos Dimopoulos <nikos@niden.net>
     * @since       2013-07-01
     *
     * @param $fields
     *
     * @return string
     */
    protected function getFieldList($fields)
    {
        $return    = '';
        $fieldList = array();

        // Common fields
        if (empty($fields) || '*' == $fields) {
            $return = $this->getCommonFields();
        } elseif ('all' == $fields) {
            // All fields
            $return = $this->getAllFields();
        } else {
            // List
            $aliases = explode(',', $fields);

            if (count($aliases) > 0) {

                foreach ($aliases as $alias) {
                    $field = $this->getMetaFieldFromAlias($alias);
                    if ($field) {
                        $fieldList[] = $field['name'];
                    }
                }

                $return = implode(',', $fieldList);

            } else {
                $return = $this->getCommonFields();
            }
        }

        return $return;
    }

    /**
     * Returns all the common fields from this model
     *
     * @author      Nikos Dimopoulos <nikos@niden.net>
     * @since       2013-07-01
     *
     * @return string
     */
    protected function getCommonFields()
    {
        $fields = array();

        foreach ($this->data as $field) {
            if ($field['common']) {
                $fields[] = $field['name'];
            }
        }

        if (count($fields) > 0) {
            $return = implode(',', $fields);
        } else {
            $return = '*';
        }

        return $return;
    }

    /**
     * Returns all the fields from this model
     *
     * @author      Nikos Dimopoulos <nikos@niden.net>
     * @since       2013-07-01
     *
     * @return string
     */
    protected function getAllFields()
    {
        $fields = array();

        foreach ($this->data as $field) {
            $fields[] = $field['name'];
        }

        if (count($fields) > 0) {
            $return = implode(',', $fields);
        } else {
            $return = '*';
        }

        return $return;
    }

    /**
     * Gets the name of the field in the database using its alias
     *
     * @author      Nikos Dimopoulos <nikos@niden.net>
     * @since       2013-07-01
     *
     * @param $alias
     *
     * @return string
     */
    protected function getMetaFieldFromAlias($alias)
    {
        $return = '';

        if (array_key_exists($alias, $this->aliases)) {
            $return = $this->data[$this->aliases[$alias]];
        }

        return $return;
    }

    protected function getMetaFieldValidatedField($field, $value)
    {
        // Find the validator
        $validator = $this->data[$this->fields[$field]]['validator'];

        switch ($validator)
        {
            case 'int':
                $return = intval($value);
                break;
            case 'bool':
                $return = (bool) $value;
                break;
            case 'string':
                $return = (string) $value;
                break;
            case 'datetime':
                /**
                 * @todo check datetime validator
                 */
                $return = (string) $value;
                break;
            default:
                $return = $value;
                break;
        }

        return $return;
    }

}

Users.php

<?php
/**
 * \ND\Models\User
 * User.php
 *
 * User model
 *
 * @author      Nikos Dimopoulos <nikos@niden.net>
 * @since       2012-12-11
 * @category    Models
 *
 */

namespace ND\Models;

use \ND\Model as NDModel;

class User extends NDModel
{
    public function initialize()
    {
        // Set the prefix
        $this->prefix = 'usr';

        parent::initialize();

        /**
         * Metadata for fields
         */
        $meta = [
            [
                'usr_id', 'id', true, 'int', 'The User Id'
            ],
            [
                'usr_name', 'username', true, 'string',
                'The User name used for logging in'
            ],
        ];
        $this->addMetaFields($meta);
        $this->addMetaCommonFields();
    }

    public function getSource()
    {
        return 'user';
    }

    /**
     * GETTERS
     */
    public function getId()
    {
        $field = $this->prefix . '_id';

        return $this->$field;
    }

    public function getName()
    {
        $field = $this->prefix . '_name';

        return $this->$field;
    }


    /**
     * SETTERS
     */
    public function setId($id)
    {
        $field = $this->prefix . '_id';

        $this->$field = intval($id);
    }

    public function setName($name)
    {
        $field = $this->prefix . '_name';

        $this->$field = $name;
    }


    public function fetchByName($name)
    {
        $builder = $this->getBuilder();

        $bind['usr_name'] = $name;

        $columns = 'usr_id, usr_name';

        $builder->where("usr_name = :usr_name:");

        // Exclude deleted
        $this->excludeDeleted($builder);

        return $this->execute($builder, $bind, true);
    }

}