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)));
}
/**
* Fetch a single row from this listing by using the supplied ID value to
* match against the listing's primary key field.
*
* @param Fields $fields
* @param mixed $id
* @return \Dewdrop\Db\Row
*/
public function fetchRow(Fields $fields, $id)
{
$select = $this->getModifiedSelect($fields);
$quotedPrimaryKey = $select->quoteWithAlias($this->primaryKey->getTable()->getTableName(), $this->primaryKey->getName());
$select->where("{$quotedPrimaryKey} = ?", $id);
return $this->select->getAdapter()->fetchRow($select);
}
public function modifySelect(Fields $fields, Select $select)
{
$conditionSetName = $this->prefix . 'filters';
$filteredInQueryString = [];
$select->registerConditionSet($conditionSetName, $this->getConditionSetConjunction());
foreach ($this->getFilteredFieldIds() as $index => $id) {
$urlId = urlencode($id);
foreach ($fields as $field) {
if ($urlId === $field->getQueryStringId()) {
$callback = $this->getFieldAssignment($field);
$defaults = $this->defaultVars->getDefaultVars($field);
$select = call_user_func($callback, $select, $conditionSetName, array_merge($defaults, $this->getFilterVars($index)));
$filteredInQueryString[] = $field->getQueryStringId();
}
}
}
/* @var $field FieldInterface */
foreach ($fields as $field) {
if (in_array($field->getQueryStringId(), $filteredInQueryString)) {
continue;
}
$defaults = $this->defaultVars->getDefaultVars($field);
if (count($defaults)) {
$select = call_user_func($this->getFieldAssignment($field), $select, $conditionSetName, $defaults);
}
}
foreach ($this->customFilters as $filter) {
$select = $filter['filter']->apply($select, $conditionSetName, $filter['vars']);
}
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]);
}
/**
* Get the basic columns contained in the listing table.
*
* @param Select $select
* @return Select
*/
private function selectFromTable(Select $select)
{
return $select->from([$this->getAlias($this->table->getTableName()) => $this->table->getTableName()], ['*']);
}
/**
* @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);
}
/**
* 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");
}
}
/**
* 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);
}
/**
* 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->request->getQuery($this->prefix . 'disable-pagination')) {
$this->disable();
}
$driver = $select->getAdapter()->getDriver();
$this->page = (int) $this->request->getQuery($this->prefix . 'listing-page', 1);
$driver->prepareSelectForTotalRowCalculation($select);
if (!$this->enabled) {
return $select;
}
return $select->limit($this->getPageSize(), $this->getPageSize() * ($this->page - 1));
}
/**
* Use the OVER() window function to store a count of the total number
* of rows that would have been retrieved if no LIMIT clause was applied
* on the supplied Select object. The total row count will be added
* to the result set as a _dewdrop_count column.
*
* @param Select $select
* @return void
*/
public function prepareSelectForTotalRowCalculation(Select $select)
{
$select->columns(['_dewdrop_count' => new Expr('COUNT(*) OVER()')]);
}
/**
* Attempt to order the options statement using the options table
* metadata. Dewdrop supports two manual sorting columns by
* convention: sort_index and sort_order. If either of those
* columns is present in your options table, the options will be
* sorted by them. Otherwise, we'll sort by the title column.
*
* @param array $columns The columns portion of the table metadata.
* @param Select $stmt
* @return \Dewdrop\Db\Select
*/
protected function orderStmt(array $columns, Select $stmt)
{
$sortColumn = null;
if (array_key_exists('sort_index', $columns)) {
$sortColumn = 'sort_index';
} elseif (array_key_exists('sort_order', $columns)) {
$sortColumn = 'sort_order';
}
if ($sortColumn) {
$primaryKey = null;
foreach ($columns as $column => $meta) {
if ($meta['PRIMARY']) {
$primaryKey = $column;
break;
}
}
return $stmt->order(array($this->tableName . '.' . $sortColumn, $this->tableName . '.' . $primaryKey));
} elseif ($this->titleColumn instanceof Expr) {
return $stmt->order($this->titleColumn);
} else {
return $stmt->order($this->tableName . '.' . $this->titleColumn);
}
}
/**
* Use the SQL_CALC_FOUND_ROWS facility in MySQL to calculate the total
* number of rows that would have been returned from a query if no LIMIT
* had been applied.
*
* @param Select $select
* @return void
*/
public function prepareSelectForTotalRowCalculation(Select $select)
{
$select->preColumnsOption('SQL_CALC_FOUND_ROWS');
}
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;
}
/**
* 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.");
}
}
/**
* 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();
$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]);
}
public function testJoinUsingInternalException()
{
$select = new Select($this->db);
try {
$select->joinUsingInternal('type', 'name', 'cond');
} catch (SelectException $e) {
$this->assertSame('You can only perform a joinUsing after specifying a FROM table', $e->getMessage());
}
}
public function modifySelect(Fields $fields, Select $select)
{
$conditionSetName = $this->prefix . 'filters';
$select->registerConditionSet($conditionSetName, $this->getConditionSetConjunction());
foreach ($this->getFilteredFieldIds() as $index => $id) {
$urlId = urlencode($id);
foreach ($fields as $field) {
if ($urlId === $field->getQueryStringId()) {
$callback = $this->getFieldAssignment($field);
$select = call_user_func($callback, $select, $conditionSetName, $this->getFilterVars($index));
}
}
}
foreach ($this->customFilters as $filter) {
$select = $filter['filter']->apply($select, $conditionSetName, $filter['vars']);
}
return $select;
}
