core/database/QueryMaker.php
<?php
namespace CodeJetter\core\database;
use CodeJetter\core\utility\MysqlUtility;
/**
* Class QueryMaker.
*/
class QueryMaker
{
private $tables;
private $validComparisonOperators = ['LIKE', 'NOT LIKE', '=', '!=', '<>', '<', '<=', '>', '>=', '<=>', 'IS NOT',
'IS', 'IS NOT NULL', 'IS NULL', 'IN', 'NOT IN', ];
private $validLogicalOperators = ['AND', 'OR', 'XOR', 'NOT'];
/**
* QueryMaker constructor.
*
* @param null $tables
*/
public function __construct($tables = null)
{
if ($tables !== null) {
if (is_array($tables)) {
$this->setTables($tables);
} else {
// $tables is string, treat it as a table
$this->addTable(
$tables,
[
'name' => $tables,
]
);
}
}
}
/**
* @param array $criteria
* @param $fromColumns
* @param $order
* @param $start
* @param $limit
*
* @throws \Exception
*
* @return string
*/
public function selectQuery(array $criteria = [], $fromColumns = '*', $order = '', $start = 0, $limit = 0)
{
// append from columns
if (empty($fromColumns) && $fromColumns !== null) {
// from all
$fromColumns = '*';
} elseif (is_array($fromColumns)) {
$fromColumns = implode(', ', $fromColumns);
}
$query = $this->getSelectFromTables($fromColumns);
// append where clause - criteria
$query .= $this->where($criteria);
// append order by if it is specified
$query .= $this->orderBy($order);
// append start and limit if they are specified
$query .= $this->startLimit($start, $limit);
$query .= ';';
return $query;
}
/**
* @param array $criteria
* @param $fieldsValues
* @param $start
* @param $limit
*
* @throws \Exception
*
* @return string
*/
public function updateQuery(array $criteria, $fieldsValues, $start, $limit)
{
if (empty($fieldsValues)) {
throw new \Exception('fieldsValues cannot be empty in updateQuery function');
}
$query = "UPDATE {$this->getTable()['name']} SET ";
// point to end of the array
end($fieldsValues);
// fetch key of the last element of the array.
$LastFieldKey = key($fieldsValues);
foreach ($fieldsValues as $fieldValueKey => $fieldValue) {
// Do not append comma if it is the last element
$comma = $LastFieldKey === $fieldValueKey ? '' : ', ';
// This is to avoid converting null to an empty string
// if ($fieldValue['value'] === null) {
// $fieldValue['value'] = 'null';
// }
if (isset($fieldValue['bind']) && $fieldValue['bind'] === false) {
$query .= "{$fieldValue['column']} = {$fieldValue['value']}{$comma}";
} else {
$placeholder = $this->preparePlaceholder($fieldValue['column']);
$query .= "{$fieldValue['column']} = :{$placeholder}{$comma}";
}
}
$query = rtrim($query);
$query .= $this->where($criteria);
$query .= $this->startLimit($start, $limit);
$query .= ';';
return $query;
}
/**
* @param array $fieldsValues
*
* @throws \Exception
*
* @return string
*/
public function insertQuery(array $fieldsValues)
{
if (empty($fieldsValues)) {
throw new \Exception('fieldsValues cannot be empty in insertQuery function');
}
$query = "INSERT INTO {$this->getTable()['name']}";
$columns = [];
$parameters = [];
foreach ($fieldsValues as $fieldValueKey => $fieldValue) {
// This is to avoid converting null to an empty string
// if ($fieldValue['value'] === null) {
// $fieldValue['value'] = 'null';
// }
$columns[] = "{$fieldValue['column']}";
if (isset($fieldValue['bind']) && $fieldValue['bind'] === false) {
$parameters[] = "{$fieldValue['value']}";
} else {
$placeholder = $this->preparePlaceholder($fieldValue['column']);
$parameters[] = ":{$placeholder}";
}
}
if (!empty($columns) && !empty($parameters)) {
$query .= ' ('.implode(',', $columns).') VALUES ('.implode(',', $parameters).');';
}
return $query;
}
/**
* @param array $fieldsValuesCollection
*
* @throws \Exception
*
* @return string
*/
public function batchInsertQuery(array $fieldsValuesCollection)
{
if (empty($fieldsValuesCollection)) {
throw new \Exception('fieldsValues cannot be empty in insertQuery function');
}
$query = "INSERT INTO {$this->getTable()['name']}";
$parametersArray = [];
foreach ($fieldsValuesCollection as $key => $fieldsValues) {
// reset parameters
$columns = [];
$parameters = [];
foreach ($fieldsValues as $fieldValueKey => $fieldValue) {
// This is to avoid converting null to an empty string
// if ($fieldValue['value'] === null) {
// $fieldValue['value'] = 'null';
// }
$columns[] = "{$fieldValue['column']}";
if (isset($fieldValue['bind']) && $fieldValue['bind'] === false) {
$parameters[] = "{$fieldValue['value']}";
} else {
$placeholder = $this->preparePlaceholder($fieldValue['column']);
$parameters[] = ":{$placeholder}{$key}";
}
}
$parameters = implode(',', $parameters);
$parametersArray[] = "({$parameters})";
}
if (!empty($columns) && !empty($parameters)) {
$query .= ' ('.implode(',', $columns).') VALUES '.implode(',', $parametersArray).';';
}
return $query;
}
/**
* @param array $criteria
* @param $start
* @param $limit
*
* @throws \Exception
*
* @return string
*/
public function deleteQuery(array $criteria, $start, $limit)
{
$query = "DELETE FROM {$this->getTable()['name']}";
$query .= $this->where($criteria);
$query .= $this->startLimit($start, $limit);
$query .= ';';
return $query;
}
/**
* @param array $criteria
*
* @throws \Exception
*
* @return string
*/
public function countQuery(array $criteria)
{
$query = $this->getSelectFromTables('COUNT(*)');
$query .= $this->where($criteria);
$query .= ';';
return $query;
}
/**
* @param array $criteria
*
* @throws \Exception
*
* @return string
*/
private function where(array $criteria)
{
if (!empty($criteria)) {
$where = ' WHERE ';
$counter = 1;
$nested = [];
foreach ($criteria as $aCriteria) {
if (!empty($aCriteria['nested'])) {
// check placeholder is not already added
$before = isset($aCriteria['nested']['before']) ? "{$aCriteria['nested']['before']} " : '';
$after = isset($aCriteria['nested']['after']) ? " {$aCriteria['nested']['after']}" : '';
$placeholder = $before.'({'.$aCriteria['nested']['key'].'})'.$after.' ';
$placeholderExists = strpos($where, $placeholder);
// if placeholder does not exist append it to where clause
if ($placeholderExists === false) {
// add placeholder
$where .= $placeholder;
}
}
if (empty($aCriteria['column'])) {
throw new \Exception('Column name cannot be empty');
}
if (empty($aCriteria['operator'])) {
// If operator is not specified, consider '=' as the operator
$aCriteria['operator'] = '=';
}
if (!in_array($aCriteria['operator'], $this->validComparisonOperators)) {
throw new \Exception("'{$aCriteria['operator']}' is not a valid comparison operator");
}
if ($counter === 1 || !empty($before) || !empty($after)) {
$logicalOperator = '';
} elseif (empty($aCriteria['logicalOperator'])) {
// counter is greater than 1 and $aCriteria['logicalOperator'] is empty, consider 'AND' as default
$logicalOperator = 'AND ';
} else {
// counter is greater than 1 and $aCriteria['logicalOperator'] is NOT empty, validate it first
if (!in_array($aCriteria['logicalOperator'], $this->validLogicalOperators)) {
throw new \Exception("'{$aCriteria['logicalOperator']}' is not a valid logical operator");
}
$logicalOperator = "{$aCriteria['logicalOperator']} ";
}
$toBeAppended = "{$logicalOperator}{$aCriteria['column']} {$aCriteria['operator']} ";
// Form the query for IN or NOT IN
if ($aCriteria['operator'] === 'IN' || $aCriteria['operator'] === 'NOT IN') {
if (is_array($aCriteria['value'])) {
// value is array
$newParameters = [];
foreach ($aCriteria['value'] as $key => $value) {
$placeholder = $this->preparePlaceholder($aCriteria['column']);
$newParameters[] = ":{$placeholder}{$counter}{$key}";
}
$toBeAppended .= '('.implode(',', $newParameters).') ';
} else {
// value is not array
$placeholder = $this->preparePlaceholder($aCriteria['column']);
$toBeAppended .= "(:{$placeholder}{$counter}) ";
}
} else {
// IS NULL and IS NOT NULL do NOT need a parameter
if ($aCriteria['operator'] !== 'IS NULL' && $aCriteria['operator'] !== 'IS NOT NULL') {
$placeholder = $this->preparePlaceholder($aCriteria['column']);
$toBeAppended .= ":{$placeholder}{$counter} ";
}
}
if (empty($aCriteria['nested'])) {
$where .= $toBeAppended;
} else {
// append it to the key in $nested
if (!isset($nested[$aCriteria['nested']['key']])) {
$nested[$aCriteria['nested']['key']] = $toBeAppended;
} else {
$nested[$aCriteria['nested']['key']] .= $toBeAppended;
}
}
$counter++;
}
if (!empty($nested)) {
foreach ($nested as $aNestedKey => $aNestedValue) {
// find and replace $aNestedKey placeholder in where clause with the nested query
$where = str_replace('{'.$aNestedKey.'}', rtrim($aNestedValue), $where);
}
}
return rtrim($where);
} else {
return '';
}
}
/**
* @param $field
*
* @return string
*/
private function orderBy($field)
{
if (!empty($field)) {
return " ORDER BY {$field}";
}
}
/**
* @param $field
*
* @return string
*/
private function groupBy($field)
{
if (!empty($field)) {
return " GROUP BY {$field}";
}
}
/**
* @param int $start
* @param int $limit
*
* @return string
*/
private function startLimit($start = 0, $limit = 0)
{
if (!empty($start) && !empty($limit)) {
return ' LIMIT :start, :limit';
} elseif (!empty($limit)) {
return ' LIMIT :limit';
}
}
/**
* @param \PDOStatement $statement
* @param array $criteria
* @param int $start
* @param int $limit
* @param array $fieldsValues is used for update query
*
* @return \PDOStatement
*/
public function bindValues(\PDOStatement $statement, array $criteria, $start = 0, $limit = 0, array $fieldsValues = [])
{
// bind criteria values
$this->bindCriteria($statement, $criteria);
// bind field values
if (!empty($fieldsValues)) {
foreach ($fieldsValues as $fieldValue) {
if (isset($fieldValue['bind']) && $fieldValue['bind'] === false) {
continue;
}
// This is to avoid converting null to an empty string
// if ($fieldValue['value'] === null) {
// $fieldValue['value'] = 'null';
// }
// set the type to string if it is empty
if (empty($fieldValue['type'])) {
$fieldValue['type'] = \PDO::PARAM_STR;
}
$placeholder = $this->preparePlaceholder($fieldValue['column']);
$statement->bindValue(':'.$placeholder, $fieldValue['value'], $fieldValue['type']);
}
}
// bind start and limit if they are specified
if (!empty($start) && !empty($limit)) {
$statement->bindValue(':start', (int) $start, \PDO::PARAM_INT);
$statement->bindValue(':limit', (int) $limit, \PDO::PARAM_INT);
} elseif (!empty($limit)) {
$statement->bindValue(':limit', $limit, \PDO::PARAM_INT);
}
return $statement;
}
/**
* @param \PDOStatement $statement
* @param array $criteria
*
* @return \PDOStatement
*/
private function bindCriteria(\PDOStatement $statement, array $criteria)
{
// bind criteria values
if (!empty($criteria)) {
$counter = 0;
foreach ($criteria as $aCriteria) {
$counter++;
if (empty($aCriteria['operator'])) {
// If operator is not specified, consider = as the operator
$aCriteria['operator'] = '=';
}
if ($aCriteria['operator'] === 'IS NULL' || $aCriteria['operator'] === 'IS NOT NULL') {
continue;
}
$placeholder = $this->preparePlaceholder($aCriteria['column']);
if ($aCriteria['operator'] === 'IN' || $aCriteria['operator'] === 'NOT IN') {
if (is_array($aCriteria['value'])) {
// value is array
foreach ($aCriteria['value'] as $key => $value) {
// to override the automatic detection $aCriteria['type'] needs to be passed
$type = empty($aCriteria['type']) ? $this->detectParameterType($value) : $aCriteria['type'];
$statement->bindValue(':'.$placeholder.$counter.$key, $value, $type);
}
} else {
// value is not array
// to override the automatic detection $aCriteria['type'] needs to be passed
$type = empty($aCriteria['type']) ? $this->detectParameterType($aCriteria['value']) : $aCriteria['type'];
$statement->bindValue(':'.$placeholder.$counter, $aCriteria['value'], $type);
}
} else {
// set the type to string if it is empty
if (empty($aCriteria['type'])) {
$aCriteria['type'] = \PDO::PARAM_STR;
}
$statement->bindValue(':'.$placeholder.$counter, $aCriteria['value'], $aCriteria['type']);
}
}
}
return $statement;
}
/**
* @param \PDOStatement $statement
* @param array $criteria
* @param int $start
* @param int $limit
* @param array $fieldsValuesCollection
*
* @return \PDOStatement
*/
public function batchBindValues(
\PDOStatement $statement,
array $criteria,
$start = 0,
$limit = 0,
array $fieldsValuesCollection = []
) {
if (!empty($fieldsValuesCollection)) {
// bind criteria values
$this->bindCriteria($statement, $criteria);
foreach ($fieldsValuesCollection as $key => $fieldsValues) {
// bind field values
if (!empty($fieldsValues)) {
foreach ($fieldsValues as $fieldValue) {
if (isset($fieldValue['bind']) && $fieldValue['bind'] === false) {
continue;
}
// This is to avoid converting null to an empty string
// if ($fieldValue['value'] === null) {
// $fieldValue['value'] = 'null';
// }
// set the type to string if it is empty
if (empty($fieldValue['type'])) {
$fieldValue['type'] = \PDO::PARAM_STR;
}
$placeholder = $this->preparePlaceholder($fieldValue['column']);
$statement->bindValue(':'.$placeholder.$key, $fieldValue['value'], $fieldValue['type']);
}
}
}
// bind start and limit if they are specified
if (!empty($start) && !empty($limit)) {
$statement->bindValue(':start', (int) $start, \PDO::PARAM_INT);
$statement->bindValue(':limit', (int) $limit, \PDO::PARAM_INT);
} elseif (!empty($limit)) {
$statement->bindValue(':limit', $limit, \PDO::PARAM_INT);
}
return $statement;
}
}
/**
* @param null $tableAlias
*
* @throws \Exception
*
* @return mixed
*/
public function getTable($tableAlias = null)
{
$tables = $this->getTables();
if ($tableAlias === null) {
return array_shift($tables);
} else {
if (array_key_exists($tableAlias, $tables)) {
return $tables[$tableAlias];
} else {
throw new \Exception("Requested table does not exist for the alias: {$tableAlias}");
}
}
}
/**
* @param array $tables
*/
public function setTables(array $tables)
{
$this->tables = $tables;
}
/**
* @return array
*/
public function getTables()
{
return $this->tables;
}
/**
* @param null $fromColumns
*
* @throws \Exception
*
* @return string
*/
public function getSelectFromTables($fromColumns = null)
{
$joinedSelect = [];
$counter = 1;
$from = '';
if (empty($this->getTables())) {
throw new \Exception('Tables cannot be empty');
}
foreach ($this->getTables() as $tableAlias => $table) {
if ($counter === 1) {
$from .= "`{$table['name']}` AS `{$tableAlias}`";
} else {
$from .= " JOIN `{$table['name']}` AS `{$tableAlias}`";
if (empty($table['on']) || !is_array($table['on'])) {
throw new \Exception("join array must have 'on'");
}
$from .= ' ON '.implode(' = ', $table['on']);
}
if ($fromColumns === null) {
$columns = (new MysqlUtility())->getTableColumns($table['name']);
if (!empty($columns)) {
foreach ($columns as $column) {
$joinedSelect[] = "`{$tableAlias}`.`{$column}` AS `{$tableAlias}.{$column}`";
}
}
}
$counter++;
}
if ($fromColumns === null) {
$fromColumns = implode(', ', $joinedSelect);
} elseif (is_array($fromColumns)) {
$fromColumns = implode(', ', $fromColumns);
}
return "SELECT {$fromColumns} FROM {$from}";
}
/**
* @param $tableAlias
* @param $table
*/
public function addTable($tableAlias, $table)
{
$tables = $this->getTables();
$tables[$tableAlias] = $table;
$this->setTables($tables);
}
/**
* @param $value
*
* @return int
*/
private function detectParameterType($value)
{
return is_int($value) ? \PDO::PARAM_INT : \PDO::PARAM_STR;
}
/**
* PDO placeholders can only be: [a-zA-Z0-9_]+.
*
* @param $placeholder
*
* @return mixed
*/
private function preparePlaceholder($placeholder)
{
$placeholder = str_replace('`', '', $placeholder);
return preg_replace('/[^a-zA-Z0-9_]/', '_', $placeholder);
}
}