Having an ActiveRecord style class in php, what is the correct way to pass a model to a view for a model that contains foriegn keys / realtionships to other models? Should my Controller instantiate the related classes (user_id, category_id) and then pass all the information to the view, or should I generate a new layer in between the model and controller that combines the two? In essence I guess I'm asking where the sql "join" queries should go?
In my database I have a table called Article which looks like,
+------------------+------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user_id | int(11) | NO | MUL | NULL | |
| title | char(30) | NO | | NULL | |
| subtitle | char(60) | YES | | NULL | |
| time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| visible | tinyint(1) | NO | | 1 | |
| body | mediumtext | YES | | NULL | |
| category_id | int(10) | NO | MUL | NULL | |
| featured_mediaid | int(10) | YES | | NULL | |
+------------------+------------+------+-----+-------------------+-----------------------------+
I implemented this table in my article class:
Class Article extends DatabaseTable{
public $id = null;
public $title = null;
public $subtitle = null;
public $body = null;
public $time = null;
public $visible = null;
public $category_id = null;
public $user_id = null;
public $featured_mediaid = null;
/*.....methods for Article*/
}
and a related table:
+-------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | NO | UNI | NULL | |
| description | char(50) | NO | | | |
| visible | tinyint(1) | NO | | 1 | |
+-------------+------------+------+-----+---------+----------------+
and a category class:
Class Category extends DatabaseTable {
public $id = null;
public $name = null;
public $description = null;
public $visible = null;
/*... Some methods here */
}
all my classes inherit from DatabaseTable which has the getById method:
Class DatabaseTable {
// ...
public static function getById($id){
try {
$connection = new PDO(Database::DB_DSN, Database::DB_USERNAME, Database::DB_PASSWORD);
$sql = "SELECT * FROM " . strtolower(self::get_called()) . " WHERE id = :id";
$statement = $connection->prepare($sql);
$statement->bindValue(":id", (int) $id, PDO::PARAM_INT);
$statement->execute();
$row = $statement->fetch(PDO::FETCH_ASSOC);
$connection = null;
}
/*catch(PDOException $exception) {
}*/
catch(Exception $exception) {
echo "An Unknown Exception Occured";
echo "Calling Class [" . self::get_called() . "] " . "Method [" . __FUNCTION__ . "]" ;
$connection = null;
echo $exception->getMessage();
}
$class = self::get_called();
return new $class($row);
}
public static function get_called(){
return get_called_class() ;
}
//...
}