terah / fluent-pdo-model

src/FluentPdoModel.php

<?php declare(strict_types=1);
/**
 * @package Terah\FluentPdoModel
 *
 * Original work Copyright (c) 2014 Mardix (http://github.com/mardix)
 * Modified work Copyright (c) 2015 Terry Cullen (http://github.com/terah)
 *
 * Licensed under The MIT License
 * For full copyright and license information, please see the LICENSE.txt
 * Redistributions of files must retain the above copyright notice.
 *
 * @license       http://www.opensource.org/licenses/mit-license.php MIT License
 */
namespace Terah\FluentPdoModel;

use Closure;
use PDOException;
use Exception;
use PDO;
use PDOStatement;
use stdClass;
use DateTime;
use Terah\FluentPdoModel\Drivers\AbstractPdo;
use Psr\Log\LoggerInterface;
use Terah\RedisCache\CacheInterface;
use function Terah\Assert\Assert;
use function Terah\Assert\Validate;
/**
 * Class FluentPdoModel
 *
 * @package Terah\FluentPdoModel
 * @author  Terry Cullen - terry@terah.com.au
 */
class FluentPdoModel
{
    const ACTIVE                    = 1;
    const INACTIVE                  = 0;
    const ARCHIVED                  = -1;
    const GZIP_PREFIX               = 'gzipped|';
    const OPERATOR_AND              = ' AND ';
    const OPERATOR_OR               = ' OR ';
    const ORDERBY_ASC               = 'ASC';
    const ORDERBY_DESC              = 'DESC';
    const SAVE_INSERT               = 'INSERT';
    const SAVE_UPDATE               = 'UPDATE';
    const LEFT_JOIN                 = 'LEFT';
    const INNER_JOIN                = 'INNER';
    const ONE_DAY                   = 86400;
    const ONE_WEEK                  = 60480060;
    const ONE_HOUR                  = 3600;
    const TEN_MINS                  = 600;
    const CACHE_NO                  = -1;
    const CACHE_DEFAULT             = 0;

    const CREATOR_ID_FIELD          = 'creator_id';
    const CREATOR_FIELD             = 'creator';
    const CREATED_TS_FIELD          = 'created_ts';
    const MODIFIER_ID_FIELD         = 'modifier_id';
    const MODIFIER_FIELD            = 'modifier';
    const MODIFIED_TS_FIELD         = 'modified_ts';
    const DELETER_ID_FIELD          = 'deleter_id';
    const DELETER_FIELD             = 'deleter';
    const DELETED_TS_FIELD          = 'deleted_ts';
    const STATUS_FIELD              = 'active';
    const ACTIVE_FIELD              = 'active';

    /** @var AbstractPdo $connection */
    protected $connection           = null;

    /** @var string */
    protected $primaryKey           = 'id';

    /** @var array */
    protected $whereParameters      = [];

    /** @var array */
    protected $selectFields         = [];

    /** @var array */
    protected $joinSources          = [];

    /** @var array */
    protected $joinAliases          = [];

    /** @var array $associations */
    protected $associations         = [
        'belongsTo' => [],
    ];

    /** @var array */
    protected $whereConditions      = [];

    /** @var string  */
    protected $rawSql               = '';

    /** @var int */
    protected $limit                = 0;

    /** @var int */
    protected $offset               = 0;

    /** @var array */
    protected $orderBy              = [];

    /** @var array */
    protected $groupBy              = [];

    /** @var string */
    protected $andOrOperator        = self::OPERATOR_AND;

    /** @var array */
    protected $having               = [];

    /** @var bool */
    protected $wrapOpen             = false;

    /** @var int */
    protected $lastWrapPosition     = 0;

    /** @var PDOStatement $pdoStmt */
    protected $pdoStmt              = null;

    /** @var bool */
    protected $distinct             = false;

    /** @var null */
    protected $requestedFields      = [];

    /** @var null */
    protected $filterMeta           = [];

    /** @var bool */
    protected $logQueries           = false;

    /** @var array */
    protected $timer                = [];

    /** @var int */
    protected $slowQuerySecs        = 5;

    /** @var array */
    protected $paginationAttribs    = [
        '_limit',
        '_offset',
        '_order',
        '_fields',
        '_search'
    ];

    /** @var string $tableName */
    protected $tableName            = '';

    /** @var string $tableAlias */
    protected $tableAlias           = '';

    /** @var string $displayColumn */
    protected $displayColumn        = '';

    /** @var string $connectionName */
    protected $connectionName       = '';

    /** @var array $schema */
    protected $schema               = [];

    /** @var array $virtualFields */
    protected $virtualFields        = [];

    /** @var array $errors */
    protected $errors               = [];

    /**
     * @var int - true  = connection default x days
     *          - false = no cache
     *          - int   = a specific amount
     */
    protected $cacheTtl             = self::CACHE_NO;

    /** @var array */
    protected $flushCacheTables     = [];

    /** @var string */
    protected $tmpTablePrefix       = 'tmp_';

    /** @var null|string */
    protected $builtQuery           = '';

    /** @var array  */
    protected $handlers             = [];

    /** @var bool User want to directly specify the fields */
    protected $explicitSelectMode   = false;

    /** @var string[] */
    protected $updateRaw            = [];

    /** @var int  */
    protected $maxCallbackFails     = -1;

    /** @var int  */
    protected $numCallbackFails     = 0;

    /** @var bool  */
    protected $filterOnFetch        = false;

    /** @var bool  */
    protected $logFilterChanges     = true;

    /** @var bool  */
    protected $includeCount         = false;

    /** @var string */
    static protected $modelNamespace    = '';

    /** @var bool */
    protected $validationExceptions = true;

    /** @var array */
    protected $pagingMeta           = [];

    /** @var bool */
    protected $softDeletes          = true;

    /** @var bool  */
    protected $allowMetaOverride    = false;

    /** @var bool  */
    protected $skipMetaUpdates      = false;

    /** @var  bool */
    protected $addUpdateAlias       = false;

    /** @var int  */
    protected $defaultMax           = 250;

    /** @var array  */
    protected $removeUnauthorisedFields = [];

    /** @var bool  */
    protected $canGenericUpdate     = true;

    /** @var bool  */
    protected $canGenericCreate     = true;

    /** @var bool  */
    protected $canGenericDelete     = true;

    /** @var  array */
    protected $rowMetaData          = [];

    /** @var  array */
    protected $excludedSearchCols   = [];


    /** @var array  */
    protected $globalRemoveUnauthorisedFields = [
        '/global_table_meta#view'               => [
            self::CREATOR_ID_FIELD,
            self::CREATOR_FIELD,
            self::CREATED_TS_FIELD,
            self::MODIFIER_ID_FIELD,
            self::MODIFIER_FIELD,
            self::MODIFIED_TS_FIELD,
            self::STATUS_FIELD,
        ],
    ];


    /**
     * @param AbstractPdo|null $connection
     */
    public function __construct(AbstractPdo $connection=null)
    {
        $connection             = $connection ?: ConnectionPool::get($this->connectionName);
        $this->connection       = $connection;
        $this->logQueries       = $connection->logQueries();
        $this->init();
    }

    public function init()
    {}

    /**
     * @return AbstractPdo
     * @throws Exception
     */
    public function getPdo() : AbstractPdo
    {
        return $this->connection;
    }

    /**
     * @return LoggerInterface
     */
    public function getLogger() : LoggerInterface
    {
        return $this->connection->getLogger();
    }

    /**
     * @return CacheInterface
     */
    public function getCache() : CacheInterface
    {
        return $this->connection->getCache();
    }

    /**
     * Define the working table and create a new instance
     *
     * @param string $tableName - Table name
     * @param string $alias     - The table alias name
     * @param string $displayColumn
     * @param string $primaryKeyName
     *
     * @return FluentPdoModel|$this
     */
    public function table(string $tableName, string $alias='', string $displayColumn='', string $primaryKeyName='id') : FluentPdoModel
    {
        return $this->reset()
            ->tableName($tableName)
            ->tableAlias($alias)
            ->displayColumn($displayColumn)
            ->primaryKeyName($primaryKeyName);
    }

    /**
     * @param string $primaryKeyName
     * @return FluentPdoModel|$this
     */
    public function primaryKeyName(string $primaryKeyName) : FluentPdoModel
    {
        $this->primaryKey       = $primaryKeyName;

        return $this;
    }

    /**
     * @param string $tableName
     *
     * @return FluentPdoModel|$this
     */
    public function tableName(string $tableName) : FluentPdoModel
    {
        $this->tableName        = $tableName;

        return $this;
    }

    /**
     * @param $explicitSelect
     *
     * @return FluentPdoModel|$this
     */
    public function explicitSelectMode(bool $explicitSelect=true) : FluentPdoModel
    {
        $this->explicitSelectMode  = (bool)$explicitSelect;

        return $this;
    }

    /**
     * @param bool $filterOnFetch
     *
     * @return FluentPdoModel|$this
     */
    public function filterOnFetch(bool $filterOnFetch=true) : FluentPdoModel
    {
        $this->filterOnFetch    = (bool)$filterOnFetch;

        return $this;
    }

    /**
     * @param bool $logFilterChanges
     *
     * @return FluentPdoModel|$this
     */
    public function logFilterChanges(bool $logFilterChanges=true) : FluentPdoModel
    {
        $this->logFilterChanges = (bool)$logFilterChanges;

        return $this;
    }

    /**
     * Return the name of the table
     *
     * @return string
     */
    public function getTableName() : string
    {
        return $this->tableName;
    }

    /**
     * @return string
     */
    public function getDisplayColumn() : string
    {
        return $this->displayColumn;
    }

    /**
     * Set the display column
     *
     * @param string $column
     *
     * @return FluentPdoModel|$this
     */
    public function displayColumn(string $column) : FluentPdoModel
    {
        $this->displayColumn    = $column;

        return $this;
    }
    /**
     * Set the table alias
     *
     * @param string $alias
     *
     * @return FluentPdoModel|$this
     */
    public function tableAlias(string $alias) : FluentPdoModel
    {
        $this->tableAlias       = $alias;

        return $this;
    }

    /**
     * @param int $cacheTtl
     * @return FluentPdoModel|$this
     * @throws Exception
     */
    protected function cacheTtl(int $cacheTtl) : FluentPdoModel
    {
        Assert($cacheTtl)->int('Cache ttl must be either -1 for no cache, 0 for default ttl or an integer for a custom ttl');
        if ( $cacheTtl !== self::CACHE_NO && ! is_null($this->pdoStmt) )
        {
            throw new Exception("You cannot cache pre-executed queries");
        }
        $this->cacheTtl         = $cacheTtl;

        return $this;
    }

    /**
     * @return string
     */
    public function getTableAlias() : string
    {
        return $this->tableAlias;
    }

    /**
     * @param array $associations
     *
     * @return FluentPdoModel|$this
     */
    public function associations(array $associations) : FluentPdoModel
    {
        $this->associations     = $associations;

        return $this;
    }

    /**
     * @param string $alias
     * @param array $definition
     * @return FluentPdoModel|$this
     */
    public function setBelongsTo(string $alias, array $definition) : FluentPdoModel
    {
        Assert($alias)->notEmpty();
        Assert($definition)->isArray()->count(4);

        $this->associations['belongsTo'][$alias] = $definition;

        return $this;
    }

    /**
     * @param $alias
     * @param $displayField
     * @return FluentPdoModel|$this
     * @throws \Terah\Assert\AssertionFailedException
     */
    public function setBelongsToDisplayField(string $alias, string $displayField) : FluentPdoModel
    {
        Assert($alias)->notEmpty();
        Assert($this->associations['belongsTo'])->keyExists($alias);
        Assert($displayField)->notEmpty();

        $this->associations['belongsTo'][$alias][2] = $displayField;

        return $this;
    }

    /**
     * @param PDOStatement $stmt
     * @param Closure $fnCallback
     * @return bool|stdClass
     */
    public function fetchRow(PDOStatement $stmt, Closure $fnCallback=null)
    {
        if ( ! ( $record = $stmt->fetch(PDO::FETCH_OBJ) ) )
        {
            $this->rowMetaData      = [];

            return false;
        }
        $this->rowMetaData      = $this->rowMetaData  ?: $this->getColumnMeta($stmt, $record);
        $record                 = $this->onFetch($record);
        if ( empty($fnCallback) )
        {
            return $record;
        }
        $record                 = $fnCallback($record);
        if ( is_null($record) )
        {
            $this->getLogger()->warning("The callback is not returning any data which might be causing early termination of the result iteration");
        }
        unset($fnCallback);

        return $record;
    }

    /**
     * @param PDOStatement $stmt
     * @param $record
     * @return array
     */
    protected function getColumnMeta(PDOStatement $stmt, $record) : array
    {
        $meta                   = [];
        if ( ! $this->connection->supportsColumnMeta() )
        {
            return $meta;
        }
        foreach(range(0, $stmt->columnCount() - 1) as $index)
        {
            $data                   = $stmt->getColumnMeta($index);
            $meta[$data['name']]    = $data;
        }
        foreach ( $record as $field => $value )
        {
            Assert($meta)->keyExists($field);
        }

        return $meta;
    }

    /**
     * @param array $schema
     *
     * @return FluentPdoModel|$this
     */
    public function schema(array $schema) : FluentPdoModel
    {
        $this->schema           = $schema;

        return $this;
    }

    /**
     * @param string|array $field
     * @param $type
     * @return FluentPdoModel|$this
     */
    public function addSchema($field, string $type) : FluentPdoModel
    {
        if ( is_array($field) )
        {
            foreach ( $field as $fieldName => $typeDef )
            {
                $this->addSchema($fieldName, $typeDef);
            }

            return $this;
        }
        Assert($field)->string()->notEmpty();
        $this->schema[$field]   = $type;

        return $this;
    }

    /**
     * @param bool $getForeign
     * @return array
     */
    public function getSchema(bool $getForeign=false) : array
    {
        if ( $getForeign )
        {
            return $this->schema;
        }
        return array_filter($this->schema, function(string $type) {

            return ! in_array($type, ['collection', 'foreign']);
        });
    }

    /**
     * @param $keysOnly
     * @return array
     */
    public function getColumns(bool $keysOnly=true) : array
    {
        $schema                 = $this->getSchema();

        return $keysOnly ? array_keys($schema) : $schema;
    }

    /**
     * Get the primary key name
     *
     * @return string
     */
    public function getPrimaryKeyName() : string
    {
        return $this->formatKeyName($this->primaryKey, $this->tableName);
    }

    /**
     * @param string $query
     * @param array $parameters
     *
     * @return bool
     * @throws Exception
     */
    public function execute(string $query, array $parameters=[]) : bool
    {
        list($this->builtQuery, $ident)  = $this->logQuery($query, $parameters);
        try
        {
            $this->pdoStmt          = $this->getPdo()->prepare($query);
            $result                 = $this->pdoStmt->execute($parameters);
            if ( false === $result )
            {
                $this->pdoStmt          = null;

                throw new PDOException("The query failed to execute.");
            }
        }
        catch( Exception $e )
        {
            $builtQuery             = $this->builtQuery ? $this->builtQuery : $this->buildQuery($query, $parameters);
            $this->getLogger()->error("FAILED: \n\n{$builtQuery}\n WITH ERROR:\n" . $e->getMessage());
            $this->pdoStmt          = null;

            if ( preg_match('/FOREIGN KEY \(`([a-zA-Z0-9_]+)`\) REFERENCES \`([a-zA-Z0-9_]+)\` \(\`id\`\)/', $e->getMessage(), $matches) )
            {
                $field                  = $matches[1] ??0?: 'Unknown';
                $reference              = $matches[2] ??0?: 'Unknown';
                $errors                 = [
                    $field                  => ["Could not find {$reference} for {$field}"],
                ];

                throw new ModelFailedValidationException("Validation of data failed", $errors, 422);
            }
            throw $e;
        }
        $this->logSlowQueries($ident, $this->builtQuery);

        return $result;
    }

    /**
     * @param string $query
     * @param array $params
     * @return FluentPdoModel|$this
     */
    public function query(string $query, array $params=[]) : FluentPdoModel
    {
        $this->rawSql           = $query;
        $this->whereParameters  = $params;

        return $this;
    }

    /**
     * @param string $sql
     * @param array $params
     *
     * @return string
     */
    public function buildQuery(string $sql, array $params=[]) : string
    {
        $indexed                = $params == array_values($params);
        if ( $indexed )
        {
            foreach ( $params as $key => $val )
            {
                $val                    = is_string($val) ? "'{$val}'" : $val;
                $val                    = is_null($val) ? 'NULL' : $val;
                $sql                    = preg_replace('/\?/', $val, $sql, 1);
            }

            return $sql;
        }

        uksort($params, function ($a, $b) {
            return strlen($b) - strlen($a);
        });
        foreach ( $params as $key => $val )
        {
            $val                    = is_string($val) ? "'{$val}'" : $val;
            $val                    = is_null($val) ? 'NULL' : $val;
            $sql                    = str_replace(":$key", $val, $sql);
            //$sql    = str_replace("$key", $val, $sql);
        }

        return $sql;
    }

    /**
     * @param stdClass $record
     *
     * @return stdClass
     */
    protected function trimAndLowerCaseKeys(stdClass $record) : stdClass
    {
        $fnTrimStrings = function($value) {

            return is_string($value) ? trim($value) : $value;
        };
        $record                 = array_map($fnTrimStrings, array_change_key_case((array)$record, CASE_LOWER));
        unset($fnTrimStrings);

        return (object)$record;
    }

    /**
     * Return the number of affected row by the last statement
     *
     * @return int
     */
    public function rowCount() : int
    {
        $stmt = $this->fetchStmt();

        return $stmt ? $stmt->rowCount() : 0;
    }

    /**
     * @return PDOStatement
     * @throws PDOException
     */
    public function fetchStmt()
    {
        if ( null === $this->pdoStmt )
        {
            $this->execute($this->getSelectQuery(), $this->getWhereParameters());
        }

        return $this->pdoStmt;
    }

    /**
     * @return array
     */
    public function fetchSqlQuery() : array
    {
        $clone                  = clone $this;
        $query                  = $clone->getSelectQuery();
        $params                 = $clone->getWhereParameters();
        $result                 = [$query, $params];
        unset($clone->handlers, $clone, $query, $params);

        return $result;
    }

    /**
     * @param string $tableName
     * @param bool  $dropIfExists
     * @param array $indexes
     * @return boolean
     * @throws Exception
     */
    public function fetchIntoMemoryTable(string $tableName, bool $dropIfExists=true, array $indexes=[]) : bool
    {
        $tableName              = preg_replace('/[^A-Za-z0-9_]+/', '', $tableName);
        $tableName              = $this->tmpTablePrefix . preg_replace('/^' . $this->tmpTablePrefix . '/', '', $tableName);
        if ( $dropIfExists )
        {
            $this->execute("DROP TABLE IF EXISTS {$tableName}");
        }
        $indexSql               = [];
        foreach ( $indexes as $name => $column )
        {
            $indexSql[]             = "INDEX {$name} ({$column})";
        }
        $indexSql               = implode(", ", $indexSql);
        $indexSql               = empty($indexSql) ? '' : "({$indexSql})";
        list($sql, $params)     = $this->fetchSqlQuery();
        $sql                    = <<<SQL
        CREATE TEMPORARY TABLE {$tableName} {$indexSql} ENGINE=MEMORY {$sql}
SQL;

        return $this->execute($sql, $params);
    }

    /**
     * @param string $keyedOn
     * @param int $cacheTtl
     * @return stdClass[]
     */
    public function fetch(string $keyedOn='', int $cacheTtl=self::CACHE_NO) : array
    {
        $this->cacheTtl($cacheTtl);
        $fnCallback             = function() use ($keyedOn) {

            $stmt                   = $this->fetchStmt();
            $rows                   = [];
            while ( $record = $this->fetchRow($stmt) )
            {
                if ( $record === false ) continue; // For scrutinizer...
                if ( $keyedOn && property_exists($record, $keyedOn) )
                {
                    $rows[$record->{$keyedOn}] = $record;
                    continue;
                }
                $rows[]                 = $record;
            }
            $this->reset();

            return $rows;
        };
        if ( $this->cacheTtl === self::CACHE_NO )
        {
            return $fnCallback();
        }
        $table                  = $this->getTableName();
        $id                     = $this->parseWhereForPrimaryLookup();
        $id                     = $id ? "/{$id}" : '';
        list($sql, $params)     = $this->fetchSqlQuery();
        $sql                    = $this->buildQuery($sql, $params);
        $cacheKey               = "/{$table}{$id}/" . md5(json_encode([
            'sql'       => $sql,
            'keyed_on'  => $keyedOn,
        ]));
        $data = $this->cacheData($cacheKey, $fnCallback, $this->cacheTtl);

        return is_array($data) ? $data : [];
    }

    /**
     * @return string
     */
    protected function parseWhereForPrimaryLookup() : string
    {
        if ( ! ( $alias = $this->getTableAlias() ) )
        {
            return '';
        }
        foreach ( $this->whereConditions as $idx => $conds )
        {
            if ( ! empty($conds['STATEMENT']) && $conds['STATEMENT'] === "{$alias}.id = ?" )
            {
                return ! empty($conds['PARAMS'][0]) ? (string)$conds['PARAMS'][0] : '';
            }
        }

        return '';
    }

    /**
     * @param string $cacheKey
     * @param Closure $func
     * @param int $cacheTtl - 0 for default ttl, -1 for no cache or int for custom ttl
     * @return mixed
     */
    protected function cacheData(string $cacheKey, Closure $func, int $cacheTtl=self::CACHE_DEFAULT)
    {
        if ( $cacheTtl === self::CACHE_NO )
        {
            return $func->__invoke();
        }
        $data                   = $this->getCache()->get($cacheKey);
        if ( $data && is_object($data) && property_exists($data, 'results') )
        {
            $this->getLogger()->debug("Cache hit on {$cacheKey}");

            return $data->results;
        }
        $this->getLogger()->debug("Cache miss on {$cacheKey}");
        $data                   = (object)[
            // Watch out... invoke most likely calls reset
            // which clears the model params like _cache_ttl
            'results' => $func->__invoke(),
        ];
        try
        {
            // The cache engine expects null for the default cache value
            $cacheTtl               = $cacheTtl === self::CACHE_DEFAULT ? 0 : $cacheTtl;
            /** @noinspection PhpMethodParametersCountMismatchInspection */
            if ( ! $this->getCache()->set($cacheKey, $data, $cacheTtl) )
            {
                throw new \Exception("Could not save data to cache");
            }

            return $data->results;
        }
        catch (\Exception $e)
        {
            $this->getLogger()->error($e->getMessage(), $e->getTrace());

            return $data->results;
        }
    }

    /**
     * @param string $cacheKey
     * @return bool
     */
    public function clearCache(string $cacheKey) : bool
    {
        return $this->getCache()->delete($cacheKey);
    }

    /**
     * @param string $table
     * @return bool
     */
    public function clearCacheByTable(string $table='') : bool
    {
        $tables                 = $table ? [$table] : $this->getFlushCacheTables();
        foreach ( $tables as $table )
        {
            $this->clearCache("/{$table}/");
        }

        return true;
    }

    /**
     * @return string[]
     */
    public function getFlushCacheTables() : array
    {
        return ! empty($this->flushCacheTables) ? $this->flushCacheTables : [$this->getTableName()];
    }

    /**
     * @param Closure $fnCallback
     * @return int
     */
    public function fetchCallback(Closure $fnCallback) : int
    {
        $successCnt             = 0;
        $stmt                   = $this->fetchStmt();
        while ( $this->tallySuccessCount($stmt, $fnCallback, $successCnt) )
        {
            continue;
        }

        return $successCnt;
    }

    /**
     * @param Closure $fnCallback
     * @param string  $keyedOn
     * @return array
     */
    public function fetchObjectsByCallback(Closure $fnCallback, string $keyedOn='') : array
    {
        $stmt                   = $this->fetchStmt();
        $rows                   = [];
        while ( $rec = $this->fetchRow($stmt, $fnCallback) )
        {
            if ( $keyedOn && property_exists($rec, $keyedOn) )
            {
                $rows[$rec->{$keyedOn}] = $rec;

                continue;
            }
            $rows[]             = $rec;
        }
        $this->reset();

        return $rows;
    }

    /**
     * @param $numFailures
     * @return FluentPdoModel|$this
     */
    public function maxCallbackFailures(int $numFailures) : FluentPdoModel
    {
        Assert($numFailures)->int();
        $this->maxCallbackFails = $numFailures;

        return $this;
    }

    /**
     * @param PDOStatement $stmt
     * @param Closure $fnCallback
     * @param int $successCnt
     * @return bool|null|stdClass
     */
    protected function tallySuccessCount(PDOStatement $stmt, Closure $fnCallback, int &$successCnt)
    {
        $rec                    = $this->fetchRow($stmt);
        if ( $rec === false )
        {
            return false;
        }
        $rec                    = $fnCallback($rec);
        // Callback return null then we want to exit the fetch loop
        if ( is_null($rec) )
        {
            $this->getLogger()->warning("The callback is not returning any data which might be causing early termination of the result iteration");

            return null;
        }
        // The not record then don't bump the tally
        if ( ! $rec )
        {
            $this->numCallbackFails++;
            if ( $this->maxCallbackFails !== -1 && $this->numCallbackFails >= $this->maxCallbackFails )
            {
                $this->getLogger()->error("The callback has failed {$this->maxCallbackFails} times... aborting...");
                $successCnt             = null;

                return null;
            }

            return true;
        }
        $successCnt++;

        return $rec;
    }

    /**
     * @return bool
     */
    public function canGenericUpdate() : bool
    {
        return $this->canGenericUpdate;
    }

    /**
     * @return bool
     */
    public function canGenericCreate() : bool
    {
        return $this->canGenericCreate;
    }

    /**
     * @return bool
     */
    public function canGenericDelete() : bool
    {
        return $this->canGenericDelete;
    }

    /**
     * @param string $keyedOn
     * @param string $valueField
     * @param int $cacheTtl
     * @return mixed
     */
    public function fetchList(string $keyedOn='', string $valueField='', int $cacheTtl=self::CACHE_NO) : array
    {
        $keyedOn                = $keyedOn ?: $this->getPrimaryKeyName();
        $valueField             = $valueField ?: $this->getDisplayColumn();
        $keyedOnAlias           = strtolower(str_replace('.', '_', $keyedOn));
        $valueFieldAlias        = strtolower(str_replace('.', '_', $valueField));
        if ( preg_match('/ as /i', $keyedOn) )
        {
            $parts                  = preg_split('/ as /i', $keyedOn);
            $keyedOn                = trim($parts[0]);
            $keyedOnAlias           = trim($parts[1]);
        }
        if ( preg_match('/ as /i', $valueField) )
        {
            $parts                  = preg_split('/ as /i', $valueField);
            $valueField             = trim($parts[0]);
            $valueFieldAlias        = trim($parts[1]);
        }

        $this->cacheTtl($cacheTtl);
        $fnCallback             = function() use ($keyedOn, $keyedOnAlias, $valueField, $valueFieldAlias) {

            $rows                   = [];
            $stmt                   = $this->select(null)
                ->select($keyedOn, $keyedOnAlias)
                ->select($valueField, $valueFieldAlias)
                ->fetchStmt();
            while ( $rec = $this->fetchRow($stmt) )
            {
                $rows[$rec->{$keyedOnAlias}] = $rec->{$valueFieldAlias};
            }

            return $rows;
        };
        if ( $this->cacheTtl === self::CACHE_NO )
        {
            $result = $fnCallback();
            unset($cacheKey, $fnCallback);

            return $result;
        }
        $table                  = $this->getTableName();
        $cacheData              = [
            'sql'                   => $this->fetchSqlQuery(),
            'keyed_on'              => $keyedOn,
            'keyed_on_alias'        => $keyedOnAlias,
            'value_field'           => $valueField,
            'value_fieldAlias'      => $valueFieldAlias,
        ];
        $cacheKey               = json_encode($cacheData);
        if ( ! $cacheKey )
        {
            $this->getLogger()->warning('Could not generate cache key from data', $cacheData);
            $result             = $fnCallback();
            unset($cacheKey, $fnCallback);

            return $result;
        }
        $cacheKey               = md5($cacheKey);

        return $this->cacheData("/{$table}/list/{$cacheKey}", $fnCallback, $this->cacheTtl);
    }

    /**
     * @param string $column
     * @param int $cacheTtl
     * @param bool|true $unique
     * @return array
     */
    public function fetchColumn(string $column, int $cacheTtl=self::CACHE_NO, bool $unique=true) : array
    {
        $list = $this->select($column)->fetch('', $cacheTtl);
        foreach ( $list as $idx => $obj )
        {
            $list[$idx] = $obj->{$column};
        }

        return $unique ? array_unique($list) : $list;
    }

    /**
     * @param string $field
     * @param int $itemId
     * @param int $cacheTtl
     * @return mixed|null
     */
    public function fetchField(string $field='', int $itemId=0, int $cacheTtl=self::CACHE_NO)
    {
        $field                  = $field ?: $this->getPrimaryKeyName();
        $object                 = $this->select(null)->select($field)->fetchOne($itemId, $cacheTtl);
        if ( ! $object )
        {
            return null;
        }
        // Handle aliases
        if ( preg_match('/ as /i', $field) )
        {
            $alias                  = preg_split('/ as /i', $field)[1];
            $field                  = trim($alias);
        }
        if ( strpos($field, '.') !== false )
        {
            $field                  = explode('.', $field)[1];
        }

        return property_exists($object, $field) ? $object->{$field} : null;
    }

    /**
     * @param string $field
     * @param int $itemId
     * @param int $cacheTtl
     * @return string
     */
    public function fetchStr(string $field='', $itemId=0, int $cacheTtl=self::CACHE_NO) : string
    {
        return (string)$this->fetchField($field, $itemId, $cacheTtl);
    }

    /**
     * @param int $cacheTtl
     * @return int
     */
    public function fetchId(int $cacheTtl=self::CACHE_NO) : int
    {
        return $this->fetchInt($this->getPrimaryKeyName(), 0, $cacheTtl);
    }

    /**
     * @param string $field
     * @param int $itemId
     * @param int $cacheTtl
     * @return int
     */
    public function fetchInt(string $field='', int $itemId=0, int $cacheTtl=self::CACHE_NO) : int
    {
        return (int)$this->fetchField($field, $itemId, $cacheTtl);
    }

    /**
     * @param string $field
     * @param int $itemId
     * @param int $cacheTtl
     * @return float
     */
    public function fetchFloat(string $field='', int $itemId=0, int $cacheTtl=self::CACHE_NO) : float
    {
        return (float)$this->fetchField($field, $itemId, $cacheTtl);
    }

    /**
     * @param string $field
     * @param int $itemId
     * @param int $cacheTtl
     * @return bool
     */
    public function fetchBool(string $field='', int $itemId=0, int $cacheTtl=self::CACHE_NO) : bool
    {
        return (bool)$this->fetchField($field, $itemId, $cacheTtl);
    }

    /**
     * @param int|null $id
     * @param int $cacheTtl
     * @return stdClass|null
     */
    public function fetchOne(int $id=0, int $cacheTtl=self::CACHE_NO)
    {
        if ( $id > 0 )
        {
            $this->wherePk($id, true);
        }
        $fetchAll               = $this
            ->limit(1)
            ->fetch('', $cacheTtl);

        return $fetchAll ? array_shift($fetchAll) : null;
    }

    /**
     * @param int|null $id
     * @param int $cacheTtl
     * @return boolean
     */
    public function fetchExists(int $id=0, int $cacheTtl=self::CACHE_NO) : bool
    {
        if ( $id > 0 )
        {
            $this->wherePk($id, true);
        }
        $cnt = $this->count('*', $cacheTtl);

        return $cnt > 0;
    }

    /*------------------------------------------------------------------------------
                                    Fluent Query Builder
    *-----------------------------------------------------------------------------*/

    /**
     * Create the select clause
     *
     * @param  mixed    $columns  - the column to select. Can be string or array of fields
     * @param  string   $alias - an alias to the column
     * @param boolean $explicitSelect
     * @return FluentPdoModel|$this
     */
    public function select($columns='*', string $alias='', bool $explicitSelect=true) : FluentPdoModel
    {
        if ( $explicitSelect )
        {
            $this->explicitSelectMode();
        }
        if ( $alias && ! is_array($columns) & $columns !== $alias )
        {
            $columns                .= " AS {$alias} ";
        }
        $schema                 = $this->getSchema();
        if ( $columns === '*' && ! empty($schema) )
        {
            $columns                = array_keys($schema);
        }
        // Reset the select list
        if ( is_null($columns) || $columns === '' )
        {
            $this->selectFields     = [];

            return $this;
        }
        $columns                = is_array($columns) ? $columns : [$columns];

//        if ( empty($this->selectFields) && $addAllIfEmpty )
//        {
//            $this->select('*');
//        }
        if ( $this->tableAlias )
        {
            $schema                 = $this->getColumns();
            foreach ( $columns as $idx => $col )
            {
                if ( in_array($col, $schema) )
                {
                    $columns[$idx]          = "{$this->tableAlias}.{$col}";
                }
            }
        }
        $this->selectFields     = array_merge($this->selectFields, $columns);

        return $this;
    }

    /**
     * @param string $select
     * @return FluentPdoModel|$this
     */
    public function selectRaw(string $select) : FluentPdoModel
    {
        $this->selectFields[]   = $select;

        return $this;
    }

    /**
     * @param bool $logQueries
     *
     * @return FluentPdoModel|$this
     */
    public function logQueries(bool $logQueries=true) : FluentPdoModel
    {
        $this->logQueries       = $logQueries;

        return $this;
    }

    /**
     * @param bool $includeCnt
     *
     * @return FluentPdoModel|$this
     */
    public function includeCount(bool $includeCnt=true) : FluentPdoModel
    {
        $this->includeCount     = $includeCnt;

        return $this;
    }

    /**
     * @param bool $distinct
     *
     * @return FluentPdoModel|$this
     */
    public function distinct(bool $distinct=true) : FluentPdoModel
    {
        $this->distinct         = $distinct;

        return $this;
    }

    /**
     * @param array $fields
     * @return FluentPdoModel|$this
     */
    public function withBelongsTo(array $fields=[]) : FluentPdoModel
    {
        if ( empty($this->associations['belongsTo']) )
        {
            return $this;
        }
        foreach ( $this->associations['belongsTo'] as $alias => $config )
        {
            $addFieldsForJoins      = empty($fields) || in_array($config[3], $fields);
            $this->autoJoin($alias, self::LEFT_JOIN, $addFieldsForJoins);
        }

        return $this;
    }

    /**
     * @param string $alias
     * @param bool   $addSelectField
     * @return FluentPdoModel
     */
    public function autoInnerJoin(string $alias, bool $addSelectField=true) : FluentPdoModel
    {
        return $this->autoJoin($alias, self::INNER_JOIN, $addSelectField);
    }

    /**
     * @param string $alias
     * @param string $type
     * @param bool   $addSelectField
     * @return FluentPdoModel|$this
     */
    public function autoJoin(string $alias, string $type=self::LEFT_JOIN, bool $addSelectField=true) : FluentPdoModel
    {
        Assert($this->associations['belongsTo'])->keyExists($alias, "Invalid join... the alias does not exists");
        list($table, $joinCol, $field, $fieldAlias)     = $this->associations['belongsTo'][$alias];
        $tableJoinField                                 = "{$this->tableAlias}.{$joinCol}";
        // Extra join onto another second level table
        if ( strpos($joinCol, '.') !== false )
        {
            $tableJoinField         =  $joinCol;
            if ( $addSelectField )
            {
                $this->select($joinCol, '', false);
            }
        }
        $condition              = "{$alias}.id = {$tableJoinField}";
        if ( in_array($alias, $this->joinAliases) )
        {
            return $this;
        }
        $this->join($table, $condition, $alias, $type);
        if ( $addSelectField )
        {
            $this->select($field, $fieldAlias, false);
        }

        return $this;
    }

    /**
     * @param array $conditions
     * @return FluentPdoModel
     */
    public function whereArr(array $conditions) : FluentPdoModel
    {
        foreach ($conditions as $key => $val)
        {
            $this->where($key, $val);
        }

        return $this;
    }
    /**
     * Add where condition, more calls appends with AND
     *
     * @param string $condition possibly containing ? or :name
     * @param mixed $parameters accepted by PDOStatement::execute or a scalar value
     * @param mixed ...
     * @return FluentPdoModel|$this
     */
    public function where($condition, $parameters=[]) : FluentPdoModel
    {
        // By default the andOrOperator and wrap operator is AND,
        if ( $this->wrapOpen || ! $this->andOrOperator )
        {
            $this->_and();
        }

        // where(array("column1" => 1, "column2 > ?" => 2))
        if ( is_array($condition) )
        {
            foreach ($condition as $key => $val)
            {
                $this->where($key, $val);
            }

            return $this;
        }

        $args                   = func_num_args();
        if ( $args != 2 || strpbrk((string)$condition, '?:') )
        { // where('column < ? OR column > ?', array(1, 2))
            if ( $args != 2 || !is_array($parameters) )
            { // where('column < ? OR column > ?', 1, 2)
                $parameters             = func_get_args();
                array_shift($parameters);
            }
        }
        else if ( ! is_array($parameters) )
        {//where(column,value) => column=value
            $condition              .= ' = ?';
            $parameters = [$parameters];
        }
        else if ( is_array($parameters) )
        { // where('column', array(1, 2)) => column IN (?,?)
            $placeholders           = $this->makePlaceholders(count($parameters));
            $condition              = "({$condition} IN ({$placeholders}))";
        }

        $this->whereConditions[] = [
            'STATEMENT'             => $condition,
            'PARAMS'                => $parameters,
            'OPERATOR'              => $this->andOrOperator
        ];
        // Reset the where operator to AND. To use OR, you must call _or()
        $this->_and();

        return $this;
    }

    /**
     * Create an AND operator in the where clause
     *
     * @return FluentPdoModel|$this
     */
    public function _and() : FluentPdoModel
    {
        if ( $this->wrapOpen )
        {
            $this->whereConditions[]    = self::OPERATOR_AND;
            $this->lastWrapPosition     = count($this->whereConditions);
            $this->wrapOpen             = false;

            return $this;
        }
        $this->andOrOperator = self::OPERATOR_AND;

        return $this;
    }


    /**
     * Create an OR operator in the where clause
     *
     * @return FluentPdoModel|$this
     */
    public function _or() : FluentPdoModel
    {
        if ( $this->wrapOpen )
        {
            $this->whereConditions[]    = self::OPERATOR_OR;
            $this->lastWrapPosition     = count($this->whereConditions);
            $this->wrapOpen             = false;

            return $this;
        }
        $this->andOrOperator    = self::OPERATOR_OR;

        return $this;
    }

    /**
     * To group multiple where clauses together.
     *
     * @return FluentPdoModel|$this
     */
    public function wrap() : FluentPdoModel
    {
        $this->wrapOpen         = true;
        $spliced                = array_splice($this->whereConditions, $this->lastWrapPosition, count($this->whereConditions), '(');
        $this->whereConditions  = array_merge($this->whereConditions, $spliced);
        array_push($this->whereConditions,')');
        $this->lastWrapPosition = count($this->whereConditions);

        return $this;
    }

    /**
     * Where Primary key
     *
     * @param int  $id
     * @param bool $addAlias
     *
     * @return FluentPdoModel|$this
     */
    public function wherePk(int $id, bool $addAlias=true) : FluentPdoModel
    {
        $alias                  = $addAlias && ! empty($this->tableAlias) ? "{$this->tableAlias}." : '';

        return $this->where($alias . $this->getPrimaryKeyName(), $id);
    }

    /**
     * @param string $name
     * @param bool   $addAlias
     * @return FluentPdoModel
     */
    public function whereDisplayName(string $name, bool $addAlias=true) : FluentPdoModel
    {
        $alias                  = $addAlias && ! empty($this->tableAlias) ? "{$this->tableAlias}." : '';

        return $this->where($alias . $this->getDisplayColumn(), $name);
    }

    /**
     * WHERE $columnName != $value
     *
     * @param  string   $columnName
     * @param  mixed    $value
     * @return FluentPdoModel|$this
     */
    public function whereNot(string $columnName, $value) : FluentPdoModel
    {
        return $this->where("$columnName != ?", $value);
    }
    /**
     * WHERE $columnName != $value
     *
     * @param  string   $columnName
     * @param  mixed    $value
     * @return FluentPdoModel|$this
     */
    public function whereCoercedNot(string $columnName, $value) : FluentPdoModel
    {
        return $this->where("IFNULL({$columnName}, '') != ?", $value);
    }

    /**
     * WHERE $columnName LIKE $value
     *
     * @param  string   $columnName
     * @param  mixed    $value
     * @return FluentPdoModel|$this
     */
    public function whereLike(string $columnName, $value) : FluentPdoModel
    {
        return $this->where("$columnName LIKE ?", $value);
    }

    /**
     * @param string $columnName
     * @param mixed $value1
     * @param mixed $value2
     * @return FluentPdoModel|$this
     */
    public function whereBetween(string $columnName, $value1, $value2) : FluentPdoModel
    {
        $value1                 = is_string($value1) ? trim($value1) : $value1;
        $value2                 = is_string($value2) ? trim($value2) : $value2;

        return $this->where("$columnName BETWEEN ? AND ?", [$value1, $value2]);
    }

    /**
     * @param string $columnName
     * @param mixed $value1
     * @param mixed $value2
     * @return FluentPdoModel|$this
     */
    public function whereNotBetween(string $columnName, $value1, $value2) : FluentPdoModel
    {
        $value1                 = is_string($value1) ? trim($value1) : $value1;
        $value2                 = is_string($value2) ? trim($value2) : $value2;

        return $this->where("$columnName NOT BETWEEN ? AND ?", [$value1, $value2]);
    }

    /**
     * @param string $columnName
     * @param string $regex
     * @return FluentPdoModel|$this
     */
    public function whereRegex(string $columnName, string $regex) : FluentPdoModel
    {
        return $this->where("$columnName REGEXP ?", $regex);
    }

    /**
     * @param string $columnName
     * @param string $regex
     * @return FluentPdoModel|$this
     */
    public function whereNotRegex(string $columnName, string $regex) : FluentPdoModel
    {
        return $this->where("$columnName NOT REGEXP ?", $regex);
    }

    /**
     * WHERE $columnName NOT LIKE $value
     *
     * @param  string   $columnName
     * @param  string   $value
     * @return FluentPdoModel|$this
     */
    public function whereNotLike(string $columnName, string $value) : FluentPdoModel
    {
        return $this->where("$columnName NOT LIKE ?", $value);
    }

    /**
     * WHERE $columnName > $value
     *
     * @param  string   $columnName
     * @param  mixed    $value
     * @return FluentPdoModel|$this
     */
    public function whereGt(string $columnName, $value) : FluentPdoModel
    {
        return $this->where("$columnName > ?", $value);
    }

    /**
     * WHERE $columnName >= $value
     *
     * @param  string   $columnName
     * @param  mixed    $value
     * @return FluentPdoModel|$this
     */
    public function whereGte(string $columnName, $value) : FluentPdoModel
    {
        return $this->where("$columnName >= ?", $value);
    }

    /**
     * WHERE $columnName < $value
     *
     * @param  string   $columnName
     * @param  mixed    $value
     * @return FluentPdoModel|$this
     */
    public function whereLt(string $columnName, $value) : FluentPdoModel
    {
        return $this->where("$columnName < ?", $value);
    }

    /**
     * WHERE $columnName <= $value
     *
     * @param  string   $columnName
     * @param  mixed    $value
     * @return FluentPdoModel|$this
     */
    public function whereLte(string $columnName, $value) : FluentPdoModel
    {
        return $this->where("$columnName <= ?", $value);
    }

    /**
     * WHERE $columnName IN (?,?,?,...)
     *
     * @param  string   $columnName
     * @param  array    $values
     * @return FluentPdoModel|$this
     */
    public function whereIn(string $columnName, array $values) : FluentPdoModel
    {
        return $this->where($columnName, array_values($values));
    }

    /**
     * WHERE $columnName NOT IN (?,?,?,...)
     *
     * @param  string   $columnName
     * @param  array    $values
     * @return FluentPdoModel|$this
     */
    public function whereNotIn(string $columnName, array $values) : FluentPdoModel
    {
        $placeholders           = $this->makePlaceholders(count($values));

        return $this->where("({$columnName} NOT IN ({$placeholders}))", $values);
    }

    /**
     * WHERE $columnName IS NULL
     *
     * @param  string   $columnName
     * @return FluentPdoModel|$this
     */
    public function whereNull(string $columnName) : FluentPdoModel
    {
        return $this->where("({$columnName} IS NULL)");
    }

    /**
     * WHERE $columnName IS NOT NULL
     *
     * @param  string   $columnName
     * @return FluentPdoModel|$this
     */
    public function whereNotNull(string $columnName) : FluentPdoModel
    {
        return $this->where("({$columnName} IS NOT NULL)");
    }

    /**
     * @param string $statement
     * @param string $operator
     * @return FluentPdoModel|$this
     */
    public function having(string $statement, string $operator=self::OPERATOR_AND) : FluentPdoModel
    {
        $this->having[]         = [
            'STATEMENT'             => $statement,
            'OPERATOR'              => $operator
        ];

        return $this;
    }

    /**
     * ORDER BY $columnName (ASC | DESC)
     *
     * @param  string   $columnName - The name of the column or an expression
     * @param  string   $ordering   (DESC | ASC)
     * @return FluentPdoModel|$this
     */
    public function orderBy(string $columnName='', string $ordering='ASC') : FluentPdoModel
    {
        $ordering               = strtoupper($ordering);
        Assert($ordering)->inArray(['DESC', 'ASC']);
        if ( ! $columnName )
        {
            $this->orderBy          = [];

            return $this;
        }
        $this->orderBy[]        = trim("{$columnName} {$ordering}");

        return $this;
    }

    /**
     * GROUP BY $columnName
     *
     * @param  string   $columnName
     * @return FluentPdoModel|$this
     */
    public function groupBy(string $columnName) : FluentPdoModel
    {
        $columnName             = is_array($columnName) ? $columnName : [$columnName];
        foreach ( $columnName as $col )
        {
            $this->groupBy[]        = $col;
        }

        return $this;
    }


    /**
     * LIMIT $limit
     *
     * @param  int      $limit
     * @param  int|null $offset
     * @return FluentPdoModel|$this
     */
    public function limit(int $limit, int $offset=0) : FluentPdoModel
    {
        $this->limit            =  $limit;
        if ( $offset )
        {
            $this->offset($offset);
        }
        return $this;
    }

    /**
     * Return the limit
     *
     * @return integer
     */
    public function getLimit() : int
    {
        return $this->limit;
    }

    /**
     * OFFSET $offset
     *
     * @param  int      $offset
     * @return FluentPdoModel|$this
     */
    public function offset(int $offset) : FluentPdoModel
    {
        $this->offset           = (int)$offset;

        return $this;
    }

    /**
     * Return the offset
     *
     * @return integer
     */
    public function getOffset() : int
    {
        return $this->offset;
    }

    /**
     * Build a join
     *
     * @param  string    $table         - The table name
     * @param  string   $constraint    -> id = profile.user_id
     * @param  string   $tableAlias   - The alias of the table name
     * @param  string   $joinOperator - LEFT | INNER | etc...
     * @return FluentPdoModel|$this
     */
    public function join(string $table, string $constraint='', string $tableAlias='', string $joinOperator='') : FluentPdoModel
    {
        if ( ! $constraint )
        {
            return $this->autoJoin($table, $joinOperator);
        }
        $join                   = [$joinOperator ? "{$joinOperator} " : ''];
        $join[]                 = "JOIN {$table} ";
        $tableAlias             = $tableAlias ?: Inflector::classify($table);
        $join[]                 = $tableAlias ? "AS {$tableAlias} " : '';
        $join[]                 = "ON {$constraint}";
        $this->joinSources[]    = implode('', $join);
        if ( $tableAlias )
        {
            $this->joinAliases[]    = $tableAlias;
        }

        return $this;
    }

    /**
     * Create a left join
     *
     * @param  string   $table
     * @param  string   $constraint
     * @param  string   $tableAlias
     * @return FluentPdoModel|$this
     */
    public function leftJoin(string $table, string $constraint, string $tableAlias='') : FluentPdoModel
    {
        return $this->join($table, $constraint, $tableAlias, self::LEFT_JOIN);
    }


    /**
     * Return the build select query
     *
     * @return string
     */
    public function getSelectQuery() : string
    {
        if ( $this->rawSql )
        {
            return $this->rawSql;
        }
        if ( empty($this->selectFields) || ! $this->explicitSelectMode )
        {
            $this->select('*', '', false);
        }
        foreach ( $this->selectFields as $idx => $cols )
        {
            if ( strpos(trim(strtolower($cols)), 'distinct ') === 0 )
            {
                $this->distinct         = true;
                $this->selectFields[$idx] = str_ireplace('distinct ', '', $cols);
            }
        }
        if ( $this->includeCount )
        {
            $this->select('COUNT(*) as __cnt');
        }
        $query                  = 'SELECT ';
        $query                  .= $this->distinct ? 'DISTINCT ' : '';
        $query                  .= implode(', ', $this->prepareColumns($this->selectFields));
        $query                  .= " FROM {$this->tableName}" . ( $this->tableAlias ? " {$this->tableAlias}" : '' );
        if ( count($this->joinSources ) )
        {
            $query                  .= (' ').implode(' ',$this->joinSources);
        }
        $query                  .= $this->getWhereString(); // WHERE
        if ( count($this->groupBy) )
        {
            $query                  .= ' GROUP BY ' . implode(', ', array_unique($this->groupBy));
        }
        if ( count($this->orderBy ) )
        {
            $query                  .= ' ORDER BY ' . implode(', ', array_unique($this->orderBy));
        }
        $query                  .= $this->getHavingString(); // HAVING

        return $this->connection->setLimit($query, $this->limit, $this->offset);
    }

    /**
     * @param string $field
     * @param string $column
     * @return string
     */
    public function getFieldComment(string $field, string $column) : string
    {
        return $this->connection->getFieldComment($field, $column);
    }

    /**
     * Prepare columns to include the table alias name
     * @param array $columns
     * @return array
     */
    protected function prepareColumns(array $columns) : array
    {
        if ( ! $this->tableAlias )
        {
            return $columns;
        }
        $newColumns             = [];
        foreach ($columns as $column)
        {
            if ( strpos($column, ',') && ! preg_match('/^[a-zA-Z_]{2,200}\(.{1,500}\)/', trim($column)) )
            {
                $newColumns             = array_merge($this->prepareColumns(explode(',', $column)), $newColumns);
            }
            elseif ( preg_match('/^(AVG|SUM|MAX|MIN|COUNT|CONCAT)/', $column) )
            {
                $newColumns[] = trim($column);
            }
            elseif (strpos($column, '.') === false && strpos(strtoupper($column), 'NULL') === false)
            {
                $column                 = trim($column);
                $newColumns[]           = preg_match('/^[0-9]/', $column) ? trim($column) : "{$this->tableAlias}.{$column}";
            }
            else
            {
                $newColumns[]       = trim($column);
            }
        }

        return $newColumns;
    }

    /**
     * Build the WHERE clause(s)
     *
     * @param bool $purgeAliases
     * @return string
     */
    protected function getWhereString(bool $purgeAliases=false) : string
    {
        // If there are no WHERE clauses, return empty string
        if ( empty($this->whereConditions) )
        {
            return '';
        }
        $whereCondition         = '';
        $lastCondition          = '';
        foreach ( $this->whereConditions as $condition )
        {
            if ( is_array($condition) )
            {
                if ( $whereCondition && $lastCondition != '(' && !preg_match('/\)\s+(OR|AND)\s+$/i', $whereCondition))
                {
                    $whereCondition         .= $condition['OPERATOR'];
                }
                if ( $purgeAliases && ! empty($condition['STATEMENT']) && strpos($condition['STATEMENT'], '.') !== false && ! empty($this->tableAlias) )
                {
                    $condition['STATEMENT'] = preg_replace("/{$this->tableAlias}\./", '', $condition['STATEMENT']);
                }
                $whereCondition         .= $condition['STATEMENT'];
                $this->whereParameters  = array_merge($this->whereParameters, $condition['PARAMS']);
            }
            else
            {
                $whereCondition         .= $condition;
            }
            $lastCondition          = $condition;
        }

        return " WHERE {$whereCondition}" ;
    }

    /**
     * Return the HAVING clause
     *
     * @return string
     */
    protected function getHavingString() : string
    {
        // If there are no WHERE clauses, return empty string
        if ( empty($this->having) )
        {
            return '';
        }
        $havingCondition        = '';
        foreach ( $this->having as $condition )
        {
            if ( $havingCondition && ! preg_match('/\)\s+(OR|AND)\s+$/i', $havingCondition) )
            {
                $havingCondition        .= $condition['OPERATOR'];
            }
            $havingCondition        .= $condition['STATEMENT'];
        }

        return " HAVING {$havingCondition}" ;
    }

    /**
     * Return the values to be bound for where
     *
     * @param bool $purgeAliases
     * @return array
     */
    protected function getWhereParameters(bool $purgeAliases=false) : array
    {
        unset($purgeAliases);

        return $this->whereParameters;
    }

    /**
     * @param array $record
     * @return stdClass
     */
    public function insertArr(array $record) : stdClass
    {
        return $this->insert((object)$record);
    }

    /**
     * Insert new rows
     * $records can be a stdClass or an array of stdClass to add a bulk insert
     * If a single row is inserted, it will return it's row instance
     *
     * @param stdClass $rec
     * @return stdClass
     * @throws Exception
     */
    public function insert(stdClass $rec) : stdClass
    {
        Assert((array)$rec)->notEmpty("The data passed to insert does not contain any data");
        Assert($rec)->isInstanceOf('stdClass', "The data to be inserted must be an object or an array of objects");

        $rec                    = $this->beforeSave($rec, self::SAVE_INSERT);
        if ( ! empty($this->errors) )
        {
            return $rec;
        }
        list($sql, $values)     = $this->insertSqlQuery([$rec]);
        $this->execute((string)$sql, (array)$values);
        $rowCount               = $this->rowCount();
        if ( $rowCount === 1 )
        {
            $primaryKeyName         = $this->getPrimaryKeyName();
            $rec->{$primaryKeyName} = $this->getLastInsertId($primaryKeyName);
        }
        $rec                    = $this->afterSave($rec, self::SAVE_INSERT);
        $this->destroy();

        return $rec;
    }

    /**
     * @param string $name
     * @return int
     */
    public function getLastInsertId(string $name='') : int
    {
        return (int)$this->getPdo()->lastInsertId($name ?: null);
    }

    /**
     * @param stdClass[] $records
     * @return stdClass[]
     */
    public function insertSqlQuery(array $records) : array
    {
        Assert($records)->notEmpty("The data passed to insert does not contain any data");
        Assert($records)->all()->isInstanceOf('stdClass', "The data to be inserted must be an object or an array of objects");

        $insertValues           = [];
        $questionMarks          = [];
        $properties             = [];
        foreach ( $records as $record )
        {
            $properties             = !empty($properties) ? $properties : array_keys(get_object_vars($record));
            $questionMarks[]        = '('  . $this->makePlaceholders(count($properties)) . ')';
            $insertValues           = array_merge($insertValues, array_values((array)$record));
        }
        $properties             = implode(', ', $properties);
        $questionMarks          = implode(', ', $questionMarks);
        $sql                    = "INSERT INTO {$this->tableName} ({$properties}) VALUES {$questionMarks}";

        return [$sql, $insertValues];
    }

    /**
     * @param       $data
     * @param array $matchOn
     * @param bool  $returnObj
     * @return bool|int|stdClass
     */
    public function upsert($data, array $matchOn=[], $returnObj=false)
    {
        if ( ! is_array($data) )
        {
            return $this->upsertOne($data, $matchOn, $returnObj);
        }
        Assert($data)
            ->notEmpty("The data passed to insert does not contain any data")
            ->all()->isInstanceOf('stdClass', "The data to be inserted must be an object or an array of objects");
        $numSuccess             = 0;
        foreach ( $data as $row )
        {
            $clone                  = clone $this;
            if ( $clone->upsertOne($row, $matchOn) )
            {
                $numSuccess++;
            }
            unset($clone->handlers, $clone); // hhvm mem leak
        }

        return $numSuccess;
    }

    /**
     * @param stdClass $object
     * @param array    $matchOn
     * @param bool     $returnObj
     * @return bool|int|stdClass
     */
    public function upsertOne(stdClass $object, array $matchOn=[], $returnObj=false)
    {
        $primaryKey             = $this->getPrimaryKeyName();
        $matchOn                = empty($matchOn) && property_exists($object, $primaryKey) ? [$primaryKey] : $matchOn;
        foreach ( $matchOn as $column )
        {
            Assert( ! property_exists($object, $column) && $column !== $primaryKey)->false('The match on value for upserts is missing.');
            if ( property_exists($object, $column) )
            {
                if ( is_null($object->{$column}) )
                {
                    $this->whereNull($column);
                }
                else
                {
                    $this->where($column, $object->{$column});
                }
            }
        }
        if ( count($this->whereConditions) < 1 )
        {
            return $this->insert($object);
        }
        if ( ( $id = (int)$this->fetchField($primaryKey) ) )
        {
            if ( property_exists($object, $primaryKey) && empty($object->{$primaryKey}) )
            {
                $object->{$primaryKey}  = $id;
            }
            $rowsAffected           = $this->reset()->wherePk($id)->update($object);
            if ( $rowsAffected === false )
            {
                return false;
            }

            return $returnObj ? $this->reset()->fetchOne($id) : $id;
        }

        return $this->insert($object);
    }

    /**
     * @param array      $data
     * @param array      $matchOn
     * @param bool|false $returnObj
     * @return bool|int|stdClass
     */
    public function upsertArr(array $data, array $matchOn=[], bool $returnObj=false)
    {
        return $this->upsert((object)$data, $matchOn, $returnObj);
    }

    /**
     * Update entries
     * Use the query builder to create the where clause
     *
     * @param stdClass $record
     * @param bool     $updateAll
     * @return int
     * @throws Exception
     */
    public function update(stdClass $record, $updateAll=false) : int
    {
        Assert($record)
            ->notEmpty("The data passed to update does not contain any data")
            ->isInstanceOf('stdClass', "The data to be updated must be an object or an array of objects");

        if ( empty($this->whereConditions) && ! $updateAll )
        {
            throw new Exception("You cannot update an entire table without calling update with updateAll=true", 500);
        }
        $record                 = $this->beforeSave($record, self::SAVE_UPDATE);
        if ( ! empty($this->errors) )
        {
            return 0;
        }
        list($sql, $values)     = $this->updateSqlQuery($record);
        $this->execute($sql, $values);
        $this->afterSave($record, self::SAVE_UPDATE);
        $rowCount               = $this->rowCount();
        $this->destroy();

        return $rowCount;
    }

    /**
     * @param array      $record
     * @param bool|false $updateAll
     * @return int
     * @throws Exception
     */
    public function updateArr(array $record, $updateAll=false) : int
    {
        return $this->update((object)$record, $updateAll);
    }

    /**
     * @param string  $field
     * @param mixed   $value
     * @param int     $id
     * @param bool|false $updateAll
     * @return int
     * @throws Exception
     */
    public function updateField(string $field, $value, int $id=0, bool $updateAll=false) : int
    {
        if ( $id && $id > 0 )
        {
            $this->wherePk($id);
        }
        $columns    = $this->getColumns();
        if ( $columns )
        {
            Assert($field)->inArray($columns, "The field {$field} does not exist on this table {$this->tableName}");
        }

        return $this->update((object)[$field => $value], $updateAll);
    }

    /**
     * @param stdClass $record
     * @return bool|int
     * @throws Exception
     */
    public function updateChanged(stdClass $record) : int
    {
        foreach ( $record as $field => $value )
        {
            if ( is_null($value) )
            {
                $this->whereNotNull($field);
                continue;
            }
            $this->whereCoercedNot($field, $value);
        }

        return $this->update($record);
    }

    /**
     * @param string    $expression
     * @param array     $params
     * @return FluentPdoModel|$this
     */
    public function updateByExpression(string $expression, array $params) : FluentPdoModel
    {
        $this->updateRaw[]      = [$expression, $params];

        return $this;
    }

    /**
     * @param array $data
     * @return int
     * @throws Exception
     */
    public function rawUpdate(array $data=[]) : int
    {
        list($sql, $values)     = $this->updateSql($data);
        $this->execute($sql, $values);
        $rowCount               = $this->rowCount();
        $this->destroy();

        return $rowCount;
    }

    /**
     * @param stdClass $record
     * @return array
     */
    public function updateSqlQuery(stdClass $record) : array
    {
        Assert($record)
            ->notEmpty("The data passed to update does not contain any data")
            ->isInstanceOf('stdClass', "The data to be updated must be an object or an array of objects");
        // Make sure we remove the primary key

        return $this->updateSql((array)$record);
    }

    /**
     * @param $record
     * @return array
     */
    protected function updateSql(array $record) : array
    {
        unset($record[$this->getPrimaryKeyName()]);
        Assert($this->limit)->eq(0, 'You cannot limit updates');

        $fieldList              = [];
        $fieldAlias             = $this->addUpdateAlias && ! empty($this->tableAlias) ? "{$this->tableAlias}." : '';
        foreach ( $record as $key => $value )
        {
            if ( is_numeric($key) )
            {
                $fieldList[]            = $value;
                unset($record[$key]);

                continue;
            }
            $fieldList[]            = "{$fieldAlias}{$key} = ?";
        }
        $rawParams              = [];
        foreach ( $this->updateRaw as $rawUpdate )
        {
            $fieldList[]            = $rawUpdate[0];
            $rawParams              = array_merge($rawParams, $rawUpdate[1]);
        }
        $fieldList              = implode(', ', $fieldList);
        $whereStr               = $this->getWhereString();
        $joins                  = ! empty($this->joinSources) ? (' ').implode(' ',$this->joinSources) : '';
        $alias                  = ! empty($this->tableAlias) ? " AS {$this->tableAlias}" : '';
        $sql                    = "UPDATE {$this->tableName}{$alias}{$joins} SET {$fieldList}{$whereStr}";
        $values                 = array_merge(array_values($record), $rawParams, $this->getWhereParameters());

        return [$sql, $values];
    }

    /**
     * @param bool $deleteAll
     * @param bool $force
     * @return int
     * @throws Exception
     */
    public function delete(bool $deleteAll=false, bool $force=false) : int
    {
        if ( ! $force && $this->softDeletes )
        {
            if ( $this->isDeleterTableType() )
            {
                return $this->updateDeleted();
            }

            return $this->updateArchived();
        }

        list($sql, $params)     = $this->deleteSqlQuery();
        if ( empty($this->whereConditions) && ! $deleteAll )
        {
            throw new Exception("You cannot update an entire table without calling update with deleteAll=true");
        }
        $this->execute($sql, $params);

        return $this->rowCount();
    }

    /**
     * @return bool
     */
    public function isSoftDelete() : bool
    {
        return $this->softDeletes;
    }

    /**
     * @param bool|false $force
     * @return FluentPdoModel|$this
     * @throws Exception
     */
    public function truncate(bool $force=false) : FluentPdoModel
    {
        if ( $force )
        {
            $this->execute('SET FOREIGN_KEY_CHECKS = 0');
        }
        $this->execute("TRUNCATE TABLE {$this->tableName}");
        if ( $force )
        {
            $this->execute('SET FOREIGN_KEY_CHECKS = 1');
        }

        return $this;
    }

    /**
     * @return array
     */
    public function deleteSqlQuery() : array
    {
        $query                  = "DELETE FROM {$this->tableName}";
        if ( ! empty($this->whereConditions) )
        {
            $query                  .= $this->getWhereString(true);

            return [$query, $this->getWhereParameters()];
        }

        return [$query, []];
    }


    /**
     * Return the aggregate count of column
     *
     * @param string $column
     * @param int $cacheTtl
     * @return float
     */
    public function count(string $column='*', int $cacheTtl=self::CACHE_NO) : float
    {
        $this->explicitSelectMode();

        if ( empty($this->groupBy) )
        {
            return $this->fetchFloat("COUNT({$column}) AS cnt", 0, $cacheTtl);
        }
        $this->select("COUNT({$column}) AS cnt");
        $sql                    = $this->getSelectQuery();
        $params                 = $this->getWhereParameters();
        $sql                    = "SELECT COUNT(*) AS cnt FROM ({$sql}) t";
        $object                 = $this->query($sql, $params)->fetchOne(0, $cacheTtl);
        if ( ! $object || empty($object->cnt) )
        {
            return 0.0;
        }

        return (float)$object->cnt;
    }


    /**
     * Return the aggregate max count of column
     *
     * @param string $column
     * @param int $cacheTtl
     * @return int|float|string|null
     */
    public function max(string $column, int $cacheTtl=self::CACHE_NO)
    {
        return $this
            ->explicitSelectMode()
            ->fetchField("MAX({$column}) AS max", 0, $cacheTtl);
    }


    /**
     * Return the aggregate min count of column
     *
     * @param string $column
     * @param int $cacheTtl
     * @return int|float|string|null
     */
    public function min(string $column, int $cacheTtl=self::CACHE_NO)
    {
        return $this
            ->explicitSelectMode()
            ->fetchField("MIN({$column}) AS min", 0, $cacheTtl);
    }

    /**
     * Return the aggregate sum count of column
     *
     * @param string $column
     * @param int $cacheTtl
     * @return int|float|string|null
     */
    public function sum(string $column, int $cacheTtl=self::CACHE_NO)
    {
        return $this
            ->explicitSelectMode()
            ->fetchField("SUM({$column}) AS sum", 0, $cacheTtl);
    }

    /**
     * Return the aggregate average count of column
     *
     * @param string $column
     * @param int $cacheTtl
     * @return int|float|string|null
     */
    public function avg(string $column, int $cacheTtl=self::CACHE_NO)
    {
        return $this
            ->explicitSelectMode()
            ->fetchField("AVG({$column}) AS avg", 0, $cacheTtl);
    }

    /*******************************************************************************/
// Utilities methods

    /**
     * Reset fields
     *
     * @return FluentPdoModel|$this
     */
    public function reset() : FluentPdoModel
    {
        $this->whereParameters      = [];
        $this->selectFields         = [];
        $this->joinSources          = [];
        $this->joinAliases          = [];
        $this->whereConditions      = [];
        $this->limit                = 0;
        $this->offset               = 0;
        $this->orderBy              = [];
        $this->groupBy              = [];
        $this->andOrOperator        = self::OPERATOR_AND;
        $this->having               = [];
        $this->wrapOpen             = false;
        $this->lastWrapPosition     = 0;
        $this->pdoStmt              = null;
        $this->distinct             = false;
        $this->requestedFields      = [];
        $this->filterMeta           = [];
        $this->cacheTtl             = -1;
        $this->timer                = [];
        $this->builtQuery           = '';
        $this->pagingMeta           = [];
        $this->rawSql               = null;
        $this->explicitSelectMode   = false;

        return $this;
    }


    /**
     * @return FluentPdoModel|$this
     */
    public function removeUnauthorisedFields() : FluentPdoModel
    {
        return $this;
    }

    /**
     * @return Closure[]
     */
    protected function getFieldHandlers() : array
    {
        $columns                = $this->getColumns(true);
        if ( empty($columns) )
        {
            return [];
        }

        return [
            'id'                    => function(string $field, $value, string $type='', stdClass $record=null) {

                unset($record);
                $value                  = $this->fixType($field, $value);
                if ( $type === self::SAVE_INSERT )
                {
                    Validate($value)->fieldName($field)->nullOr()->id('ID must be a valid integer id, (%s) submitted.');

                    return $value;
                }
                Validate($value)->fieldName($field)->id('ID must be a valid integer id, (%s) submitted.');

                return $value;
            },
            self::CREATOR_ID_FIELD  => function(string $field, $value, string $type='', stdClass $record=null) {

                unset($type, $record);
                $value                  = $this->fixType($field, $value);
                // Created user id is set to current user if record is an insert or deleted if not (unless override is true)
                $value                  = $this->allowMetaOverride ? $value : $this->getUserId();
                Validate($value)->fieldName($field)->id('Created By must be a valid integer id, (%s) submitted.');

                return $value;
            },
            self::CREATED_TS_FIELD  => function(string $field, $value, string $type='', stdClass $record=null) {

                unset($type, $record);
                $value                  = $this->fixType($field, $value);
                // Created ts is set to now if record is an insert or deleted if not (unless override is true)
                $value                  = static::dateTime($this->allowMetaOverride ? $value : null);
                Validate($value)->fieldName($field)->date('Created must be a valid timestamp, (%s) submitted.');

                return $value;
            },
            self::MODIFIER_ID_FIELD => function(string $field, $value, string $type='', stdClass $record=null) {

                unset($type, $record);
                $value                  = $this->fixType($field, $value);
                // Modified user id is set to current user (unless override is true)
                $value                  = $this->allowMetaOverride ? $value : $this->getUserId();
                Validate($value)->fieldName($field)->id('Modified By must be a valid integer id, (%s) submitted.');

                return $value;
            },
            self::MODIFIED_TS_FIELD => function(string $field, $value, string $type='', stdClass $record=null) {

                unset($type, $record);
                $value                  = $this->fixType($field, $value);
                // Modified timestamps are set to now (unless override is true)
                $value                  = static::dateTime($this->allowMetaOverride ? $value : null);
                Validate($value)->fieldName($field)->date('Modified must be a valid timestamp, (%s) submitted.');

                return $value;
            },
            self::DELETER_ID_FIELD => function(string $field, $value, string $type='', stdClass $record=null) {

                if ( $type === self::SAVE_INSERT )
                {
                    return null;
                }
                if ( empty($record->deleted_ts) )
                {
                    return null;
                }
                unset($type, $record);
                $value                  = $this->fixType($field, $value);

                // Modified user id is set to current user (unless override is true)
                $value                  = $this->allowMetaOverride ? $value : $this->getUserId();
                Validate($value)->fieldName($field)->nullOr()->id('Deleter must be a valid integer id, (%s) submitted.');

                return $value;
            },
            self::DELETED_TS_FIELD => function(string $field, $value, string $type='', stdClass $record=null) {

                if ( $type === self::SAVE_INSERT )
                {
                    return null;
                }
                unset($type, $record);
                $value                  = $this->fixType($field, $value);
                if ( $value )
                {
                    $value                  = static::dateTime($this->allowMetaOverride ? $value : null);
                    Validate($value)->fieldName($field)->date('Deleted Timestamp must be a valid timestamp, (%s) submitted.');
                }

                return $value;
            },
        ];
    }

    /**
     * @return bool
     */
    public function begin() : bool
    {
        $pdo                    = $this->getPdo();
        $oldDepth               = $pdo->getTransactionDepth();
        $res                    = $pdo->beginTransaction();
        $newDepth               = $pdo->getTransactionDepth();
        $this->getLogger()->debug("Calling db begin transaction", [
            'old_depth'             => $oldDepth,
            'new_depth'             => $newDepth,
            'trans_started'         => $newDepth === 1 ? true : false,
        ]);

        return $res;
    }

    /**
     * @return bool
     */
    public function commit() : bool
    {
        $pdo                    = $this->getPdo();
        $oldDepth               = $pdo->getTransactionDepth();
        $res                    = $pdo->commit();
        $newDepth               = $pdo->getTransactionDepth();
        $this->getLogger()->debug("Calling db commit transaction", [
            'old_depth'             => $oldDepth,
            'new_depth'             => $newDepth,
            'trans_ended'           => $newDepth === 0 ? true : false,
        ]);
        if ( ! $res )
        {
            return false;
        }

        return $res === 0 ? true : $res;
    }

    /**
     * @return bool
     */
    public function rollback() : bool
    {
        $pdo                    = $this->getPdo();
        $oldDepth               = $pdo->getTransactionDepth();
        $res                    = $pdo->rollback();
        $newDepth               = $pdo->getTransactionDepth();
        $this->getLogger()->debug("Calling db rollback transaction", [
            'old_depth'             => $oldDepth,
            'new_depth'             => $newDepth,
            'trans_ended'           => $newDepth === 0 ? true : false,
        ]);

        return $res;
    }

    /**
     * @param stdClass $record
     * @param  string  $type
     * @return stdClass
     */
    public function applyGlobalModifiers(stdClass $record, string $type) : stdClass
    {
        unset($type);
        foreach ( $record as $field => $value )
        {
            if ( is_string($record->{$field}) )
            {
                $record->{$field}       = str_replace(["\r\n", "\\r\\n", "\\n"], "\n", $value);
            }
        }

        return $record;
    }

    /**
     * @param stdClass $record
     * @param  string $type
     * @return stdClass
     */
    public function removeUnneededFields(stdClass $record, string $type) : stdClass
    {
        $creatorId              = self::CREATOR_ID_FIELD;
        $createdTs              = self::CREATED_TS_FIELD;
        $activeFg               = self::STATUS_FIELD;

        // remove un-needed fields
        $columns = $this->getColumns(true);
        if ( empty($columns) )
        {
            return $record;
        }
        foreach ( $record as $name => $value )
        {
            if ( ! in_array($name, $columns) || in_array($name, $this->virtualFields) )
            {
                unset($record->{$name});
            }
        }
        if ( property_exists($record, $createdTs) && $type !== 'INSERT' && ! $this->allowMetaOverride )
        {
            unset($record->{$createdTs});
        }
        if ( property_exists($record, $creatorId) && $type !== 'INSERT' && ! $this->allowMetaOverride )
        {
            unset($record->{$creatorId});
        }
        unset($record->{$activeFg});

        return $record;
    }


    /**
     * @param array $ids
     * @param array $values
     * @param int   $batch
     * @return bool
     */
    public function setById(array $ids, array $values, int $batch=1000) : bool
    {
        $ids                    = array_unique($ids);
        if ( empty($ids) )
        {
            return true;
        }
        if ( count($ids) <= $batch )
        {
            return (bool)$this->whereIn('id', $ids)->updateArr($values);
        }
        while ( ! empty($ids) )
        {
            $thisBatch              = array_slice($ids, 0, $batch);
            $ids                    = array_diff($ids, $thisBatch);
            $this->reset()->whereIn('id', $thisBatch)->updateArr($values);
        }

        return true;
    }


    /**
     * @param string $displayColumnValue
     * @return int
     */
    public function resolveId(string $displayColumnValue) : int
    {
        $displayColumn          = $this->getDisplayColumn();
        $className              = get_class($this);
        Assert($displayColumn)->notEmpty("Could not determine the display column for model ({$className})");

        return $this
            ->reset()
            ->where($displayColumn, $displayColumnValue)
            ->fetchInt('id', 0, self::ONE_HOUR);
    }

    /**
     * @param int   $resourceId
     * @param array $query
     * @param array $extraFields
     * @param int $cacheTtl
     * @return array
     */
    public function fetchApiResource(int $resourceId, array $query=[], array $extraFields=[], int $cacheTtl=self::CACHE_NO) : array
    {
        Assert($resourceId)->id();

        $query['_limit']        = 1;
        $pagingMetaData         = $this->wherePk($resourceId)->prepareApiResource($query, $extraFields);
        if ( $pagingMetaData['total'] === 0 )
        {
            return [[], $pagingMetaData];
        }

        return [$this->fetchOne($resourceId, $cacheTtl), $pagingMetaData];
    }

    /**
     * @param array     $query
     * @param array     $extraFields
     * @param int       $cacheTtl
     * @param string    $permEntity
     * @return array
     */
    public function fetchApiResources(array $query=[], array $extraFields=[], int $cacheTtl=self::CACHE_NO, string $permEntity='') : array
    {
        $pagingMetaData         = $this->prepareApiResource($query, $extraFields);
        if ( $pagingMetaData['total'] === 0 )
        {
            return [[], $pagingMetaData];
        }
        $results                = $this->fetch('', $cacheTtl);
        if ( ! $permEntity )
        {
            return [$results, $pagingMetaData];
        }
        foreach ( $results as $rec )
        {
            if ( ! empty($rec->id) )
            {
                $pagingMetaData['perms'][(int)$rec->id] = $this->getMaskByResourceAndId($permEntity, $rec->id);
            }
        }

        return [$results, $pagingMetaData];
    }


    /**
     * @return array
     */
    public function getSearchableAssociations() : array
    {
        $belongsTo              = ! empty($this->associations['belongsTo']) ? $this->associations['belongsTo'] : [];
        unset($belongsTo['CreatedBy'], $belongsTo['ModifiedBy']);

        return $belongsTo;
    }

    /**
     * @param array $fields
     */
    public function removeUnrequestedFields(array $fields)
    {
        foreach ( $this->selectFields as $idx => $field )
        {
            $field = trim(static::after(' AS ', $field, true));
            if ( ! in_array($field, $fields) )
            {
                unset($this->selectFields[$idx]);
            }
        }
    }

    /**
     * @param array $removeFields
     */
    public function removeFields(array $removeFields=[])
    {
        $searches               = [];
        foreach ( $removeFields as $removeField )
        {
            $removeField            = str_replace("{$this->tableAlias}.", '', $removeField);
            $searches[]             = "{$this->tableAlias}.{$removeField}";
            $searches[]             = $removeField;
        }
        foreach ( $this->selectFields as $idx => $selected )
        {
            $selected               = stripos($selected, ' AS ') !== false ? preg_split('/ as /i', $selected) : [$selected];
            foreach ( $selected as $haystack )
            {
                foreach ( $searches as $search )
                {
                    if ( trim($haystack) === trim($search) )
                    {
                        unset($this->selectFields[$idx]);

                        continue;
                    }
                }
            }
        }
    }

    /**
     * @return FluentPdoModel|$this
     */
    public function defaultFilters() : FluentPdoModel
    {
        return $this;
    }

    /**
     * @param bool $allow
     *
     * @return FluentPdoModel|$this
     */
    public function allowMetaColumnOverride(bool $allow=false) : FluentPdoModel
    {
        $this->allowMetaOverride = $allow;

        return $this;
    }

    /**
     * @param bool $skip
     *
     * @return FluentPdoModel|$this
     */
    public function skipMetaUpdates(bool $skip=true) : FluentPdoModel
    {
        $this->skipMetaUpdates  = $skip;

        return $this;
    }

    /**
     * @param bool $add
     *
     * @return FluentPdoModel|$this
     */
    public function addUpdateAlias(bool $add=true) : FluentPdoModel
    {
        $this->addUpdateAlias   = $add;

        return $this;
    }

    /**
     * @param stdClass $record
     * @return stdClass
     */
    public function onFetch(stdClass $record) : stdClass
    {
        $record                 = $this->trimAndLowerCaseKeys($record);
        if ( $this->filterOnFetch )
        {
            $record                 = $this->cleanseRecord($record);
        }

        $record                 =  $this->fixTypesToSentinel($record);

        return $this->fixTimestamps($record);
    }

    /**
     * @param $value
     * @return string
     */
    public function gzEncodeData(string $value) : string
    {
        if ( $this->hasGzipPrefix($value) )
        {
            return $value;
        }

        return static::GZIP_PREFIX . base64_encode(gzencode($value, 9));
    }

    /**
     * @param $value
     * @return mixed|string
     */
    public function gzDecodeData(string $value) : string
    {
        if ( ! $this->hasGzipPrefix($value) )
        {
            return $value;
        }
        $value = substr_replace($value, '', 0, strlen(static::GZIP_PREFIX));

        return gzdecode(base64_decode($value));
    }

    /**
     * @param $value
     * @return bool
     */
    protected function hasGzipPrefix(string $value) : bool
    {
        return substr($value, 0, strlen(static::GZIP_PREFIX)) === static::GZIP_PREFIX ? true : false;
    }

    /**
     * @param stdClass $record
     * @return stdClass
     */
    public function fixTimestamps(stdClass $record) : stdClass
    {
        foreach ( $record as $field => $value )
        {
            if ( preg_match('/_ts$/', $field) )
            {
                $record->{$field}       = empty($value) ? $value : static::atom($value);
            }
        }

        return $record;
    }

    /**
     * @param int $max
     * @return FluentPdoModel|$this
     */
    public function setMaxRecords(int $max) : FluentPdoModel
    {
        Assert($max)->int();
        $this->defaultMax       = $max;

        return $this;
    }


    /**
     * @param stdClass $record
     * @param string   $type
     * @return stdClass
     */
    public function afterSave(stdClass $record, string $type) : stdClass
    {
        unset($type);
        $this->clearCacheByTable();
        foreach ( $record as $col => $value )
        {
            if ( !empty($record->{$col}) )
            {
                if ( preg_match('/_ts$/', $col) )
                {
                    $record->{$col}         = static::atom($value);
                }
                if ( preg_match('/_am$/', $col) )
                {
                    $record->{$col}         = number_format($value, 2, '.', '');
                }
            }
        }

        return $record;
    }

    /**
     * @param stdClass $record
     * @param string $type
     * @return stdClass
     */
    public function addDefaultFields(stdClass $record, string $type) : stdClass
    {
        $columns                = $this->getColumns(true);
        if ( empty($columns) )
        {
            return $record;
        }
        $defaults               = [
            self::SAVE_UPDATE       => [
                self::MODIFIER_ID_FIELD => null,
                self::MODIFIED_TS_FIELD => null,
            ],
            self::SAVE_INSERT       => [
                self::CREATOR_ID_FIELD  => null,
                self::CREATED_TS_FIELD  => null,
                self::MODIFIER_ID_FIELD => null,
                self::MODIFIED_TS_FIELD => null,
            ]
        ];
        if ( $this->skipMetaUpdates )
        {
            $defaults[self::SAVE_UPDATE] = [];
        }
        $columns            = array_flip($this->getColumns());
        $defaults           = array_intersect_key($defaults[$type], $columns);
        foreach ( $defaults as $column => $def )
        {
            $record->{$column} = $record->{$column} ?? $def;
        }
        unset($record->active);

        return $record;
    }


    /**
     * @return bool
     */
    public function createTable() : bool
    {
        return true;
    }

    /**
     * @param bool|false $force
     * @return FluentPdoModel|$this
     * @throws Exception
     */
    public function dropTable(bool $force=false) : FluentPdoModel

The parameter $force is not used and could be removed.

    {
        return $this;
    }

    protected function compileHandlers()
    {
        if ( $this->handlers )
        {
            return;
        }
        $parentHandlers         = self::getFieldHandlers();
        $this->handlers         = array_merge($parentHandlers, $this->getFieldHandlers());
    }

    /**
     * @param string $viewName
     * @param int $cacheTtl
     * @return array
     */
    public function getViewColumns($viewName, $cacheTtl=self::CACHE_NO)
    {
        return $this->getColumnsByTableFromDb($viewName, $cacheTtl);
    }

    /**
     * @param int $id
     * @return string
     */
    public function getDisplayNameById(int $id) : string
    {
        $displayColumn  = $this->getDisplayColumn();
        $className      = get_class($this);
        Assert($displayColumn)->notEmpty("Could not determine the display column for model ({$className})");

        return $this
            ->reset()
            ->fetchStr($displayColumn, $id, self::ONE_HOUR);
    }

    /**
     * @param int $id
     * @param string $displayColumnValue
     * @return bool
     */
    public function validIdDisplayNameCombo(int $id, $displayColumnValue) : bool
    {
        return $displayColumnValue === $this->getDisplayNameById($id);
    }

    /**
     * @param array $toPopulate
     * @return stdClass
     */
    protected function getEmptyObject(array $toPopulate=[]) : stdClass
    {
        $toPopulate[]   = 'id';

        return (object)array_flip($toPopulate);
    }

    /**
     * @param array $toPopulate
     * @return stdClass
     */
    protected static function emptyObject(array $toPopulate=[]) : stdClass
    {
        $toPopulate[]   = 'id';

        return (object)array_flip($toPopulate);
    }

    /**
     * @param int $id
     * @return bool
     */
    public static function isId(int $id) : bool
    {
        return $id > 0;
    }

    /**
     * @param int $cacheTtl
     * @return int
     */
    public function activeCount(int $cacheTtl=self::CACHE_NO) : int
    {
        return (int)$this->whereActive()->count('*', $cacheTtl);
    }

    /**
     * @param string        $tableAlias
     * @param string   $columnName
     * @return FluentPdoModel|$this
     */
    public function whereActive(string $tableAlias='', string $columnName=self::STATUS_FIELD) : FluentPdoModel
    {
        return $this->whereStatus(static::ACTIVE, $tableAlias, $columnName);
    }

    /**
     * @param string        $tableAlias
     * @param string        $columnName
     * @return FluentPdoModel|$this
     */
    public function whereInactive(string $tableAlias='', string $columnName=self::STATUS_FIELD) : FluentPdoModel
    {
        return $this->whereStatus(static::INACTIVE, $tableAlias, $columnName);
    }

    /**
     * @param string        $tableAlias
     * @param string        $columnName
     * @return FluentPdoModel|$this
     */
    public function whereArchived(string $tableAlias='', string $columnName='status') : FluentPdoModel
    {
        return $this->whereStatus(static::ARCHIVED, $tableAlias, $columnName);
    }

    /**
     * @param int $status
     * @param string $tableAlias
     * @param string $columnName
     * @return FluentPdoModel|$this
     */
    public function whereStatus(int $status, string $tableAlias='', string $columnName=self::STATUS_FIELD) : FluentPdoModel
    {
        Assert($status)->inArray([static::ACTIVE, static::INACTIVE, static::ARCHIVED]);

        $tableAlias = empty($tableAlias) ? $this->getTableAlias() : $tableAlias;
        $field      = empty($tableAlias) ? $columnName : "{$tableAlias}.{$columnName}";

        return $this->where($field, $status);
    }

    /**
     * @param int $id
     * @return int
     */
    public function updateActive(int $id=0) : int
    {
        Assert($id)->unsignedInt();
        if ( $id )
        {
            $this->wherePk($id);
        }

        return $this->updateStatus(static::ACTIVE);
    }

    /**
     * @param int $id
     * @return int
     */
    public function updateInactive(int $id=0) : int
    {
        Assert($id)->unsignedInt();
        if ( $id )
        {
            $this->wherePk($id);
        }
        return $this->updateStatus(static::INACTIVE);
    }

    /**
     * @param string $field
     * @param int  $id
     * @return int
     */
    public function updateNow(string $field, int $id=0) : int
    {
        Assert($field)->notEmpty();

        return $this->updateField($field, date('Y-m-d H:i:s'), $id);
    }

    /**
     * @param string $field
     * @param int  $id
     * @return int
     */
    public function updateToday($field, int $id=0) : int
    {
        Assert($field)->notEmpty();

        return $this->updateField($field, date('Y-m-d'), $id);
    }

    /**
     * @param int $id
     * @return int
     */
    public function updateDeleted(int $id=0) : int
    {
        Assert($id)->unsignedInt();
        if ( $id )
        {
            $this->wherePk($id);
        }

        return $this->update((object)[
            self::DELETER_ID_FIELD  => $this->getUserId(),
            self::DELETED_TS_FIELD  => static::dateTime(),
        ]);
    }

    /**
     * @return bool
     */
    public function isDeleterTableType() : bool
    {
        $columns                = $this->getColumns();
        if ( in_array(self::ACTIVE_FIELD, $columns) && in_array(self::DELETER_ID_FIELD, $columns) && in_array(self::DELETED_TS_FIELD, $columns) )
        {
            return true;
        }

        return false;
    }

    /**
     * @param int $id
     * @return int
     */
    public function updateArchived(int $id=0) : int
    {
        Assert($id)->unsignedInt();
        if ( $id )
        {
            $this->wherePk($id);
        }

        return $this->updateStatus(static::ARCHIVED);
    }

    /**
     * @param int $status
     * @return int
     * @throws \Exception
     */
    public function updateStatus(int $status)
    {
        Assert($status)->inArray([static::ACTIVE, static::INACTIVE, static::ARCHIVED]);

        return $this->updateField('status', $status);
    }

    /**
     * Return a YYYY-MM-DD HH:II:SS date format
     *
     * @param string $datetime - An english textual datetime description
     *          now, yesterday, 3 days ago, +1 week
     *          http://php.net/manual/en/function.strtotime.php
     * @return string YYYY-MM-DD HH:II:SS
     */
    public static function NOW(string $datetime='now') : string
    {
        return (new DateTime($datetime ?: 'now'))->format('Y-m-d H:i:s');
    }

    /**
     * Return a string containing the given number of question marks,
     * separated by commas. Eg '?, ?, ?'
     *
     * @param int - total of placeholder to insert
     * @return string
     */
    protected function makePlaceholders(int $numberOfPlaceholders=1) : string
    {
        return implode(', ', array_fill(0, $numberOfPlaceholders, '?'));
    }

    /**
     * Format the table{Primary|Foreign}KeyName
     *
     * @param  string $pattern
     * @param  string $tableName
     * @return string
     */
    protected function formatKeyName(string $pattern, string $tableName) : string
    {
        return sprintf($pattern, $tableName);
    }

    /**
     * @param array $query
     * @param array $extraFields
     * @return array
     * @throws \Exception
     */
    protected function prepareApiResource(array $query=[], array $extraFields=[]) : array
    {
        $this->defaultFilters()->filter($query)->paginate($query);
        $pagingMetaData         = $this->getPagingMeta();
        if ( $pagingMetaData['total'] === 0 )
        {
            return $pagingMetaData;
        }
        $this->withBelongsTo($pagingMetaData['fields']);
        if ( ! empty($extraFields) )
        {
            $this->select($extraFields, '', false);
        }
        $this->removeUnauthorisedFields();

The call to the method Terah\FluentPdoModel\FluentPdoModel::removeUnauthorisedFields() seems un-needed as the method has no side-effects.

        if ( ! empty($pagingMetaData['fields']) )
        {
            $this->removeUnrequestedFields($pagingMetaData['fields']);
        }

        return $pagingMetaData;
    }

    /**
     * @param string $query
     * @param array $parameters
     *
     * @return array
     */
    protected function logQuery(string $query, array $parameters) : array
    {
        $query                  = $this->buildQuery($query, $parameters);
        if ( ! $this->logQueries )
        {
            return ['', ''];
        }
        $ident                  = substr(str_shuffle(md5($query)), 0, 10);
        $this->getLogger()->debug($ident . ': ' . PHP_EOL . $query);
        $this->timer['start']   = microtime(true);

        return [$query, $ident];
    }

    /**
     * @param string $ident
     * @param string $builtQuery
     */
    protected function logSlowQueries(string $ident, string $builtQuery)
    {
        if ( ! $this->logQueries )
        {
            return ;
        }
        $this->timer['end']     = microtime(true);
        $secondsTaken           = round($this->timer['end'] - $this->timer['start'], 3);
        if ( $secondsTaken > $this->slowQuerySecs )
        {
            $this->getLogger()->warning("SLOW QUERY - {$ident} - {$secondsTaken} seconds:\n{$builtQuery}");
        }
    }

    /**
     * @return float
     */
    public function getTimeTaken() : float
    {
        $secondsTaken           = $this->timer['end'] - $this->timer['start'];

        return (float)$secondsTaken;
    }

    /**
     * @param $secs
     * @return FluentPdoModel|$this
     */
    public function slowQuerySeconds(int $secs) : FluentPdoModel
    {
        Assert($secs)->notEmpty("Seconds cannot be empty.")->numeric("Seconds must be numeric.");
        $this->slowQuerySecs    = $secs;

        return $this;
    }


    /**
     * @param       $field
     * @param array $values
     * @param string  $placeholderPrefix
     *
     * @return array
     */
    public function getNamedWhereIn(string $field, array $values, string $placeholderPrefix='') : array
    {
        Assert($field)->string()->notEmpty();
        Assert($values)->isArray();

        if ( empty($values) )
        {
            return ['', []];
        }
        $placeholderPrefix      = $placeholderPrefix ?: strtolower(str_replace('.', '__', $field));
        $params                 = [];
        $placeholders           = [];
        $count                  = 1;
        foreach ( $values as $val )
        {
            $name                   = "{$placeholderPrefix}_{$count}";
            $params[$name]          = $val;
            $placeholders[]         = ":{$name}";
            $count++;
        }
        $placeholders           = implode(',', $placeholders);

        return ["AND {$field} IN ({$placeholders})\n", $params];
    }

    /**
     * @param string $field
     * @param string $delimiter
     *
     * @return array
     */
    protected function getColumnAliasParts(string $field, string $delimiter=':') : array
    {
        $parts                  = explode($delimiter, $field);
        if ( count($parts) === 2 )
        {
            return $parts;
        }
        $parts                  = explode('.', $field);
        if ( count($parts) === 2 )
        {
            return $parts;
        }

        return ['', $field];
    }

    /**
     * @param string $column
     * @param string $term
     * @return FluentPdoModel|$this
     */
    protected function addWhereClause(string $column, string $term) : FluentPdoModel
    {
        /*

         whereLike          i.e ?name=whereLike(%terry%)
         whereNotLike       i.e ?name=whereNotLike(%terry%)
         whereLt            i.e ?age=whereLt(18)
         whereLte           i.e ?age=whereLte(18)
         whereGt            i.e ?event_dt=whereGt(2014-10-10)
         whereGte           i.e ?event_dt=whereGte(2014-10-10)
         whereBetween       i.e ?event_dt=whereBetween(2014-10-10,2014-10-15)
         whereNotBetween    i.e ?event_dt=whereNotBetween(2014-10-10,2014-10-15)

         */
        list ($func, $matches)  = $this->parseWhereClause($term);
        switch ($func)
        {
            case 'whereLike':

                return $this->whereLike($column, $matches[0]);

            case 'whereNotLike':

                return $this->whereNotLike($column, $matches[0]);

            case 'whereLt':

                return $this->whereLt($column, $matches[0]);

            case 'whereLte':

                return $this->whereLte($column, $matches[0]);

            case 'whereGt':

                return $this->whereGt($column, $matches[0]);

            case 'whereGte':

                return $this->whereGte($column, $matches[0]);

            case 'whereBetween':

                return $this->whereBetween($column, $matches[0], $matches[1]);

            case 'whereNotBetween':

                return $this->whereNotBetween($column, $matches[0], $matches[1]);
        }

        return $this->where($column, $term);
    }

    /**
     * @param string $term
     * @return array
     */
    public function parseWhereClause(string $term) : array
    {
        $modifiers              = [
            'whereLike'             => '/^whereLike\(([%]?[ a-z0-9:-]+[%]?)\)$/i',
            'whereNotLike'          => '/^whereNotLike\(([%]?[ a-z0-9:-]+[%]?)\)$/i',
            'whereLt'               => '/^whereLt\(([ a-z0-9:-]+)\)$/i',
            'whereLte'              => '/^whereLte\(([ a-z0-9:-]+)\)$/i',
            'whereGt'               => '/^whereGt\(([ a-z0-9:-]+)\)$/i',
            'whereGte'              => '/^whereGte\(([ a-z0-9:-]+)\)$/i',
            'whereBetween'          => '/^whereBetween\(([ a-z0-9:-]+),([ a-z0-9:-]+)\)$/i',
            'whereNotBetween'       => '/^whereNotBetween\(([ a-z0-9:-]+),([ a-z0-9:-]+)\)$/i',
        ];

        foreach ( $modifiers as $func => $regex )
        {
            if ( preg_match($regex, $term, $matches) )
            {
                array_shift($matches);

                return [$func, $matches];
            }
        }

        return ['', []];
    }

    public function destroy()
    {
        if ( !is_null($this->pdoStmt) )
        {
            $this->pdoStmt->closeCursor();
        }
        $this->pdoStmt          = null;
        $this->handlers         = [];
    }

    public function __destruct()
    {
        $this->destroy();
    }

    /**
     * Load a model
     *
     * @param string $modelName
     * @param AbstractPdo $connection
     * @return FluentPdoModel|$this
     * @throws ModelNotFoundException
     */
    public static function loadModel(string $modelName, AbstractPdo $connection=null) : FluentPdoModel
    {
        $modelName              = static::$modelNamespace . $modelName;
        if ( ! class_exists($modelName) )
        {
            throw new ModelNotFoundException("Failed to find model class {$modelName}.");
        }

        return new $modelName($connection);
    }

    /**
     * Load a model
     *
     * @param string      $tableName
     * @param AbstractPdo $connection
     * @return FluentPdoModel|$this
     */
    public static function loadTable(string $tableName, AbstractPdo $connection=null) : FluentPdoModel
    {
        $modelName              = Inflector::classify($tableName);
        Assert($modelName)->notEmpty("Could not resolve model name from table name.");

        return static::loadModel($modelName, $connection);
    }

    /**
     * @param string   $columnName
     * @param int $cacheTtl
     * @param bool $flushCache
     * @return bool
     */
    public function columnExists(string $columnName, int $cacheTtl=self::CACHE_NO, bool $flushCache=false) : bool
    {
        $columns = $this->getSchemaFromDb($cacheTtl, $flushCache);

        return array_key_exists($columnName, $columns);
    }

    /**
     * @param string $typeName
     * @return bool
     */
    public function typeExists(string $typeName) : bool
    {
        return $this->connection->typeExists($typeName);
    }

    /**
     * @param string   $foreignKeyName
     * @param int $cacheTtl
     * @param bool $flushCache
     * @return bool
     */
    public function foreignKeyExists(string $foreignKeyName, int $cacheTtl=self::CACHE_NO, bool $flushCache=false) : bool
    {
        $columns = $this->getSchemaFromDb($cacheTtl, $flushCache);

        return array_key_exists($foreignKeyName, $columns);
    }

    /**
     * @param string   $indexName
     * @param int $cacheTtl
     * @param bool $flushCache
     * @return bool
     */
    public function indexExists(string $indexName, int $cacheTtl=self::CACHE_NO, bool $flushCache=false) : bool
    {
        Assert($indexName)->string()->notEmpty();

        $callback = function() use ($indexName) {

            $index = $this->execute("SHOW INDEX FROM {$this->tableName} WHERE Key_name = ':indexName'", compact('indexName'));

            return $index ? true : false;
        };
        if  ( $cacheTtl === self::CACHE_NO )
        {
            return $callback();
        }
        $cacheKey   = '/column_schema/' . $this->tableName . '/index/' . $indexName;
        if ( $flushCache === true )
        {
            $this->clearCache($cacheKey);
        }

        return (bool)$this->cacheData($cacheKey, $callback, $cacheTtl);
    }



    /**
     * @param int $cacheTtl
     * @param bool $flushCache
     * @return FluentPdoModel|$this
     */
    public function loadSchemaFromDb(int $cacheTtl=self::CACHE_NO, bool $flushCache=false) : FluentPdoModel
    {
        $schema = $this->getSchemaFromDb($cacheTtl, $flushCache);
        $this->schema($schema);

        return $this;
    }

    /**
     * @param int $cacheTtl
     * @param bool $flushCache
     * @return Column[][]
     */
    public function getSchemaFromDb(int $cacheTtl=self::CACHE_NO, bool $flushCache=false) : array
    {
        $table                  = $this->getTableName();
        Assert($table)->string()->notEmpty();
        $schema                 = [];
        $columns                = $this->getColumnsByTableFromDb($table, $cacheTtl, $flushCache);
        foreach ( $columns[$table] as $column => $meta )
        {
            /** Column $meta */
            $schema[$column]        = $meta->dataType;
        }

        return $schema;
    }

    /**
     * @param int $cacheTtl
     * @param bool $flushCache
     * @return array
     */
    public function getForeignKeysFromDb(int $cacheTtl=self::CACHE_NO, bool $flushCache=false) : array
    {
        $table                  = $this->getTableName();
        Assert($table)->string()->notEmpty();
        $schema                 = [];
        $foreignKeys            = $this->getForeignKeysByTableFromDb($table, $cacheTtl, $flushCache);
        foreach ( $foreignKeys[$table] as $key => $meta )
        {
            $schema[$key]           = $meta->dataType;
        }

        return $schema;
    }

    /**
     * @param string $table
     * @param int $cacheTtl
     * @param bool $flushCache
     * @return Column[][]
     */
    protected function getColumnsByTableFromDb(string $table, int $cacheTtl=self::CACHE_NO, bool $flushCache=false) : array
    {
        Assert($table)->string()->notEmpty();

        $callback = function() use ($table) {

            return $this->connection->getColumns(true, $table);
        };
        $cacheKey   = '/column_schema/' . $table;
        if ( $flushCache === true )
        {
            $this->clearCache($cacheKey);
        }

        return (array)$this->cacheData($cacheKey, $callback, $cacheTtl);
    }

    /**
     * @param string $table
     * @param int $cacheTtl
     * @param bool $flushCache
     * @return Column[][]
     */
    protected function getForeignKeysByTableFromDb(string $table, int $cacheTtl=self::CACHE_NO, bool $flushCache=false) : array
    {
        Assert($table)->string()->notEmpty();

        $callback = function() use ($table) {

            return $this->connection->getForeignKeys($table);
        };
        $cacheKey   = '/foreign_keys_schema/' . $table;
        if ( $flushCache === true )
        {
            $this->clearCache($cacheKey);
        }

        return (array)$this->cacheData($cacheKey, $callback, $cacheTtl);
    }

    /**
     * @param string $table
     * @return bool
     */
    public function clearSchemaCache(string $table) : bool
    {
        return $this->clearCache('/column_schema/' . $table);
    }

    /**
     * @param stdClass $record
     * @return stdClass
     */
    public function cleanseRecord(stdClass $record) : stdClass
    {
        foreach ( $record as $field => $value )
        {
            if ( is_string($record->{$field}) )
            {
                $sanitised          = filter_var($record->{$field}, FILTER_SANITIZE_STRING, FILTER_FLAG_NO_ENCODE_QUOTES);
                $record->{$field}   = str_replace(["\r\n", "\\r\\n", "\\n"], "\n", $sanitised);
                if ( $this->logFilterChanges && $value !== $record->{$field} )
                {
                    $table              = $this->tableName ?: '';
                    $this->getLogger()->debug("Field {$table}.{$field} has been cleansed", ['old' => $value, 'new' => $record->{$field}]);
                }
            }
        }

        return $record;
    }

    /**
     * @param stdClass $record
     * @param string   $type
     * @return stdClass
     */
    public function beforeSave(stdClass $record, string $type) : stdClass
    {
        $record                 = $this->addDefaultFields($record, $type);
        $record                 = $this->applyGlobalModifiers($record, $type);
        $record                 = $this->applyHandlers($record, $type);
        $record                 = $this->removeUnneededFields($record, $type);

        return $record;
    }

    /**
     * @param array  $data
     * @param string $saveType
     * @param array  $preserve
     * @return array
     * @throws \Terah\Assert\AssertionFailedException
     */
    public function cleanseWebData(array $data, string $saveType, array $preserve=[]) : array
    {
        Assert($saveType)->inArray([self::SAVE_UPDATE, self::SAVE_INSERT]);
        $columns                = $this->getColumns(false);
        if ( empty($columns) )
        {
            return $data;
        }
        foreach ( $data as $field => $val )
        {
            $data[$field]           = empty($val) && $val !== 0 && ! is_array($val) ? null : $val;
        }
        $columns                = array_merge($columns, array_flip($preserve));

        return array_intersect_key($data, $columns);
    }

    /**
     * @return array
     */
    public function skeleton() : array
    {
        $skel                   = [];
        $columns                = $this->getColumns(false);
        foreach ( $columns as $column => $type )
        {
            $skel[$column]          = null;
        }

        return $skel;
    }

    /**
     * @param bool $toString
     * @return array
     */
    public function getErrors(bool $toString=false) : array
    {
        if ( ! $toString )
        {
            return $this->errors;
        }
        $errors                 = [];
        foreach ( $this->errors as $field => $error )
        {
            $errors[]               = implode("\n", $error);
        }

        return implode("\n", $errors);
    }

    /**
     * @param bool $throw
     * @return FluentPdoModel|$this
     */
    public function validationExceptions(bool $throw=true) : FluentPdoModel
    {
        $this->validationExceptions = $throw;

        return $this;
    }

    /**
     * @param array $query array('_limit' => int, '_offset' => int, '_order' => string, '_fields' => string, _search)
     *
     * @return FluentPdoModel|$this
     * @throws Exception
     */
    public function paginate(array $query=[]) : FluentPdoModel
    {
        $_fields                = null;
        $_order                 = null;
        $_limit                 = null;
        $_offset                = null;
        extract($query);
        $this->setLimit((int)$_limit, (int)$_offset);
        $this->setOrderBy((string)$_order);
        $_fields                = is_array($_fields) ? $_fields : (string)$_fields;
        $_fields                = empty($_fields) ? [] : $_fields;
        $_fields                = is_string($_fields) ? explode('|', $_fields) : $_fields;
        $_fields                = empty($_fields) ? [] : $_fields;
        $this->setFields(is_array($_fields) ? $_fields : explode('|', (string)$_fields));

        return $this;
    }

    /**
     * @param int $limit
     * @param int $offset
     * @return FluentPdoModel|$this
     */
    protected function setLimit(int $limit=0, int $offset=0) : FluentPdoModel
    {
        $limit                  = ! $limit || (int)$limit > (int)$this->defaultMax ? (int)$this->defaultMax : (int)$limit;
        if ( ! is_numeric($limit) )
        {
            return $this;
        }
        $this->limit((int)$limit);
        if ( $offset && is_numeric($offset) )
        {
            $this->offset((int)$offset);
        }

        return $this;
    }

    /**
     * @param array $fields
     * @return FluentPdoModel|$this
     * @throws Exception
     */
    protected function setFields(array $fields=[]) : FluentPdoModel
    {
        if ( ! $fields )
        {
            return $this;
        }
        $this->explicitSelectMode();
        $columns                = $this->getColumns();

        foreach ( $fields as $idx => $field )
        {
            list($alias, $field)    = $this->getColumnAliasParts($field);
            $field = $field === '_display_field' ? $this->displayColumn : $field;
            // Regular primary table field
            if ( ( empty($alias) || $alias === $this->tableAlias ) && in_array($field, $columns) )
            {
                $this->select("{$this->tableAlias}.{$field}");
                $this->requestedFields[] = "{$this->tableAlias}.{$field}";

                continue;
            }
            // Reference table field with alias
            if ( ! empty($alias) )
            {
                Assert($this->associations['belongsTo'])->keyExists($alias, "Invalid table alias ({$alias}) specified for the field query");
                Assert($field)->eq($this->associations['belongsTo'][$alias][3], "Invalid field ({$alias}.{$field}) specified for the field query");
                list(, , $joinField, $fieldAlias) = $this->associations['belongsTo'][$alias];
                $this->autoJoin($alias, static::LEFT_JOIN, false);
                $this->select($joinField, $fieldAlias);
                $this->requestedFields[] = $fieldAlias;

                continue;
            }
            // Reference table select field without alias
            $belongsTo              = array_key_exists('belongsTo', $this->associations) ?  $this->associations['belongsTo'] : [];
            foreach ( $belongsTo as $joinAlias => $config )
            {
                list(, , $joinField, $fieldAlias) = $config;
                if ( $field === $fieldAlias )
                {
                    $this->autoJoin($joinAlias, static::LEFT_JOIN, false);
                    $this->select($joinField, $fieldAlias);
                    $this->requestedFields[] = $fieldAlias;

                    continue;
                }
            }
        }

        return $this;
    }

    /**
     * @param string $orderBy
     * @return FluentPdoModel|$this|FluentPdoModel
     */
    protected function setOrderBy(string $orderBy='') : FluentPdoModel
    {
        if ( ! $orderBy )
        {
            return $this;
        }
        $columns                = $this->getColumns();
        list($order, $direction)= strpos($orderBy, ',') !== false ? explode(',', $orderBy) : [$orderBy, 'ASC'];
        list($alias, $field)    = $this->getColumnAliasParts(trim($order), '.');
        $field                  = explode(' ', $field);
        $field                  = trim($field[0]);
        $direction              = ! in_array(strtoupper(trim($direction)), ['ASC', 'DESC']) ? 'ASC' : strtoupper(trim($direction));
        $belongsTo              = array_key_exists('belongsTo', $this->associations) ? $this->associations['belongsTo'] : [];
        // Regular primary table order by
        if ( ( empty($alias) || $alias === $this->tableAlias ) && in_array($field, $columns) )
        {
            return $this->orderBy("{$this->tableAlias}.{$field}", $direction);
        }
        // Reference table order by with alias
        if ( ! empty($alias) )
        {
            Assert($belongsTo)->keyExists($alias, "Invalid table alias ({$alias}) specified for the order query");
            Assert($field)->eq($belongsTo[$alias][3], "Invalid field ({$alias}.{$field}) specified for the order query");

            return $this->autoJoin($alias)->orderBy("{$alias}.{$field}", $direction);
        }
        // Reference table order by without alias
        foreach ( $belongsTo as $joinAlias => $config )
        {
            if ( $field === $config[3] )
            {
                return $this->autoJoin($joinAlias)->orderBy($config[2], $direction);
            }
        }

        return $this;
    }

    /**
     * @return array
     */
    public function getPagingMeta()
    {
        if ( empty($this->pagingMeta) )
        {
            $this->setPagingMeta();
        }

        return $this->pagingMeta;
    }

    /**
     * @return FluentPdoModel|$this
     */
    public function setPagingMeta() : FluentPdoModel
    {
        $model                  = clone $this;
        $limit                  = intval($this->getLimit());
        $offset                 = intval($this->getOffset());
        $total                  = intval($model->withBelongsTo()->select('')->offset(0)->limit(0)->orderBy()->count());
        unset($model->handlers, $model); //hhmv mem leak
        $orderBys               = ! is_array($this->orderBy) ? [] : $this->orderBy;
        $this->pagingMeta       = [
            'limit'                 => $limit,
            'offset'                => $offset,
            'page'                  => $offset === 0 ? 1 : intval( $offset / $limit ) + 1,
            'pages'                 => $limit === 0 ? 1 : intval(ceil($total / $limit)),
            'order'                 => $orderBys,
            'total'                 => $total = $limit === 1 && $total > 1 ? 1 : $total,
            'filters'               => $this->filterMeta,
            'fields'                => $this->requestedFields,
            'perms'                 => [],
        ];

        return $this;
    }

    /**
     * Take a web request and format a query
     *
     * @param array $query
     *
     * @return FluentPdoModel|$this
     * @throws Exception
     */
    public function filter(array $query=[]) : FluentPdoModel
    {
        $columns                = $this->getColumns(false);
        $alias                  = '';
        foreach ( $query as $column => $value )
        {
            if ( in_array($column, $this->paginationAttribs) )
            {
                continue;
            }
            $field              = $this->findFieldByQuery($column, $this->displayColumn);
            if ( is_null($field) )
            {
                continue;
            }
            $this->filterMeta[$field]   = $value;
            $where                      = ! is_array($value) && mb_stripos((string)$value, '|') !== false ? explode('|', $value) : $value;
            if ( is_array($where) )
            {
                $this->whereIn($field, $where);
            }
            else
            {
                $this->addWhereClause($field, (string)$where);
            }
        }
        if ( empty($query['_search']) )
        {
            return $this;
        }
        $alias                  = ! empty($alias) ? $alias : $this->tableAlias;
        $stringCols             = array_filter($columns, function($type) {

            return in_array($type, ['varchar', 'text', 'enum']);
        });
        $terms                  = explode('|', $query['_search']);
        $whereLikes             = [];
        foreach ( $stringCols as $column => $type )
        {
            if ( in_array($column, $this->excludedSearchCols) )
            {
                continue;
            }
            foreach ( $terms as $term )
            {
                $whereLikes["{$alias}.{$column}"] = "%{$term}%";
            }
        }
        // Reference fields...
        $belongsTo = $this->getSearchableAssociations();
        foreach ( $belongsTo as $alias => $config )
        {
            foreach ( $terms as $term )
            {
                $whereLikes[$config[2]] = "%{$term}%";
            }
        }
        if ( empty($whereLikes) )
        {
            return $this;
        }
        $this->where('1', '1')->wrap()->_and();
        foreach ( $whereLikes as $column => $term )
        {
            $this->_or()->whereLike($column, $term);
        }
        $this->wrap();

        return $this;
    }

    /**
     * @param string $column
     * @param string $displayCol
     * @return string|null
     */
    protected function findFieldByQuery(string $column, string $displayCol)
    {
        list($alias, $field)    = $this->getColumnAliasParts($column);
        $field                  = $field === '_display_field' ? $displayCol : $field;
        $columns                = $this->getColumns();
        $tableAlias             = $this->getTableAlias();
        if ( ! empty($alias) && $alias === $tableAlias )
        {
            // Alias is set but the field isn't correct
            if ( ! in_array($field, $columns) )
            {
                return null;
            }
            return "{$alias}.{$field}";
        }
        // Alias isn't passed in but the field is ok
        if ( empty($alias) && in_array($field, $columns) )
        {
            return "{$tableAlias}.{$field}";
        }
//        // Alias is passed but not this table in but there is a matching field on this table
//        if ( empty($alias) ) //&& in_array($field, $columns) )
//        {
//            return null;
//        }
        // Now search the associations for the field
        $associations = $this->getSearchableAssociations();
        if ( ! empty($alias) )
        {
            if ( array_key_exists($alias, $associations) && $associations[$alias][3] === $field )
            {
                return "{$alias}.{$field}";
            }

            return null;
        }
        foreach ( $associations as $assocAlias => $config )
        {
            list(, , $assocField, $fieldAlias) = $config;
            if ( $fieldAlias === $field )
            {
                return $assocField;
            }
        }

        return null;
    }

    /**
     * @param string $field
     * @param mixed $value
     * @param array $pdoMetaData
     * @return float|int
     * @throws Exception
     */
    protected function fixTypeToSentinel(string $field, $value, array $pdoMetaData=[])
    {
        Assert($value)->nullOr()->scalar("var is type of " . gettype($value));

        $fieldType              = strtolower($pdoMetaData['native_type'] ??''?: '');
        if ( ! $fieldType )
        {
            $schema                 = $this->getColumns();
            if ( empty($schema) )
            {
                return $value;
            }
            $columns                = $this->getColumns(false);
            Assert($columns)->keyExists($field, "The property {$field} does not exist.");

            $fieldType              = $columns[$field] ?: null;
        }


        // Don't cast invalid values... only those that can be cast cleanly
        switch ( $fieldType )
        {
            case 'varchar':
            case 'var_string':
            case 'string':
            case 'text';
            case 'date':
            case 'datetime':
            case 'timestamp':
            case 'blob':

                return (string)$value;

            case 'int':
            case 'integer':
            case 'tinyint':
            case 'tiny':
            case 'long':
            case 'longlong':

                return (int)$value;

            case 'decimal':
            case 'float':
            case 'double':
            case 'newdecimal':

                return (float)$value;

            default:

                Assert(false)->true('Unknown type: ' . $fieldType);

                return $value;
        }
    }

    /**
     * @param string $field
     * @param mixed $value
     * @param bool|false $permissive
     * @return float|int|null|string
     */
    protected function fixType(string $field, $value, bool $permissive=false)
    {
        Assert($value)->nullOr()->scalar("var is type of " . gettype($value));
        $schema                 = $this->getColumns();
        if ( empty($schema) || ( ! array_key_exists($field, $schema) && $permissive ) )
        {
            return $value;
        }
        $columns                = $this->getColumns(false);
        Assert($columns)->keyExists($field, "The property {$field} does not exist.");

        $fieldType              = ! empty($columns[$field]) ? $columns[$field] : null;

        if ( is_null($value) )
        {
            return null;
        }
        // return on null, '' but not 0
        if ( ! is_numeric($value) && empty($value) )
        {
            return null;
        }
        // Don't cast invalid values... only those that can be cast cleanly
        switch ( $fieldType )
        {
            case 'varchar':
            case 'text';
            case 'date':
            case 'datetime':
            case 'timestamp':

                // return on null, '' but not 0
                return ! is_numeric($value) && empty($value) ? null : (string)$value;

            case 'int':

                if ( $field === 'id' || substr($field, -3) === '_id' )
                {
                    return $value ? (int)$value : null;
                }

                return ! is_numeric($value) ? null : (int)$value;

            case 'decimal':

                return ! is_numeric($value) ? null : (float)$value;

            default:

                return $value;
        }
    }

    /**
     * @param stdClass $record
     * @param string $type
     * @return stdClass
     */
    public function fixTypesToSentinel(stdClass $record, string $type='') : stdClass
    {
        foreach ( $this->rowMetaData as $column => $pdoMetaData )
        {
            if ( ! property_exists($record, $column) )
            {
                continue;
            }
            $record->{$column}      = $this->fixTypeToSentinel($column, $record->{$column}, $pdoMetaData);
        }
        // PDO might not be able to generate the meta data to sniff types
        if ( ! $this->rowMetaData )
        {
            foreach ( $this->getColumns(false) as $column => $fieldType )
            {
                if ( ! property_exists($record, $column) )
                {
                    continue;
                }
                $record->{$column}      = $this->fixTypeToSentinel($column, $record->{$column});
            }
        }

        unset($type);

        return $record;
    }

    /**
     * @param stdClass $record
     * @param string   $type
     * @return stdClass
     * @throws Exception
     */
    public function applyHandlers(stdClass $record, string $type='INSERT') : stdClass
    {
        $this->compileHandlers();
        $this->errors               = [];
        // Disable per field exceptions so we can capture all errors for the record
        $tmpExceptions              = $this->validationExceptions;
        $this->validationExceptions = false;
        foreach ( $this->handlers as $field => $fnValidator )
        {
            if ( ! property_exists($record, $field) )
            {
                // If the operation is an update it can be a partial update
                if ( $type === self::SAVE_UPDATE )
                {
                    continue;
                }
                $record->{$field}       = null;
            }
            $record->{$field}       = $this->applyHandler($field, $record->{$field}, $type, $record);
        }
        $this->validationExceptions = $tmpExceptions;
        if ( $this->validationExceptions && ! empty($this->errors) )
        {
            throw new ModelFailedValidationException("Validation of data failed", $this->getErrors(), 422);
        }

        return $record;
    }


    /**
     * @param stdClass $record
     * @param array    $fields
     * @param string   $type
     * @return bool
     */
    protected function uniqueCheck(stdClass $record, array $fields, string $type) : bool
    {
        if ( $type === self::SAVE_UPDATE )
        {
            $this->whereNot($this->primaryKey, $record->{$this->primaryKey});
        }
        foreach ( $fields as $field )
        {
            $this->where($field, $record->{$field});
        }

        return (int)$this->count() > 0;
    }

    /**
     * @param string $field
     * @param mixed $value
     * @param string $type
     * @param stdClass $record
     * @return null
     * @throws Exception
     */
    protected function applyHandler(string $field, $value, string $type='', stdClass $record=null)
    {
        $this->compileHandlers();
        $fnHandler              = ! empty($this->handlers[$field]) ? $this->handlers[$field] : null;
        if ( is_callable($fnHandler) )
        {
            try
            {
                $value                  = $fnHandler($field, $value, $type, $record);
            }
            catch( Exception $e )
            {
                $this->errors[$field][]     = $e->getMessage();
                if ( $this->validationExceptions && ! empty($this->errors) )
                {
                    throw new ModelFailedValidationException("Validation of data failed", $this->getErrors(), 422);
                }

                return null;
            }
        }

        return $value;
    }

    /**
     * @param string $start
     * @param string $end
     * @param string $hayStack
     * @return mixed
     */
    public static function between(string $start, string $end, string $hayStack) : string
    {
        return static::before($end, static::after($start, $hayStack));
    }

    /**
     * @param string     $needle
     * @param string     $hayStack
     * @param bool $returnOrigIfNeedleNotExists
     * @return mixed
     */
    public static function before(string $needle, string $hayStack, bool $returnOrigIfNeedleNotExists=false) : string
    {
        $result = mb_substr($hayStack, 0, mb_strpos($hayStack, $needle));
        if ( !$result && $returnOrigIfNeedleNotExists )
        {
            return $hayStack;
        }

        return $result;
    }

    /**
     * @param string     $needle
     * @param string     $hayStack
     * @param bool $returnOrigIfNeedleNotExists
     * @return string
     */
    public static function after(string $needle, string $hayStack, bool $returnOrigIfNeedleNotExists=false) : string
    {
        if ( ! is_bool(mb_strpos($hayStack, $needle)) )
        {
            return mb_substr($hayStack, mb_strpos($hayStack, $needle) + mb_strlen($needle));
        }

        return $returnOrigIfNeedleNotExists ? $hayStack : '';
    }

    /**
     * @return int
     */
    public function getUserId()
    {
        return 0;
    }

    /**
     * @param string $entity
     * @param int $id
     * @return int
     */
    public function getMaskByResourceAndId(string $entity, int $id) : int

The parameter $entity is not used and could be removed.

The parameter $id is not used and could be removed.

    {
        return 31;
    }

    /**
     * @param string|int|null $time
     * @return string
     */
    public static function date($time=null) : string
    {
        return date('Y-m-d', static::getTime($time));
    }

    /**
     * @param string|int|null $time
     * @return string
     */
    public static function dateTime($time=null) : string
    {
        return date('Y-m-d H:i:s', static::getTime($time));
    }

    /**
     * @param string|int|null $time
     * @return string
     */
    public static function atom($time=null) : string
    {
        return date('Y-m-d\TH:i:sP', static::getTime($time));
    }

    /**
     * @param string|int|null $time
     * @return int
     */
    public static function getTime($time=null) : int
    {
        if ( ! $time )
        {
            return time();
        }
        if ( is_int($time) )
        {
            return $time;
        }

        return strtotime($time);
    }

    /**
     * @param int $id
     * @param int $cacheTtl
     * @return string
     */
    public function getCodeById(int $id, int $cacheTtl=self::ONE_DAY) : string
    {
        Assert($id)->id();
        $code   = $this->defaultFilters()->fetchStr($this->getDisplayColumn(), $id, $cacheTtl);
        Assert($code)->notEmpty();

        return $code;
    }

    /**
     * @param array $authUserRoles
     * @param int   $authUserId
     * @return FluentPdoModel
     */
    public function applyRoleFilter(array $authUserRoles, int $authUserId) : FluentPdoModel

The parameter $authUserRoles is not used and could be removed.

The parameter $authUserId is not used and could be removed.

    {
        return $this;
    }

    /**
     * @param int    $id
     * @param string[] $authUserRoles
     * @param int    $authUserId
     * @return bool
     */
    public function canAccessIdWithRole(int $id, array $authUserRoles, int $authUserId) : bool

The parameter $id is not used and could be removed.

The parameter $authUserRoles is not used and could be removed.

The parameter $authUserId is not used and could be removed.

    {
        return true;
    }
}