/** * Create a new database table object. * * @param \Tabulate\DB\Database $database The database to which this table belongs. * @param string $name The name of the table. */ public function __construct($database, $name) { $this->database = $database; $this->name = $name; $this->columns = array(); $columns = $this->database->query("SHOW FULL COLUMNS FROM `{$name}`"); foreach ($columns as $column_info) { $column = new Column($this->database, $this, $column_info); $this->columns[$column->getName()] = $column; } $this->recordCounter = new RecordCounter($this); }
/** * Determine whether a given value is valid for a foreign key (i.e. is the title of a foreign row). * * @param \Tabulate\DB\Column $column The foreign key column * @param string $value The value to check * @return true If the value is valid * @throws Exception If the value is not valid */ protected function validateForeignKey($column, $value) { $foreignTable = $column->getReferencedTable(); if (!$this->getRecordsByTitle($foreignTable, $value)) { $link = '<a href="' . $foreignTable->getUrl() . '" title="Opens in a new tab or window" target="_blank" >' . $foreignTable->getTitle() . '</a>'; throw new \Exception("Value <code>{$value}</code> not found in {$link}"); } return true; }
/** * For a given foreign key column, get an alias and join clause for selecting * against that column's foreign values. If the column is not a foreign key, * the alias will just be the qualified column name, and the join clause will * be the empty string. * * @param \Tabulate\DB\Column $column The FK column * @return array Array with 'join_clause' and 'column_alias' keys */ public function joinOn($column) { $joinClause = ''; $columnAlias = '`' . $this->getName() . '`.`' . $column->getName() . '`'; if ($column->isForeignKey()) { $fk1Table = $column->getReferencedTable(); if (!$fk1Table) { throw new \Exception("Unable to get referenced table of " . $column->getTable()->getName() . '.' . $column->getName()); } $fk1TitleColumn = $fk1Table->getTitleColumn(); $joinClause .= ' LEFT OUTER JOIN `' . $fk1Table->getName() . '` AS f' . $this->aliasCount . ' ON (`' . $this->getName() . '`.`' . $column->getName() . '` ' . ' = `f' . $this->aliasCount . '`.`' . $fk1Table->getPkColumn()->getName() . '`)'; $columnAlias = "`f{$this->aliasCount}`.`" . $fk1TitleColumn->getName() . "`"; // FK is also an FK? if ($fk1TitleColumn->isForeignKey()) { $fk2_table = $fk1TitleColumn->getReferencedTable(); $fk2_title_column = $fk2_table->getTitleColumn(); $joinClause .= ' LEFT OUTER JOIN `' . $fk2_table->getName() . '` AS ff' . $this->aliasCount . ' ON (f' . $this->aliasCount . '.`' . $fk1TitleColumn->getName() . '` ' . ' = ff' . $this->aliasCount . '.`' . $fk1Table->getPkColumn()->getName() . '`)'; $columnAlias = "`ff{$this->aliasCount}`.`" . $fk2_title_column->getName() . "`"; } $this->aliasCount++; } return array('join_clause' => $joinClause, 'column_alias' => $columnAlias); }