public static function getAssociations(ezcDbHandler $db, $url) { $options = new ezcAuthenticationOpenidDbStoreOptions(); $table = $options->tableAssociations; $query = new ezcQuerySelect($db); $e = $query->expr; $query->select('*')->from($db->quoteIdentifier($table['name']))->where($e->eq($db->quoteIdentifier($table['fields']['url']), $query->bindValue($url))); $query = $query->prepare(); $query->execute(); $rows = $query->fetchAll(); if (count($rows) > 0) { $rows = $rows[0]; $data = $rows[$table['fields']['association']]; return $data; } }
static function execOptionsQuery(ezcQuerySelect $query, $keyName, $valueName, array $options = array()) { $defaultOptions = array('skip_empty' => false, 'allow_empty' => false, 'empty_text' => _('-- Selezionare --')); $opt = array_merge($defaultOptions, $options); $stmt = $query->prepare(); $stmt->execute(); $optionsList = array(); if ($opt['allow_empty']) { $optionsList[''] = $opt['empty_text']; } while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { if ($opt['skip_empty'] !== true || trim($row[$valueName]) != '') { $optionsList[$row[$keyName]] = $row[$valueName]; } } return $optionsList; }
/** * Returns the SQL string for the subselect. * * Example: * <code> * <?php * $subSelect = $q->subSelect(); * $subSelect->select( name )->from( 'table2' ); * $q->select( '*' ) * ->from( 'table1' ) * ->where ( $q->expr->eq( 'name', $subSelect ) ); * $stmt = $q->prepare(); * $stmt->execute(); * ?> * </code> * * @return string */ public function getQuery() { return '( ' . parent::getQuery() . ' )'; }
/** * Resets the query object for reuse. * * @return void */ public function reset() { parent::reset(); $this->fromTables = array(); $this->rightJoins = array(null); }
/** * Returns the SQL to check if a value is one in a set of * given values. * * in() accepts an arbitrary number of parameters. The first parameter * must always specify the value that should be matched against. Successive * parameters must contain a logical expression or an array with logical * expressions. These expressions will be matched against the first * parameter. * * Example: * <code> * $q->select( '*' )->from( 'table' ) * ->where( $q->expr->in( 'id', 1, 2, 3 ) ); * </code> * * Oracle limits the number of values in a single IN() to 1000. This * implementation creates a list of combined IN() expressions to bypass * this limitation. * * @throws ezcQueryVariableParameterException if called with less than two * parameters. * @throws ezcQueryInvalidParameterException if the 2nd parameter is an * empty array. * @param string|array(string) values that will be matched against $column * @return string logical expression */ public function in($column) { $args = func_get_args(); if (count($args) < 2) { throw new ezcQueryVariableParameterException('in', count($args), 2); } if (is_array($args[1]) && count($args[1]) == 0) { throw new ezcQueryInvalidParameterException('in', 2, 'empty array', 'non-empty array'); } $values = ezcQuerySelect::arrayFlatten(array_slice($args, 1)); $values = $this->getIdentifiers($values); $column = $this->getIdentifier($column); if (count($values) == 0) { throw new ezcQueryVariableParameterException('in', count($args), 2); } if ($this->quoteValues) { foreach ($values as $key => $value) { switch (true) { case is_int($value): case is_float($value): case $value instanceof ezcQuerySubSelect: $values[$key] = (string) $value; break; default: $values[$key] = $this->db->quote($value); } } } if (count($values) <= 1000) { return "{$column} IN ( " . join(', ', $values) . ' )'; } else { $expression = '( '; do { $bunch = array_slice($values, 0, 1000); $values = array_slice($values, 1000); $expression .= "{$column} IN ( " . join(', ', $bunch) . ' ) OR '; } while (count($values) > 1000); $expression .= "{$column} IN ( " . join(', ', $values) . ' ) )'; return $expression; } }
/** * Handles preparing query. * * Overrides ezcQuery->prepare() * * Adds "FROM dual" to the select if no FROM clause specified * i.e. fixes queries like "SELECT 1+1" to work in Oracle. * * @return PDOStatement */ public function prepare() { if ($this->fromString == null || $this->fromString == '') { $this->from($this->getDummyTableName()); } return parent::prepare(); }
/** * Creates a simple JOIN to fetch the objects defined by $relation. * * Creates a simple LEFT JOIN using the aliases defined in $relation and * the $srcTableAlias, to fetch all objects defined by $relation, which are * related to the source object, fetched by $srcTableAlias. * * @param ezcQuerySelect $q * @param string $srcTableAlias * @param string $dstTableAlias * @param ezcPersistentRelationFindDefinition $relation */ protected function createSimpleJoin(ezcQuerySelect $q, $srcTableAlias, $dstTableAlias, ezcPersistentRelationFindDefinition $relation) { $relationDefinition = $relation->relationDefinition; $first = true; $joinCond = null; foreach ($relationDefinition->columnMap as $mapping) { $srcColumn = $this->getColumnName($srcTableAlias, $mapping->sourceColumn); $destColumn = $this->getColumnName($dstTableAlias, $mapping->destinationColumn); if ($first) { $joinCond = $q->expr->eq($srcColumn, $destColumn); $first = false; } else { $joinCond = $q->expr->and($joinCond, $q->expr->eq($srcColumn, $destColumn)); } } $q->leftJoin($q->alias($this->db->quoteIdentifier($relationDefinition->destinationTable), $this->db->quoteIdentifier($dstTableAlias)), $joinCond); }
/** * Returns the unserialized association linked to the OpenID provider URL. * * Returns false if the association could not be retrieved or if it expired. * * @param string $url The URL of the OpenID provider * @return ezcAuthenticationOpenidAssociation */ public function getAssociation($url) { $table = $this->options->tableAssociations; $query = new ezcQuerySelect($this->instance); $e = $query->expr; $query->select('*')->from($this->instance->quoteIdentifier($table['name']))->where($e->eq($this->instance->quoteIdentifier($table['fields']['url']), $query->bindValue($url))); $query = $query->prepare(); $query->execute(); $rows = $query->fetchAll(); if (count($rows) > 0) { $rows = $rows[0]; $data = unserialize($rows[$table['fields']['association']]); return $data; } // no association was found for $url return false; }
public function testSubSubSelect() { $name = 'IBM'; $name2 = 'company'; $q = new ezcQuerySelect(ezcDbInstance::get()); $q->expr->setValuesQuoting(false); // subselect $q2 = $q->subSelect(); // sub subselect $q3 = $q2->subSelect(); $q3->expr->setValuesQuoting(false); $q3->select('*')->from('query_test2')->where($q3->expr->in('company', 'IBM', 'eZ systems')); // bind values $q2->select('company')->from('query_test')->where($q2->expr->eq('company', $q2->bindParam($name)), ' id > 2 '); $q->select('*')->from('query_test')->where(' id >= 1 ', $q->expr->in('company', $q2->getQuery()))->orderBy('id'); $stmt = $q->prepare(); $stmt->execute(); $result = $stmt->fetchAll(); $this->assertEquals('IBM', $result[0]['company']); $this->assertEquals('Norway', $result[0]['section']); $this->assertEquals('IBM', $result[1]['company']); $this->assertEquals('Germany', $result[1]['section']); }
/** * Returns a series of strings concatinated * * concat() accepts an arbitrary number of parameters. Each parameter * must contain an expression or an array with expressions. * * @param string|array(string) $... strings that will be concatinated. */ public function concat() { $args = func_get_args(); $cols = ezcQuerySelect::arrayFlatten($args); if (count($cols) < 1) { throw new ezcQueryVariableParameterException('concat', count($args), 1); } $cols = $this->getIdentifiers($cols); return "CONCAT( " . join(', ', $cols) . ' )'; }
/** * Helper for {@see listVersions()} and {@see listVersionsForUser()} that filters duplicates * that are the result of the cartesian product performed by createVersionInfoFindQuery() * * @param \ezcQuerySelect $query * @return string[][] */ private function listVersionsHelper($query) { $query->orderBy($this->dbHandler->quoteColumn('id', 'ezcontentobject_version')); $statement = $query->prepare(); $statement->execute(); $results = array(); $previousId = null; foreach ($statement->fetchAll(\PDO::FETCH_ASSOC) as $row) { if ($row["ezcontentobject_version_id"] == $previousId) { continue; } $previousId = $row["ezcontentobject_version_id"]; $results[] = $row; } return $results; }
/** * Transforms the query from the parent to provide LIMIT functionality. * * Note: doesn't work exactly like the MySQL equivalent; it will always return * $limit rows even if $offset + $limit exceeds the total number of rows. * * @throws ezcQueryInvalidException if offset is used and orderBy is not. * @return string */ public function getQuery() { $query = parent::getQuery(); if ($this->hasLimit) { if ($this->offset) { if (!$this->orderString) { // Uh ow. We need some columns to sort in the oposite order to make this work throw new ezcQueryInvalidException("LIMIT workaround for MS SQL", "orderBy() was not called before getQuery()."); } return 'SELECT * FROM ( SELECT TOP ' . $this->limit . ' * FROM ( ' . self::top($this->offset + $this->limit, $query) . ' ) AS ezcDummyTable1 ' . $this->invertedOrderString . ' ) AS ezcDummyTable2 ' . $this->orderString; } return self::top($this->limit, $query); } return $query; }
<?php require_once __DIR__ . '/tutorial_example_01.php'; $name = 'IBM'; $q = new ezcQuerySelect(ezcDbInstance::get()); // Creating subselect object $q2 = $q->subSelect(); // $q2 will build the subquery "SELECT company FROM query_test WHERE // company = :ezcValue1 AND id > 2". This query will be used inside the SQL for // $q. $q2->select('company')->from('query_test')->where($q2->expr->eq('company', $q2->bindParam($name)), 'id > 2'); // $q the resulting query. It produces the following SQL: // SELECT * FROM query_test // WHERE id >= 1 AND // company IN ( ( // SELECT company FROM query_test // WHERE company = :ezcValue1 AND id > 2 // ) ) $q->select('*')->from('query_test')->where(' id >= 1 ', $q->expr->in('company', $q2)); $stmt = $q->prepare(); echo $stmt->queryString; //$stmt->execute();
/** * Runs the filter and returns a status code when finished. * * @param ezcAuthenticationPasswordCredentials $credentials Authentication credentials * @return int */ public function run($credentials) { $db = $this->database; // see if username exists $query = new ezcQuerySelect($db->instance); $e = $query->expr; $query->select('COUNT( ' . $db->instance->quoteIdentifier($db->fields[0]) . ' )')->from($db->instance->quoteIdentifier($db->table))->where($e->eq($db->instance->quoteIdentifier($db->fields[0]), $query->bindValue($credentials->id))); $rows = $query->prepare(); $rows->execute(); $count = (int) $rows->fetchColumn(0); if ($count === 0) { return self::STATUS_USERNAME_INCORRECT; } $rows->closeCursor(); if (count($this->requestedData) > 0) { // fetch extra data from the database $query = new ezcQuerySelect($db->instance); $e = $query->expr; $params = array(); foreach ($this->requestedData as $param) { $params[] = $db->instance->quoteIdentifier($param); } $query->select(implode(', ', $params))->from($db->instance->quoteIdentifier($db->table))->where($e->lAnd($e->eq($db->instance->quoteIdentifier($db->fields[0]), $query->bindValue($credentials->id)))); $rows = $query->prepare(); $rows->execute(); $data = $rows->fetchAll(); $data = $data[0]; foreach ($this->requestedData as $attribute) { $this->data[$attribute] = array($data[$attribute]); } } return self::STATUS_OK; }