/**
*
* @param Table $table
* @param Table $parentTable
* @param boolean $strict default false. If false, search in both ways
* @return string
* @throws \Exception
*/
private function getJoinCriteria(Table $table, Table $parentTable, $strict = false)
{
$libelle = '';
//Look for the FK object
if ($table && count($table->getForeignKeys()) > 0) {
foreach ($table->getForeignKeys() as $fk) {
//var_dump($fk);
if ($fk->getParentTable() == $parentTable->getOid()) {
$cols = [];
$refCols = [];
$colNames = [];
$refColNames = [];
//var_dump($fk->getCols());
foreach ($fk->getCols() as $colId) {
$col = $table->getAColumn($colId);
if ($col) {
$cols[] = $col;
$colNames[] = $table->getAlias() . '.' . $col->getName();
} else {
throw new \Exception('Unknwon column ID : ' . $colId . print_r($table->getColumns(), true));
}
}
if (count($cols) == 0) {
throw new \Exception('No Column in the foreign key' . print_r($fk, true));
}
$index = 0;
foreach ($fk->getRefCols() as $colId) {
$col = $parentTable->getAColumn($colId);
if ($col) {
if (count($cols) == 1 || $cols[$index]->getType() == $col->getType()) {
$refCols[] = $col;
$refColNames[] = $parentTable->getAlias() . '.' . $col->getName();
} else {
throw new \Exception('Columns order not matching');
}
} else {
throw new \Exception('Unknwon column ID : ' . $colId . print_r($parentTable->getColumns(), true));
}
$index++;
}
$libelle = '(' . implode(', ', $colNames) . ') = (' . implode(', ', $refColNames) . ')';
break;
}
}
}
if (empty($libelle) && !$strict) {
$libelle = $this->getJoinCriteria($parentTable, $table, true);
}
if (empty($libelle)) {
throw new \Exception('No foreign key possible');
}
return $libelle;
}
/**
*
* @param Table $table
* @param Table $parentTable
* @param $parentAlias
* @return string
* @throws \Exception
* @internal param bool $strict default false. If false, search in both ways
* @TODO Manage null value with foreign key. For now we do always inner join => Null value are ignored
*/
private function getJoinCriteria(Table $table, Table $parentTable, $parentAlias)
{
$libelle = '';
$joinType = 'INNER';
//Look for the FK object
if ($table && count($table->getForeignKeys()) > 0) {
foreach ($table->getForeignKeys() as $fk) {
//var_dump($fk);
if ($fk->getParentTable() == $parentTable->getOid()) {
$cols = [];
$refCols = [];
$colNames = [];
$refColNames = [];
//From table
foreach ($fk->getCols() as $position) {
$col = null;
foreach ($table->getColumns() as $colTable) {
if ($colTable->getPosition() == $position) {
/* if($colTable->getNullable()){
$joinType = 'LEFT';
}*/
$col = $colTable;
break;
}
}
if ($col) {
$cols[] = $col;
$colNames[] = self::INSERT_VALUES_TABLE . '.' . $col->getName();
} else {
throw new \Exception('Unknwon column position : ' . $position . print_r($table->getColumns(), true));
}
}
if (count($cols) == 0) {
throw new \Exception('No Column in the foreign key' . print_r($fk, true));
}
//Reference table
$index = 0;
foreach ($fk->getRefCols() as $position) {
$col = null;
foreach ($parentTable->getColumns() as $colTable) {
if ($colTable->getPosition() == $position) {
$col = $colTable;
break;
}
}
if ($col) {
if (count($cols) == 1 || $cols[$index]->getType() == $col->getType()) {
$refCols[] = $col;
$refColNames[] = $parentAlias . '.' . $col->getName();
} else {
throw new \Exception('Columns order not matching');
}
} else {
throw new \Exception('Unknwon column position : ' . $position . print_r($parentTable->getColumns(), true));
}
$index++;
}
$libelle .= " {$joinType} JOIN " . $parentTable->getSchema() . PgAnalyzer::DB_SEPARATOR . $parentTable->getName() . ' ' . $parentAlias . ' ON (' . implode(', ', $colNames) . ') = (' . implode(', ', $refColNames) . ')';
break;
}
}
}
if (empty($libelle)) {
throw new \Exception('No foreign key possible');
}
return $libelle;
}
