Items with relationships
Cherrycake does not restricts you on how to establish relationships between tables in your database, and the Item and Items classes provide some capabilities that will help you build the relationship structure of your liking.
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:
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:
Now, we add a method to our Movie
class that allows us to get a Director
object:
So now, whenever we have a Movie
object, we can get its Director by calling the getDirector
method, for example:
Getting the director's name was this straightforward: $movie->getDirector()->name
Note that in this example we've also applied the
random
order, which is always available in addition to your custom orders, to simply randomize the order of the resulting Item objects.
See this example working in the Cherrycake documentation examples site.
Custom filtering with relationships
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:
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:
See this example working in the Cherrycake documentation examples site.
Last updated