/**
* @expectedException \dokuwiki\plugin\struct\meta\StructException
*/
public function test_leftjoin_existing_alias_exception()
{
$qb = new QueryBuilder();
$qb->addTable('first', 'T1');
$qb->addTable('second', 'T2');
$qb->addLeftJoin('T2', 'third', 'T1', 'T2.bar = T1.bar');
}
/**
* 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();
}