/** * * @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; }