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