Items with relationships
For example, in addition to our
movies
table, let's imagine we have also a simple directors
table containing the names and birth years of movie directors, and that it looks like this:Field name | Specs | |
id | unsigned int auto_increment primary key | The unique id to identify movies. |
name | varchar | The name of the director. |
birthYear | year | The year the director was born. |
A simple relationship would be one that allows us to get the name of the director of one of our movies. Since we already defined our
Movie
class, let's now define a class to represent a director. We create the file /classes/Director.class.php
, and it looks like this:<?php
namespace CherrycakeApp;
class Director extends \Cherrycake\Item {
protected $tableName = "directors";
protected $fields = [
"id" => [
"type" => \Cherrycake\DATABASE_FIELD_TYPE_INTEGER
],
"name" => [
"type" => \Cherrycake\DATABASE_FIELD_TYPE_STRING
]
];
}
Now, we add a method to our
Movie
class that allows us to get a Director
object:class Movie extends \Cherrycake\Item {
...
function getDirector() {
return new Director([
"loadMethod" => "fromId",
"id" => $this->directorId
]);
}
}
So now, whenever we have a
Movie
object, we can get its Director by calling the getDirector
method, for example:$movies = new Movies([
"fillMethod" => "fromParameters",
"p" => [
"limit" => 5,
"order" => ["random"]
]
]);
foreach ($movies as $movie)
echo "{$movie->title} directed by {$movie->getDirector()->name}\n";
Explorers directed by Joe Dante
E.T. the Extra-Terrestrial directed by Steven Spielberg
Tron directed by Steven Lisberger
Close Encounters of the Third Kind directed by Steven Spielberg
Arrival directed by Denis Villeneuve
Getting the director's name was this straightforward:
$movie->getDirector()->name
Note that in this example we've also applied therandom
order, which is always available in addition to your custom orders, to simply randomize the order of the resulting Item objects.
Quite often you'll need to look for data in other tables when using your Items classes. For example, let's say we want to be able to get all the movies whose director was less than 35 years old when they were released.
This is done by adding a custom filter to our Items class, but because the director's
birthYear
is in the directors
table and not in the movies
table, we'll need some way to access it in the new Movies
custom filter.We'll call this filter
releasedWhenDirectorWasYoungerThan
, and here's how it would be done:class Movies extends \Cherrycake\Items {
protected $tableName = "movies";
protected $itemClassName = "\CherrycakeApp\Movie";
function fillFromParameters($p = false) {
// Treat parameters
self::treatParameters($p, [
"releasedWhenDirectorWasYoungerThan" => [
"default" => false
]
]);
// Modify $p accordingly
if ($p["releasedWhenDirectorWasYoungerThan"]) {
$p["tables"][] = "directors";
$p["wheres"][] = ["sqlPart" => "directors.id = movies.directorId"];
$p["wheres"][] = [
"sqlPart" => "movies.year - directors.birthYear <= ?",
"values" => [
[
"type" => \Cherrycake\DATABASE_FIELD_TYPE_INTEGER,
"value" => $p["releasedWhenDirectorWasYoungerThan"]
]
]
];
}
}
// Call the parent fillFromParameters
return parent::fillFromParameters($p);
}
}
Here's what we did in this filter:
- First we added the table
directors
to thetables
parameter to make it available in our SQL statements by doing$p["tables"][] = "directors";
- Then we've added a new entry to the
wheres
array of SQL statements to connect thedirectors
with themovies
table, with thesqlPart
:directors.id = movies.directorId
- Finally, we've added another where statement to filter out only the movies whose director was younger than the specified age when the movie was released, with the
sqlPart
:movies.year - directors.birthYear <= ?
Remember that when using values coming from untrusted sources, it's highly recommended to use the prepared queries methodology: Use a question mark?
instead of the value, and then pass along the value specification in thevalues
key of the array.
So now it's ready to run:
$movies = new Movies([
"fillMethod" => "fromParameters",
"p" => [
"releasedWhenDirectorWasYoungerThan" => 35
]
]);
foreach ($movies as $movie)
echo
"\"{$movie->title}\"".
" directed by ".
$movie->getDirector()->name.
" at age ".
($movie->year - $movie->getDirector()->birthYear).
"\n";
"The Thing" directed by John Carpenter at age 34
"Silent Running" directed by Douglas Trumbull at age 30
"The Hitchhiker’s Guide to the Galaxy" directed by Garth Jennings at age 33
"Tron" directed by Steven Lisberger at age 31
"Close Encounters of the Third Kind" directed by Steven Spielberg at age 31
"The Abyss" directed by James Cameron at age 35
Last modified 2yr ago