public function count(\Spot\Query $query, array $options = array())
{
$conditions = $this->statementConditions($query->conditions);
$binds = $this->statementBinds($query->params());
$sql = "\n SELECT COUNT(*) as count\n FROM " . $query->datasource . "\n " . ($conditions ? 'WHERE ' . $conditions : '') . "\n " . ($query->group ? 'GROUP BY ' . implode(', ', $query->group) : '');
// Unset any NULL values in binds (compared as "IS NULL" and "IS NOT NULL" in SQL instead)
if ($binds && count($binds) > 0) {
foreach ($binds as $field => $value) {
if (null === $value) {
unset($binds[$field]);
}
}
}
// Add query to log
\Spot\Log::addQuery($this, $sql, $binds);
$result = false;
try {
// Prepare count query
$stmt = $this->connection()->prepare($sql);
//if prepared, execute
if ($stmt && $stmt->execute($binds)) {
//the count is returned in the first column
$result = (int) $stmt->fetchColumn();
} else {
$result = false;
}
} catch (\PDOException $e) {
// Table does not exist
if ($e->getCode() == "42S02") {
throw new \Spot\Exception_Datasource_Missing("Table or datasource '" . $query->datasource . "' does not exist");
}
// Re-throw exception
throw $e;
}
return $result;
}
/**
* Build a select statement in SQL
* Can be overridden by adapters for custom syntax
*
* @todo Add support for JOINs
*/
public function read(\Spot\Query $query, array $options = array())
{
$isCount = false;
if (isset($options['SPOT_SQL_COUNT']) && $options['SPOT_SQL_COUNT'] === true) {
$isCount = true;
}
$conditions = $this->statementConditions($query->conditions);
$binds = $this->statementBinds($query->params());
$order = array();
if (!$isCount) {
if ($query->order) {
foreach ($query->order as $oField => $oSort) {
$order[] = $this->escapeField($oField) . " " . $oSort;
}
}
}
if ($query->having) {
$havingConditions = $this->statementConditions($query->having, count($binds) - count($query->having));
}
$fields = $this->statementFields($query->fields);
// Prepend COUNT(*) if count query
if ($isCount && !empty($fields)) {
// Remove field wildcard '*' because it will cause errors with COUNT
$aFields = explode(',', $fields);
$fields = implode(',', array_filter($aFields, function ($r) {
return trim($r) != '*';
}));
$fields = "COUNT(*) as count" . ($fields ? ', ' . $fields : '');
}
$sql = "\n SELECT " . $fields . "\n FROM " . $query->datasource . "\n " . ($conditions ? 'WHERE ' . $conditions : '') . "\n " . ($query->group ? 'GROUP BY ' . implode(', ', $query->group) : '') . "\n " . ($query->having ? 'HAVING' . $havingConditions : '') . "\n " . ($order ? 'ORDER BY ' . implode(', ', $order) : '') . "\n " . (!$isCount ? ($query->limit ? 'LIMIT ' . $query->limit : '') . " " . ($query->limit && $query->offset ? 'OFFSET ' . $query->offset : '') : '') . "\n ";
// Unset any NULL values in binds (compared as "IS NULL" and "IS NOT NULL" in SQL instead)
if ($binds && count($binds) > 0) {
foreach ($binds as $field => $value) {
if (null === $value) {
unset($binds[$field]);
}
}
}
// Add query to log
\Spot\Log::addQuery($this, $sql, $binds);
$result = false;
try {
// Prepare update query
$stmt = $this->connection()->prepare($sql);
if ($stmt) {
// Execute
if ($stmt->execute($binds)) {
if (isset($options['SPOT_SQL_COUNT'])) {
//the count is returned in the first column
$result = (int) $stmt->fetchColumn();
} else {
$result = $this->toCollection($query, $stmt);
}
} else {
$result = false;
}
} else {
$result = false;
}
} catch (\PDOException $e) {
// Table does not exist
if ($e->getCode() == "42S02") {
throw new \Spot\Exception_Datasource_Missing("Table or datasource '" . $query->datasource . "' does not exist");
}
// Re-throw exception
throw $e;
}
return $result;
}
/**
* Build a select statement in SQL
* Can be overridden by adapters for custom syntax
*
* @todo Add support for JOINs
*/
public function read(\Spot\Query $query, array $options = array())
{
$conditions = $this->statementConditions($query->conditions);
$binds = $this->statementBinds($query->params());
$order = array();
if ($query->order) {
foreach ($query->order as $oField => $oSort) {
$order[] = $oField . " " . $oSort;
}
}
$sql = "\n SELECT " . $this->statementFields($query->fields) . "\n FROM " . $query->datasource . "\n " . ($conditions ? 'WHERE ' . $conditions : '') . "\n " . ($query->group ? 'GROUP BY ' . implode(', ', $query->group) : '') . "\n " . ($order ? 'ORDER BY ' . implode(', ', $order) : '') . "\n " . ($query->limit ? 'LIMIT ' . $query->limit : '') . " " . ($query->limit && $query->offset ? 'OFFSET ' . $query->offset : '') . "\n ";
// Unset any NULL values in binds (compared as "IS NULL" and "IS NOT NULL" in SQL instead)
if ($binds && count($binds) > 0) {
foreach ($binds as $field => $value) {
if (null === $value) {
unset($binds[$field]);
}
}
}
// Add query to log
\Spot\Log::addQuery($this, $sql, $binds);
$result = false;
try {
// Prepare update query
$stmt = $this->connection()->prepare($sql);
if ($stmt) {
// Execute
if ($stmt->execute($binds)) {
$result = $this->toCollection($query, $stmt);
} else {
$result = false;
}
} else {
$result = false;
}
} catch (PDOException $e) {
// Table does not exist
if ($e->getCode() == "42S02") {
throw new \Spot\Exception_Datasource_Missing("Table or datasource '" . $query->datasource . "' does not exist");
}
throw $e;
}
return $result;
}
