/** * Generates the join statement for many-many relationship. * @param \Database\schema\TableSchema $joinTable the join table * @param array $fks the foreign keys * @param \Database\ActiveRecord\Join\Element $parent the parent join element * @return string the join statement * @throws \Exception if a foreign key is invalid */ private function joinManyMany($joinTable, $fks, $parent) { $schema = $this->_builder->getSchema(); $joinAlias = $schema->quoteTableName($this->relation->name . '_' . $this->tableAlias); $parentCondition = []; $childCondition = []; $fkDefined = true; foreach ($fks as $fk) { if (!isset($joinTable->columns[$fk])) { throw new \Database\Exception('The relation "{relation}" in active record class "{class}" is specified with an invalid foreign key "{key}". There is no such column in the table "{table}".', ['{class}' => get_class($parent->model), '{relation}' => $this->relation->name, '{key}' => $fk, '{table}' => $joinTable->name]); } if (isset($joinTable->foreignKeys[$fk])) { list($tableName, $pk) = $joinTable->foreignKeys[$fk]; if (!isset($parentCondition[$pk]) && $schema->compareTableNames($parent->_table->rawName, $tableName)) { $parentCondition[$pk] = $parent->getColumnPrefix() . $schema->quoteColumnName($pk) . '=' . $joinAlias . '.' . $schema->quoteColumnName($fk); } elseif (!isset($childCondition[$pk]) && $schema->compareTableNames($this->_table->rawName, $tableName)) { $childCondition[$pk] = $this->getColumnPrefix() . $schema->quoteColumnName($pk) . '=' . $joinAlias . '.' . $schema->quoteColumnName($fk); } else { $fkDefined = false; break; } } else { $fkDefined = false; break; } } if (!$fkDefined) { $parentCondition = []; $childCondition = []; foreach ($fks as $i => $fk) { if ($i < count($parent->_table->primaryKey)) { $pk = is_array($parent->_table->primaryKey) ? $parent->_table->primaryKey[$i] : $parent->_table->primaryKey; $parentCondition[$pk] = $parent->getColumnPrefix() . $schema->quoteColumnName($pk) . '=' . $joinAlias . '.' . $schema->quoteColumnName($fk); } else { $j = $i - count($parent->_table->primaryKey); $pk = is_array($this->_table->primaryKey) ? $this->_table->primaryKey[$j] : $this->_table->primaryKey; $childCondition[$pk] = $this->getColumnPrefix() . $schema->quoteColumnName($pk) . '=' . $joinAlias . '.' . $schema->quoteColumnName($fk); } } } if ($parentCondition !== [] && $childCondition !== []) { $join = $this->relation->joinType . ' ' . $joinTable->rawName . ' ' . $joinAlias; $join .= ' ON (' . implode(') AND (', $parentCondition) . ')'; $join .= ' ' . $this->relation->joinType . ' ' . $this->getTableNameWithAlias(); $join .= ' ON (' . implode(') AND (', $childCondition) . ')'; if (!empty($this->relation->on)) { $join .= ' AND (' . $this->relation->on . ')'; } return $join; } else { throw new \Database\Exception('The relation "{relation}" in active record class "{class}" is specified with an incomplete foreign key. The foreign key must consist of columns referencing both joining tables.', ['{class}' => get_class($parent->model), '{relation}' => $this->relation->name]); } }
/** * Creates the SQL statement. * @param \Database\schema\CommandBuilder $builder the command builder * @return \Database\Command DB command instance representing the SQL statement */ public function createCommand($builder) { $sql = ($this->distinct ? 'SELECT DISTINCT ' : 'SELECT ') . implode(', ', $this->selects); $sql .= ' FROM ' . implode(' ', $this->joins); $conditions = []; foreach ($this->conditions as $condition) { if ($condition !== '') { $conditions[] = $condition; } } if ($conditions !== []) { $sql .= ' WHERE (' . implode(') AND (', $conditions) . ')'; } $groups = []; foreach ($this->groups as $group) { if ($group !== '') { $groups[] = $group; } } if ($groups !== []) { $sql .= ' GROUP BY ' . implode(', ', $groups); } $havings = []; foreach ($this->havings as $having) { if ($having !== '') { $havings[] = $having; } } if ($havings !== []) { $sql .= ' HAVING (' . implode(') AND (', $havings) . ')'; } $orders = []; foreach ($this->orders as $order) { if ($order !== '') { $orders[] = $order; } } if ($orders !== []) { $sql .= ' ORDER BY ' . implode(', ', $orders); } $sql = $builder->applyLimit($sql, $this->limit, $this->offset); $command = $builder->getDbConnection()->createCommand($sql); $builder->bindValues($command, $this->params); return $command; }