/**
* Builds and returns the query string
*
* @param mixed $conn Database connection to use
* @return QueryStatement
*/
function getQuery(PDO $conn = null)
{
if (null === $conn && class_exists('DBManager')) {
$conn = DBManager::getConnection();
}
// the QueryStatement for the Query
$stmnt = new QueryStatement($conn);
// the string $statement will use
$qry_s = '';
$action = $this->_action;
switch ($action) {
default:
case self::ACTION_COUNT:
case self::ACTION_SELECT:
$columns_stmnt = $this->getColumnsClause($conn);
$stmnt->addIdentifiers($columns_stmnt->identifiers);
$stmnt->addParams($columns_stmnt->params);
$qry_s .= 'SELECT ' . $columns_stmnt->string . "\nFROM ";
break;
case self::ACTION_DELETE:
$qry_s .= "DELETE\nFROM ";
break;
case self::ACTION_UPDATE:
$qry_s .= "UPDATE\n";
break;
}
$table_stmnt = $this->getTablesClause($conn);
$stmnt->addIdentifiers($table_stmnt->identifiers);
$stmnt->addParams($table_stmnt->params);
$qry_s .= $table_stmnt->string;
if ($this->_joins) {
foreach ($this->_joins as $join) {
$join_stmnt = $join->getQueryStatement($conn);
$qry_s .= "\n\t" . $join_stmnt->string;
$stmnt->addParams($join_stmnt->params);
$stmnt->addIdentifiers($join_stmnt->identifiers);
}
}
if (self::ACTION_UPDATE === $action) {
if (empty($this->_updateColumnValues)) {
throw new RuntimeException('Unable to build UPDATE query without update column values');
}
$column_updates = array();
foreach ($this->_updateColumnValues as $column_name => &$column_value) {
$column_updates[] = QueryStatement::IDENTIFIER . '=' . QueryStatement::PARAM;
$stmnt->addIdentifier($column_name);
$stmnt->addParam($column_value);
}
$qry_s .= "\nSET " . implode(',', $column_updates);
}
$where_stmnt = $this->getWhereClause();
if (null !== $where_stmnt && $where_stmnt->string !== '') {
$qry_s .= "\nWHERE " . $where_stmnt->string;
$stmnt->addParams($where_stmnt->params);
$stmnt->addIdentifiers($where_stmnt->identifiers);
}
if ($this->_groups) {
$clause = $this->getGroupByClause();
$stmnt->addIdentifiers($clause->identifiers);
$stmnt->addParams($clause->params);
$qry_s .= $clause->string;
}
if (null !== $this->getHaving()) {
$having_stmnt = $this->getHaving()->getQueryStatement();
if (null !== $having_stmnt) {
$qry_s .= "\nHAVING " . $having_stmnt->string;
$stmnt->addParams($having_stmnt->params);
$stmnt->addIdentifiers($having_stmnt->identifiers);
}
}
if ($action !== self::ACTION_COUNT && $this->_orders) {
$clause = $this->getOrderByClause();
$stmnt->addIdentifiers($clause->identifiers);
$stmnt->addParams($clause->params);
$qry_s .= $clause->string;
}
if (null !== $this->_limit) {
if ($conn) {
if (class_exists('DBMSSQL') && $conn instanceof DBMSSQL) {
$qry_s = QueryStatement::embedIdentifiers($qry_s, $stmnt->getIdentifiers(), $conn);
$stmnt->setIdentifiers(array());
}
$conn->applyLimit($qry_s, $this->_offset, $this->_limit);
} else {
$qry_s .= "\nLIMIT " . ($this->_offset ? $this->_offset . ', ' : '') . $this->_limit;
}
}
if (self::ACTION_COUNT === $action && $this->needsComplexCount()) {
$qry_s = "SELECT count(0)\nFROM ({$qry_s}) a";
}
$stmnt->string = $qry_s;
return $stmnt;
}
/**
* @return QueryStatement
*/
private static function processCondition($left = null, $right = null, $operator = Query::EQUAL, $quote = null)
{
if ($left instanceof QueryStatement && 1 === func_num_args()) {
return $left;
}
$statement = new QueryStatement();
// Left can be a Condition
if ($left instanceof self) {
$clause_statement = $left->getQueryStatement();
if (null === $clause_statement) {
return null;
}
$clause_statement->string = '(' . $clause_statement->string . ')';
return $clause_statement;
}
if (null === $quote) {
// You can skip $operator and specify $quote with parameter 3
if (is_int($operator)) {
$quote = $operator;
$operator = Query::EQUAL;
} else {
$quote = self::QUOTE_RIGHT;
}
}
if (Query::BEGINS_WITH === $operator) {
$right .= '%';
$operator = Query::LIKE;
} elseif (Query::ENDS_WITH === $operator) {
$right = '%' . $right;
$operator = Query::LIKE;
} elseif (Query::CONTAINS === $operator) {
$right = '%' . $right . '%';
$operator = Query::LIKE;
}
// Escape $left
if ($quote === self::QUOTE_LEFT || $quote === self::QUOTE_BOTH) {
$statement->addParam($left);
$left = QueryStatement::PARAM;
} else {
$statement->addIdentifier($left);
$left = QueryStatement::IDENTIFIER;
}
$is_query = $right instanceof Query;
$is_array = false === $is_query && is_array($right);
if ($is_array || $is_query) {
if (false === $is_query || 1 !== $right->getLimit()) {
// Convert any sort of equality operator to something suitable for arrays
switch ($operator) {
// Various forms of equal
case Query::IN:
break;
case Query::EQUAL:
$operator = Query::IN;
break;
case Query::BETWEEN:
break;
// Various forms of not equal
// Various forms of not equal
case Query::NOT_IN:
break;
case Query::NOT_EQUAL:
case Query::ALT_NOT_EQUAL:
$operator = Query::NOT_IN;
break;
default:
throw new Exception('Operator "' . $operator . '" cannot be used for comparing an array.');
}
}
// Right can be a Query, if you're trying to nest queries, like "WHERE MyColumn = (SELECT OtherColumn From MyTable LIMIT 1)"
if ($is_query) {
if (!$right->getTable()) {
throw new Exception('right does not have a table, so it cannot be nested.');
}
$clause_statement = $right->getQuery();
if (null === $clause_statement) {
return null;
}
$right = '(' . $clause_statement->string . ')';
$statement->addParams($clause_statement->params);
$statement->addIdentifiers($clause_statement->identifiers);
if ($quote !== self::QUOTE_LEFT) {
$quote = self::QUOTE_NONE;
}
} elseif ($is_array) {
$array_len = count($right);
// BETWEEN
if (2 === $array_len && $operator === Query::BETWEEN) {
$statement->string = $left . ' ' . $operator . ' ' . QueryStatement::PARAM . ' AND ' . QueryStatement::PARAM;
$statement->addParams($right);
return $statement;
} elseif (0 === $array_len) {
// Handle empty arrays
if ($operator === Query::IN) {
$statement->string = '(0 = 1)';
return $statement;
} elseif ($operator === Query::NOT_IN) {
return null;
}
} elseif ($quote === self::QUOTE_RIGHT || $quote === self::QUOTE_BOTH) {
$statement->addParams($right);
$r_string = '(';
for ($x = 0; $x < $array_len; ++$x) {
if (0 < $x) {
$r_string .= ',';
}
$r_string .= QueryStatement::PARAM;
}
$right = $r_string . ')';
}
}
} else {
if (null === $right) {
if ($operator === Query::NOT_EQUAL || $operator === Query::ALT_NOT_EQUAL) {
// IS NOT NULL
$operator = Query::IS_NOT_NULL;
} elseif ($operator === Query::EQUAL) {
// IS NULL
$operator = Query::IS_NULL;
}
}
if ($operator === Query::IS_NULL || $operator === Query::IS_NOT_NULL) {
$right = null;
} elseif ($quote === self::QUOTE_RIGHT || $quote == self::QUOTE_BOTH) {
$statement->addParam($right);
$right = QueryStatement::PARAM;
}
}
if (!in_array($operator, Query::$operators, true)) {
throw new InvalidArgumentException('"' . $operator . '" is not a valid SQL operator.');
}
$statement->string = $left . ' ' . $operator . ' ' . $right;
return $statement;
}
