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