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