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; }