This method provides a shortcut for PDOStatement::bindValue
when using prepared statements.
The parameter $value specifies the value that you want to bind. If
$placeholder is not provided bindValue() will automatically create a
placeholder for you. An automatic placeholder will be of the name
':dcValue1', ':dcValue2' etc.
For more information see {@link http://php.net/pdostatement-bindparam}
Example:
$value = 2;
$q->eq( 'id', $q->bindValue( $value ) );
$stmt = $q->executeQuery(); // executed with 'id = 2'
/**
* @param $tableName
* @param $conditions
*
* @return array
*
* @throws LookupError
*
* @since 1.1.0
*
* @author Eddilbert Macharia (http://eddmash.com) <*****@*****.**>
*/
public static function filters(QueryBuilder $queryBuilder, $conditions)
{
// default lookup is equal
$lookup = 'eq';
// we add the or conditions afterwards to avoid them being mistaken for "and" conditions when they come first
$or_combine = [];
$and_combine = [];
// create where clause from the conditions given
foreach ($conditions as $condition) {
foreach ($condition as $key => $value) {
$column = self::getLookupColumn($key);
$lookup = self::getLookUP($key);
$value = self::prepareValue($value, $lookup);
echo self::$lookuOptions[$lookup] . '<br>';
echo $queryBuilder->createNamedParameter($value) . '<br>';
echo $value . '<br>';
$lookupCondition = sprintf(self::$lookuOptions[$lookup], $queryBuilder->createNamedParameter($value));
$queryString = sprintf('%s %s', $column, $lookupCondition);
if (self::combine($key) === self::$or) {
$queryBuilder->orWhere($queryString);
} else {
$queryBuilder->andWhere($queryString);
}
}
}
}
/**
* @deprecated
*/
public function filter($field, $value, $comparison = '=')
{
if ($field == false) {
$this->query->andWhere($value);
// ugh
} else {
$this->query->andWhere(implode(' ', array($field, $comparison, $this->query->createNamedParameter($value))));
}
}
public function where($column, $operator = null, $value = null, $boolean = 'and')
{
$args = func_get_args();
if (count($args) == 1) {
parent::where($args[0]);
return $this;
}
$column = $args[0];
$boolean = 'and';
$operator = '=';
if (count($args) == 2) {
$value = $args[1];
}
if (count($args) == 3) {
$operator = $args[1];
$value = $args[2];
}
if (count($args) == 4) {
$operator = $args[1];
$value = $args[2];
$boolean = $args[3];
}
if (is_array($value)) {
$operator = $operator == '=' ? 'in' : 'notIn';
$where_clause = $this->expr()->{$operator}($column, parent::createNamedParameter($value, \Doctrine\DBAL\Connection::PARAM_STR_ARRAY));
} else {
$where_clause = $column . $operator . parent::createNamedParameter($value);
}
if ($boolean == 'and') {
parent::andWhere($where_clause);
} elseif ($boolean == 'or') {
parent::orWhere($where_clause);
}
return $this;
}
/**
* Build an expression to be included in a Doctrine QueryBuilder instance.
*
* This method will build an expression, adding any parameters to the query,
* but the caller is responsible for adding the expression to the query, for
* example as where() parameter. This allows the caller to handle all context,
* such as parenthetical groupings.
*
* @param QueryBuilder $qb query builder instance
*
* @return string expression
*/
public function buildExpressionQb(QueryBuilder $qb)
{
$eb = $qb->expr();
$column = (empty($this->prefix) ? "" : $this->prefix . '.') . $this->column;
// this should be done using portability functions
if (!empty($this->function)) {
$column = sprintf($this->function, $column);
}
$value = trim($this->value);
$operator = strtolower($this->operator);
$expr = '';
// handle special case of value
if (in_array($operator, array('is null', 'is not null', 'in', 'not in'))) {
switch ($operator) {
case 'is null':
$expr = $eb->isNull($column);
break;
case 'is not null':
$expr = $eb->isNotNull($column);
break;
case 'in':
if (!empty($value) && $value !== '()') {
$expr = $column . ' IN ' . $value;
} else {
// odd case of a null set - this won't match anything
$expr = $eb->neq($column, $column);
}
break;
case 'not in':
if (!empty($value) && $value !== '()') {
$expr = $column . ' NOT IN ' . $value;
}
break;
}
} elseif (!empty($column)) {
// no value is a nop (bug: this should be a valid value)
$columnValue = $qb->createNamedParameter($value);
switch ($operator) {
case '=':
case 'eq':
$expr = $eb->eq($column, $columnValue);
break;
case '!=':
case '<>':
case 'neq':
$expr = $eb->neq($column, $columnValue);
break;
case '<':
case 'lt':
$expr = $eb->lt($column, $columnValue);
break;
case '<=':
case 'lte':
$expr = $eb->lte($column, $columnValue);
break;
case '>':
case 'gt':
$expr = $eb->gt($column, $columnValue);
break;
case '>=':
case 'gte':
$expr = $eb->gte($column, $columnValue);
break;
case 'like':
$expr = $eb->like($column, $columnValue);
break;
case 'not like':
$expr = $eb->notLike($column, $columnValue);
break;
default:
$expr = $eb->comparison($column, strtoupper($operator), $columnValue);
break;
}
} else {
$expr = '(1)';
}
return $expr;
}
/**
* @test
*/
public function createNamedParameterDelegatesToConcreteQueryBuilder()
{
$this->concreteQueryBuilder->createNamedParameter(5, Argument::cetera())->shouldBeCalled()->willReturn(':dcValue1');
$this->subject->createNamedParameter(5);
}
public function testCreateNamedParameterCustomPlaceholder()
{
$qb = new QueryBuilder($this->conn);
$qb->select('u.*')->from('users', 'u')->where($qb->expr()->eq('u.name', $qb->createNamedParameter(10, \PDO::PARAM_INT, ':test')));
$this->assertEquals('SELECT u.* FROM users u WHERE u.name = :test', (string) $qb);
$this->assertEquals(10, $qb->getParameter('test'));
}
/**
* @param mixed $value
* @param int $type
* @return string
*/
public function parameter($value, $type = null)
{
return $this->queryBuilder->createNamedParameter($value, $type);
}
/**
* Creates a new named parameter and bind the value $value to it.
*
* This method provides a shortcut for PDOStatement::bindValue
* when using prepared statements.
*
* The parameter $value specifies the value that you want to bind. If
* $placeholder is not provided bindValue() will automatically create a
* placeholder for you. An automatic placeholder will be of the name
* ':dcValue1', ':dcValue2' etc.
*
* For more information see {@link http://php.net/pdostatement-bindparam}
*
* Example:
* <code>
* $value = 2;
* $q->eq( 'id', $q->bindValue( $value ) );
* $stmt = $q->executeQuery(); // executed with 'id = 2'
* </code>
*
* @license New BSD License
* @link http://www.zetacomponents.org
*
* @param mixed $value
* @param mixed $type
* @param string $placeHolder The name to bind with. The string must start with a colon ':'.
*
* @return IParameter the placeholder name used.
*/
public function createNamedParameter($value, $type = IQueryBuilder::PARAM_STR, $placeHolder = null)
{
return new Parameter($this->queryBuilder->createNamedParameter($value, $type, $placeHolder));
}
/**
* Creates a new named parameter and bind the value $value to it.
*
* This method provides a shortcut for PDOStatement::bindValue
* when using prepared statements.
*
* The parameter $value specifies the value that you want to bind. If
* $placeholder is not provided bindValue() will automatically create a
* placeholder for you. An automatic placeholder will be of the name
* ':dcValue1', ':dcValue2' etc.
*
* @param mixed $value
* @param int $type
* @param string $placeHolder The name to bind with. The string must start with a colon ':'.
*
* @return string the placeholder name used.
*/
public function createNamedParameter($value, int $type = \PDO::PARAM_STR, string $placeHolder = null) : string
{
return $this->concreteQueryBuilder->createNamedParameter($value, $type, $placeHolder);
}
/**
* @param QueryBuilder $builder
* @param CompositeExpression $link
* @param ErrorCollection $errors
* @param string $table
* @param string $column
* @param string $method
* @param string|array $params
*
* @return void
*
* @SuppressWarnings(PHPMD.ElseExpression)
*/
protected function applyComparisonMethod(QueryBuilder $builder, CompositeExpression $link, ErrorCollection $errors, $table, $column, $method, $params)
{
// params could be in form of 1 value or array of values
if (is_array($params) === true) {
foreach ($params as $param) {
if (is_scalar($param) === true) {
$param = (string) $builder->createNamedParameter($param);
$link->add($builder->expr()->{$method}($this->getTableColumn($table, $column), $param));
} else {
$this->addInvalidQueryParameterError($errors, $column);
}
}
} elseif (is_scalar($params) === true) {
$param = $builder->createNamedParameter((string) $params);
$link->add($builder->expr()->{$method}($this->getTableColumn($table, $column), $param));
} else {
// parameter is neither array nor string/scalar
$this->addInvalidQueryParameterError($errors, $column);
}
}
/**
* Creates a new named parameter and bind the value $value to it.
*
* @param mixed $value
* @param mixed $type
* @param string $placeHolder The name to bind with. The string must start with a colon ':'.
*
* @return string the placeholder name used.
*/
public function createNamedParameter($value, $type = \PDO::PARAM_STR, $placeHolder = null)
{
return $this->qb->createNamedParameter($value, $type, $placeHolder);
}
/**
* @param $value
* @param $key
* @param ClassMetadata $metaData
* @param QueryBuilder $builder
* @return string
*/
protected function getNamedParameter($value, $key, ClassMetadata $metaData, QueryBuilder $builder)
{
$pdoTypeMapping = ['string' => \PDO::PARAM_STR, 'text' => \PDO::PARAM_STR, 'date' => \PDO::PARAM_STR, 'datetime' => \PDO::PARAM_STR, 'boolean' => \PDO::PARAM_INT, 'integer' => \PDO::PARAM_INT, 'decimal' => \PDO::PARAM_STR, 'float' => \PDO::PARAM_STR];
$nullAble = $metaData->fieldMappings[$key]['nullable'];
// Check if nullable
if (!isset($value) && $nullAble) {
return $builder->createNamedParameter(null, \PDO::PARAM_NULL);
}
$type = $metaData->fieldMappings[$key]['type'];
if (!array_key_exists($type, $pdoTypeMapping)) {
throw new \RuntimeException("Type {$type} not found");
}
return $builder->createNamedParameter($value, $pdoTypeMapping[$type]);
}
/**
* Builds the WHERE clause from $filter
* @param QueryBuilder $qb
* @param array $filters should be an array with arrays wich contains 3 datas
* [
* ['column_name', 'expr_type', 'value'],
* [],
* ...
* ]
* expr_types: 'eq', 'neq', 'lt', 'lte', 'gt', 'gte', 'like', 'in', 'notIn', 'notLike'
* @return self
*/
protected function buildWhere(QueryBuilder $qb, array $filters = [])
{
if (!empty($filters)) {
$expr = $qb->expr()->andX();
foreach ($filters as $f) {
$column = $f[0];
$expr_type = $f[1];
$value = isset($f[2]) ? $f[2] : null;
$type = \PDO::PARAM_STR;
if ($this->getColumn($column) === null) {
throw Schema\SchemaException::columnDoesNotExist($column, $this->table_name);
}
if (!in_array($expr_type, $this->getExpressionTypes())) {
throw QueryBuilderException::expressionTypeDoesNotExist($expr_type);
}
if (in_array($expr_type, ['in', 'notIn']) && is_array($value)) {
switch ($this->column_types[$column]) {
case 'integer':
$type = \Doctrine\DBAL\Connection::PARAM_INT_ARRAY;
break;
case 'string':
$type = \Doctrine\DBAL\Connection::PARAM_STR_ARRAY;
break;
}
}
$expr->add($qb->expr()->{$expr_type}($this->conn->quoteIdentifier($column), $qb->createNamedParameter($value, $type)));
}
$qb->where($expr);
}
return $this;
}
