function processRelational($select, $colAlias = null, $autoSelectId = false) { $sql = []; $type = ''; $typeParent = $this->name; $prefix = $this->dataSource->getTablePrefix(); $q = $this->dataSource->getQuoteCharacter(); $aliasParent = $prefix . $this->name; $shareds = []; $selection = explode('~', ltrim(str_replace(['<', '>', '<>', '<~~>', '.'], ['~<~', '~>~', '~<>~', '<>', '~.~'], $select), '~')); $relation = null; foreach ($selection as $i => $token) { if (in_array($token, ['<>', '<', '>', '.'])) { $pkP = $this->dataSource[$typeParent]->primaryKey; if (!isset($selection[$i + 1])) { throw new Exception('Unexpected end of relational declaration expecting table or column name after "' . $token . '" in ' . $select); } switch ($token) { case '<>': $type = $selection[$i + 1]; list($type, $alias) = self::specialTypeAliasExtract($type, $superalias); $pkT = $this->dataSource[$type]->primaryKey; if (substr($type, -1) == '2') { if ($type == $alias) { $alias = substr($alias, 0, -1); } $type = substr($type, 0, -1); $two = true; } else { $two = false; } if ($superalias) { $alias = $superalias . '__' . ($alias ? $alias : $type); } $rels = [$typeParent, $type]; sort($rels); $imp = implode('_', $rels); $impt = $q . $prefix . $imp . $q . ($superalias ? ' AS ' . $q . $prefix . $superalias . '__' . $imp . $q : ''); if ($exist = $this->dataSource->tableExists($type) && $this->dataSource->tableExists($imp)) { if ($superalias) { $imp = $superalias . '__' . $imp; } $joint = $type != $alias ? "{$q}{$prefix}{$type}{$q} AS {$q}{$prefix}{$alias}{$q}" : $q . $prefix . $alias . $q; $sql[] = [$impt]; $sql[] = [$joint, "{$q}{$prefix}{$alias}{$q}.{$q}{$pkT}{$q}={$q}{$prefix}{$imp}{$q}.{$q}{$type}" . (!$two && in_array($type, $shareds) ? '2' : '') . "_{$pkT}{$q}", "{$q}{$aliasParent}{$q}.{$q}{$pkP}{$q}={$q}{$prefix}{$imp}{$q}.{$q}{$typeParent}" . ($two ? '2' : '') . "_{$pkP}{$q}"]; if (!$two) { $shareds[] = $type; } } $typeParent = $type; $aliasParent = $prefix . $alias; $type = ''; $relation = '<>'; break; case '>': list($type, $alias) = self::specialTypeAliasExtract($type, $superalias); $pkT = $this->dataSource[$type]->primaryKey; if ($superalias) { $alias = $superalias . '__' . $alias; } $joint = $type != $alias ? "{$q}{$prefix}{$type}{$q} as {$q}{$prefix}{$alias}{$q}" : $q . $prefix . $alias . $q; if ($exist = $this->dataSource->tableExists($type) && $this->dataSource->columnExists($type, $typeParent . '_' . $pkP)) { $sql[] = [$joint, "{$q}{$aliasParent}{$q}.{$q}{$pkP}{$q}={$q}{$prefix}{$alias}{$q}.{$q}{$typeParent}_{$pkP}{$q}"]; } $typeParent = $type; $aliasParent = $prefix . $alias; $type = ''; $relation = '>'; break; case '<': list($type, $alias) = self::specialTypeAliasExtract($type, $superalias); if (substr($type, -1) == '2') { if ($type == $alias) { $alias = substr($alias, 0, -1); } $type = substr($type, 0, -1); $two = true; } else { $two = false; } $pkT = $this->dataSource[$type]->primaryKey; if ($superalias) { $alias = $superalias . '__' . $alias; } $joint = $type != $alias ? "{$q}{$prefix}{$type}{$q} as {$q}{$prefix}{$alias}{$q}" : $q . $prefix . $alias . $q; if ($exist = $this->dataSource->tableExists($typeParent) && $this->dataSource->columnExists($typeParent, $type . '_' . $pkT)) { $sql[] = [$joint, "{$q}{$prefix}{$alias}{$q}.{$q}{$pkT}{$q}={$q}{$prefix}{$typeParent}{$q}.{$q}{$type}_{$pkT}{$q}"]; } $typeParent = $type; $type = ''; $relation = '<'; break; case '.': $type = $selection[$i + 1]; break; } } } $Qt = new Select(null, $this->dataSource->getQuoteCharacter(), $this->dataSource->getTablePrefix()); $i = 0; foreach ($sql as $_sql) { if ($i) { $Qt->join(array_shift($_sql)); if (!empty($_sql)) { $Qt->joinOn(implode(' AND ', $_sql)); } } else { $Qt->from(array_shift($_sql)); if (!empty($_sql)) { $Qt->where(implode(' AND ', $_sql)); } } $i++; } $table = $typeParent; $col = trim($type); $agg = $this->dataSource->getAgg(); $aggc = $this->dataSource->getAggCaster(); $sep = $this->dataSource->getSeparator(); $cc = $this->dataSource->getConcatenator(); if (!$colAlias) { $colAlias = ($superalias ? $superalias : $alias) . $relation . $col; } if ($colAlias) { $colAlias = ' AS ' . $q . $colAlias . $q; } if ($autoSelectId) { $idAlias = ' AS ' . $q . ($superalias ? $superalias : $alias) . $relation . $pkT . $q; } $Qt2 = $Qt->getClone(); if ($exist) { switch ($relation) { case '<': $Qt->select($this->dataSource->getReadSnippetCol($table, $col, $q . $prefix . $alias . $q . '.' . $q . $col . $q)); $this->select('(' . $Qt . ') ' . $colAlias); if ($autoSelectId) { $Qt2->select($q . $prefix . $alias . $q . '.' . $q . $pkT . $q); $this->select('(' . $Qt2 . ') ' . $idAlias); } break; case '>': $Qt->select("{$agg}(COALESCE(" . $this->dataSource->getReadSnippetCol($table, $col, "{$q}{$prefix}{$alias}{$q}.{$q}{$col}{$q}") . "{$aggc},''{$aggc}) {$sep} {$cc})"); $this->select('(' . $Qt . ') ' . $colAlias); if ($autoSelectId) { $Qt2->select("{$agg}(COALESCE({$q}{$prefix}{$alias}{$q}.{$q}{$pkT}{$q}{$aggc},''{$aggc}) {$sep} {$cc})"); $this->select('(' . $Qt2 . ') ' . $idAlias); } break; case '<>': $Qt->select("{$agg}(" . $this->dataSource->getReadSnippetCol($table, $col, "{$q}{$prefix}{$alias}{$q}.{$q}{$col}{$q}") . "{$aggc} {$sep} {$cc})"); $this->select('(' . $Qt . ') ' . $colAlias); if ($autoSelectId) { $Qt2->select("{$agg}({$q}{$prefix}{$alias}{$q}.{$q}{$pkT}{$q}{$aggc} {$sep} {$cc})"); $this->select('(' . $Qt2 . ') ' . $idAlias); } break; } } }
function processRelational($select, $colAlias = null, $autoSelectId = false) { $selection = explode('~~', ltrim(str_replace(['<', '>', '<>', '<~~~>', '.'], ['~~<~', '~~>~', '~~<>~', '<>', '~~.'], $select), '~')); $selection = array_reverse($selection); $column = ltrim(array_shift($selection), '.'); $selection = array_map(function ($v) { return explode('~', $v); }, $selection); $tmp = $selection; $selection = []; foreach ($tmp as $i => list($relation, $table)) { if ($relation == '<>') { $joinWith = isset($tmp[$i + 1]) ? $tmp[$i + 1][1] : $this->name; $relationTable = $this->dataSource->many2manyTableName($table, $joinWith); $selection[] = ['<', $table]; $selection[] = ['>', $relationTable]; } else { $selection[] = [$relation, $table]; } } list($relation, $table) = array_shift($selection); $qTable = $table; $q = $this->quoteCharacter; $Q = new Select($table, $this->quoteCharacter, $this->tablePrefix); $agg = $this->dataSource->getAgg(); $aggc = $this->dataSource->getAggCaster(); $aggc = $this->dataSource->getAggCaster(); $sep = $this->dataSource->getSeparator(); $cc = $this->dataSource->getConcatenator(); $Q->select("{$agg}( COALESCE(" . $Q->formatColumnName($column) . "{$aggc}, ''{$aggc}) {$sep} {$cc} )"); $Q->from($table); $qPk = $pk = $this->dataSource[$table]->getPrimaryKey(); $previousTable = $table; $previousRelation = $relation; $qRelation = $relation; $previousTablePk = $pk; $tableQ = $this->escTable($table); $pkQ = $Q->quote($pk); foreach ($selection as list($relation, $table)) { //list($table, $alias) = self::specialTypeAliasExtract($table,$superalias); $Q->join($table); $pk = $this->dataSource[$table]->getPrimaryKey(); $tableQ = $this->escTable($table); $pkQ = $Q->quote($pk); $previousTableQ = $this->escTable($previousTable); $previousTablePkQ = $Q->quote($previousTablePk); if ($previousRelation == '<') { $col1 = $Q->quote($previousTable . '_' . $previousTablePk); $col2 = $previousTablePkQ; } elseif ($previousRelation == '>') { $col1 = $pkQ; $col2 = $Q->quote($table . '_' . $pk); } $Q->joinOn($tableQ . '.' . $col1 . ' = ' . $previousTableQ . '.' . $col2); $previousTable = $table; $previousTablePk = $pk; $previousRelation = $relation; } if ($relation == '<') { $Q->where($tableQ . '.' . $pkQ . ' = ' . $this->escTable($this->name) . '.' . $Q->quote($table . '_' . $this->getPrimaryKey())); } elseif ($relation == '>') { $Q->where($this->escTable($this->name) . '.' . $pkQ . ' = ' . $tableQ . '.' . $Q->quote($this->name . '_' . $this->getPrimaryKey())); } $colAlias = $Q->quote($qTable . $qRelation . $column); $this->select("({$Q}) as {$colAlias}"); if ($autoSelectId && $column != $qPk) { $Q2 = clone $Q; $Q2->unSelect(); $Q2->select("{$agg}( COALESCE(" . $Q->formatColumnName($qPk) . "{$aggc}, ''{$aggc}) {$sep} {$cc} )"); $colIdAlias = $Q->quote($qTable . $qRelation . $qPk); $this->select("({$Q2}) as {$colIdAlias}"); } }