protected function getComparisonExpression(Select $select) { if ($this->isExpr()) { return (string) $this->expr; } else { return $select->quoteWithAlias($this->tableName, $this->columnName); } }
private function filterIsOrIsNot($operator, Select $select, $conditionSetName, $value) { // Don't attempt to filter if no value is available if (!$value) { return $select; } $quotedAlias = $select->quoteWithAlias($this->tableName, $this->columnName); $operator = self::OP_IS === $operator ? '=' : '!='; return $select->whereConditionSet($conditionSetName, "{$quotedAlias} {$operator} ?", $value); }
private function filter($operator, Select $select, $conditionSetName, $value) { $quotedAlias = $select->quoteWithAlias($this->relationship->getSourceTable()->getTableName(), $this->relationship->getSourceColumnName()); switch ($operator) { case self::OP_NOT_EMPTY: case self::OP_CONTAINS: $operator = 'IN'; break; case self::OP_NOT_CONTAINS: case self::OP_EMPTY: $operator = 'NOT IN'; break; } return $select->whereConditionSet($conditionSetName, "{$quotedAlias} {$operator} (?)", new Expr($this->relationship->getFilterSubquery($value))); }
/** * Apply the filter to the supplied Select object. * * @param Select $select * @param string $conditionSetName * @param array $queryVars * @return Select * @throws InvalidOperator */ public function apply(Select $select, $conditionSetName, array $queryVars) { $this->ensurePresenceOfRequiredQueryVars($queryVars); if ($this->isExpr()) { $expression = (string) $this->expr; } else { $expression = $select->quoteWithAlias($this->tableName, $this->columnName); } $op1 = trim($queryVars['operand1']); $op2 = trim($queryVars['operand2']); switch ($queryVars['comp']) { case static::OP_IS: if ('' === $op1) { return $select; } return $select->whereConditionSet($conditionSetName, "{$expression} = ?", $op1); case static::OP_IS_BETWEEN: if ('' === $op1 && '' === $op2) { return $select; } elseif ('' === $op1) { return $select->whereConditionSet($conditionSetName, "{$expression} <= ?", $op2); } elseif ('' === $op2) { return $select->whereConditionSet($conditionSetName, "{$expression} >= ?", $op1); } else { if ($op1 > $op2) { $op1Temp = $op1; $op1 = $op2; $op2 = $op1Temp; } $db = $select->getAdapter(); return $select->whereConditionSet($conditionSetName, sprintf("{$expression} BETWEEN %s AND %s", $db->quote($op1), $db->quote($op2))); } case static::OP_IS_LESS_THAN: if ('' === $op1) { return $select; } return $select->whereConditionSet($conditionSetName, "{$expression} < ?", $op1); case static::OP_IS_MORE_THAN: if ('' === $op1) { return $select; } return $select->whereConditionSet($conditionSetName, "{$expression} > ?", $op1); default: throw new InvalidOperator("{$queryVars['comp']} is not a valid operator for numeric filters."); } }
/** * Provide a default sorting strategy for reference columns. * * @param DbField $field * @param Select $select * @param string $direction * @return Select * @throws Select\SelectException */ public function sortDbReference(DbField $field, Select $select, $direction) { $optionPairs = $field->getOptionPairs(); $tableName = $optionPairs->getTableName(); try { $titleColumn = $optionPairs->detectTitleColumn(); } catch (TitleColumnNotDetectedException $e) { $titleColumn = $field->getName(); } if ($titleColumn instanceof Expr) { $orderSpec = "{$titleColumn} {$direction}"; } else { $orderSpec = new Expr("{$select->quoteWithAlias($tableName, $titleColumn)} {$direction}"); } return $select->order($orderSpec); }
/** * Augment the provided Select object with all the EAV attribute values from this * definition. * * @param Select $select * @return Select * @throws Select */ public function augmentSelect(Select $select) { $db = $this->table->getAdapter(); $id = current($this->table->getPrimaryKey()); $rootTableAlias = $select->quoteWithAlias($this->table->getTableName(), $id); foreach ($this->getAttributes() as $attribute) { $alias = 'eav_' . $attribute['attribute_id']; $table = $this->table->getTableName() . $this->valueTablePrefix . $attribute['backend_type']; $select->joinLeft([$alias => $table], $db->quoteInto("{$alias}.{$id} = {$rootTableAlias} AND {$alias}.attribute_id = ?", $attribute['attribute_id']), [$alias => 'value']); } return $select; }
/** * Augment the provided Select object with a comma-separated list of values for this * many-to-many relationship, using the name parameter as the name of the value in * the resultset. * * @param Select $select * @param string $name * @return Select */ public function augmentSelect(Select $select, $name) { $anchorColumn = $select->quoteWithAlias($this->sourceTable->getTableName(), $this->getSourceColumnName()); $titleColumn = $this->findReferenceTitleColumn(); $driver = $select->getAdapter()->getDriver(); if ($driver instanceof \Dewdrop\Db\Driver\Pdo\Pgsql) { $expr = new Expr("ARRAY_TO_STRING(\n ARRAY(\n SELECT {$titleColumn}\n FROM {$this->getReferenceTableName()} ref\n JOIN {$this->xrefTableName} xref\n ON xref.{$this->xrefReferenceColumnName} = ref.{$this->getReferenceColumnName()}\n WHERE xref.{$this->xrefAnchorColumnName} = {$anchorColumn}\n ORDER BY {$titleColumn}\n ),\n ', '\n )"); } else { $expr = new Expr("(SELECT\n GROUP_CONCAT({$titleColumn} SEPARATOR ', ')\n FROM {$this->getReferenceTableName()} ref\n JOIN {$this->xrefTableName} xref\n ON xref.{$this->xrefReferenceColumnName} = ref.{$this->getReferenceColumnName()}\n WHERE xref.{$this->xrefAnchorColumnName} = {$anchorColumn}\n ORDER BY {$titleColumn}\n )"); } return $select->columns([$name => $expr]); }
/** * @todo Could use REVERSE() trick here, but we'd need to require at least PG 9.1. */ private function filterEndsWith(Select $select, $conditionSetName, $value) { $quotedAlias = $select->quoteWithAlias($this->tableName, $this->columnName); $operator = $select->getAdapter()->getDriver()->getCaseInsensitiveLikeOperator(); return $select->whereConditionSet($conditionSetName, "{$quotedAlias} {$operator} ?", '%' . $value); }
/** * Augment the provided Select object with a comma-separated list of values for this * many-to-many relationship, using the name parameter as the name of the value in * the resultset. * * @param Select $select * @param string $name * @return Select */ public function augmentSelect(Select $select, $name) { $anchorColumn = $select->quoteWithAlias($this->sourceTable->getTableName(), $this->getSourceColumnName()); $titleColumn = $this->findReferenceTitleColumn(); $expr = new Expr("ARRAY_TO_STRING(\n ARRAY(\n SELECT {$titleColumn}\n FROM {$this->getReferenceTableName()} ref\n JOIN {$this->xrefTableName} xref\n ON xref.{$this->xrefReferenceColumnName} = ref.{$this->getReferenceColumnName()}\n WHERE xref.{$this->xrefAnchorColumnName} = {$anchorColumn}\n ORDER BY {$titleColumn}\n ),\n ', '\n )"); return $select->columns([$name => $expr]); }
private function getAliasForComparison(Select $select) { $quotedAlias = $select->quoteWithAlias($this->tableName, $this->columnName); $dbAdapter = $select->getAdapter(); $metadata = $dbAdapter->getTableMetadata($this->tableName); if ($this->truncateTimestamps && 'timestamp' === $metadata['columns'][$this->columnName]['GENERIC_TYPE']) { $quotedAlias = $dbAdapter->getDriver()->truncateTimestampToDate($quotedAlias); } return $quotedAlias; }
/** * Using the supplied \Dewdrop\Fields and \Dewdrop\Db\Select, modify the * Select to include only the current page with the correct number of * records. The DB driver is used to ensure we can get the total number * of records that _would_ have been returned had no pagination been applied * after the query has been executed (using whatever facility is provided * for that use in the specific RDBMS). * * @param Fields $fields * @param Select $select * @return Select * @throws Exception */ public function modifySelect(Fields $fields, Select $select) { if (!$this->isEnabled()) { return $select; } $column = $select->quoteWithAlias($this->field->getTable()->getTableName(), $this->field->getName()); $this->showingDeletedRecords = (bool) $this->request->getQuery($this->getQueryParameterName()); if ($this->isShowingDeletedRecords()) { return $select->where("{$column} = true"); } else { return $select->where("{$column} = false"); } }