/**
* @expectedException \dokuwiki\plugin\struct\meta\StructException
*/
public function test_missing_alias()
{
$qb = new QueryBuilder();
$qb->addTable('first', 'T1');
$qb->addSelectColumn('WrongAlias', 'colbar', 'colAlias');
}
/**
* Transform the set search parameters into a statement
*
* @return array ($sql, $opts) The SQL and parameters to execute
*/
public function getSQL()
{
if (!$this->columns) {
throw new StructException('nocolname');
}
$QB = new QueryBuilder();
// basic tables
$first_table = '';
foreach ($this->schemas as $schema) {
$datatable = 'data_' . $schema->getTable();
if ($first_table) {
// follow up tables
$QB->addLeftJoin($first_table, $datatable, $datatable, "{$first_table}.pid = {$datatable}.pid");
} else {
// first table
if (!$schema->isLookup()) {
$QB->addTable('schema_assignments');
$QB->filters()->whereAnd("{$datatable}.pid = schema_assignments.pid");
$QB->filters()->whereAnd("schema_assignments.tbl = '{$schema->getTable()}'");
$QB->filters()->whereAnd("schema_assignments.assigned = 1");
$QB->filters()->whereAnd("GETACCESSLEVEL({$datatable}.pid) > 0");
$QB->filters()->whereAnd("PAGEEXISTS({$datatable}.pid) = 1");
}
$QB->addTable($datatable);
$QB->addSelectColumn($datatable, 'pid', 'PID');
$QB->addGroupByColumn($datatable, 'pid');
$first_table = $datatable;
}
$QB->filters()->whereAnd("{$datatable}.latest = 1");
}
// columns to select, handling multis
$sep = self::CONCAT_SEPARATOR;
$n = 0;
foreach ($this->columns as $col) {
$CN = 'C' . $n++;
if ($col->isMulti()) {
$datatable = "data_{$col->getTable()}";
$multitable = "multi_{$col->getTable()}";
$MN = 'M' . $col->getColref();
$QB->addLeftJoin($datatable, $multitable, $MN, "{$datatable}.pid = {$MN}.pid AND\n {$datatable}.rev = {$MN}.rev AND\n {$MN}.colref = {$col->getColref()}");
$col->getType()->select($QB, $MN, 'value', $CN);
$sel = $QB->getSelectStatement($CN);
$QB->addSelectStatement("GROUP_CONCAT({$sel}, '{$sep}')", $CN);
} else {
$col->getType()->select($QB, 'data_' . $col->getTable(), $col->getColName(), $CN);
$QB->addGroupByStatement($CN);
}
}
// where clauses
foreach ($this->filter as $filter) {
list($col, $value, $comp, $op) = $filter;
$datatable = "data_{$col->getTable()}";
$multitable = "multi_{$col->getTable()}";
/** @var $col Column */
if ($col->isMulti()) {
$MN = 'MN' . $col->getColref();
// FIXME this joins a second time if the column was selected before
$QB->addLeftJoin($datatable, $multitable, $MN, "{$datatable}.pid = {$MN}.pid AND\n {$datatable}.rev = {$MN}.rev AND\n {$MN}.colref = {$col->getColref()}");
$coltbl = $MN;
$colnam = 'value';
} else {
$coltbl = $datatable;
$colnam = $col->getColName();
}
$col->getType()->filter($QB, $coltbl, $colnam, $comp, $value, $op);
// type based filter
}
// sorting - we always sort by the single val column
foreach ($this->sortby as $sort) {
list($col, $asc) = $sort;
/** @var $col Column */
$col->getType()->sort($QB, 'data_' . $col->getTable(), $col->getColName(false), $asc ? 'ASC' : 'DESC');
}
return $QB->getSQL();
}
/**
* Add the proper selection for this type to the current Query
*
* The default implementation here should be good for nearly all types, it simply
* passes the given parameters to the query builder. But type may do more fancy
* stuff here, eg. join more tables or select multiple values and combine them to
* JSON. If you do, be sure implement a fitting rawValue() method.
*
* The passed $tablealias.$columnname might be a data_* table (referencing a single
* row) or a multi_* table (referencing multiple rows). In the latter case the
* multi table has already been joined with the proper conditions.
*
* You may assume a column alias named 'PID' to be available, should you need the
* current page context for a join or sub select.
*
* @param QueryBuilder $QB
* @param string $tablealias The table the currently saved value(s) are stored in
* @param string $colname The column name on above table
* @param string $alias The added selection *has* to use this column alias
*/
public function select(QueryBuilder $QB, $tablealias, $colname, $alias)
{
$QB->addSelectColumn($tablealias, $colname, $alias);
}