/**
* 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);
}
/**
* 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 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);
}
/**
* 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));
}
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;
}
