In a previous post, I described working on a persistence mechanism not like ActiveRecord. It's more like a gateway. I indicated that I wish it to:
- return domain objects instead of returning an array of values which have then to be set as properties of domain objects;
- allow the selection of database fields rather than merely performing a SELECT * FROM tablename;
- allow the expression of relations between tables and map/create related domain objects accordingly; and
- allow some customization of queries
Examples of queries from a blogging system will serve well to further explain my intentions, as features of such systems can be implemented with the relations I'm thinking of:
- A blog consists of posts or entries (I'll call them posts).
- Each post has one author. Conversely, each author has many posts.
- Each post may attract many comments. Conversely, each comment belongs to one post.
- Each post may be assigned to many categories or tags (I'll call them categories). Conversely, each category has many posts.
The gateway I have chosen to call a "manager". I'll demonstrate selects only at the moment, but I should probably implement separate objects for SELECTs, INSERTs and UPDATEs, or at least maintain a division between SELECT and INSERT/UPDATE.
So here are some use cases. Each requires a PDO or PDO subclass instance, and a "manager" object. So imagine that something like the following is being done for each case:
$pdo = new PDO();
$manager = new Persistence_Manager($pdo);I've used a "PDO" object here but in fact I'm using my own subclass of the connection object from the excellent pdoext library.
Use Case 1: Find all posts
//Make a prototype
$prototype = new Post();
//Decide which fields to fetch
$prototype->with('Post', array('id', 'title', 'post');
foreach($manager->find($prototype) as $post) {
//display post
}Use Case 2: Find a specific post
$prototype = new Post();
$prototype->with('Post', array('id', 'title', 'post');
//Find using primary key
$posts = $manager->findByPk($prototype, 2);
//Or find using some other condition, such as title = 'My First Post'
$posts = $manager->find($prototype, array(array('title', 'My First Post', '=')); //WHERE title='My First Post'
foreach($posts and $post) {
//display post or posts
}My plan here is to allow multiple conditions. The reason there's a nested array is because pdoext allows expressing conditions as arrays.
Use Case 3: Find a post and its author
$prototype = new Post();
$prototype->with('Post', array('id', 'title', 'post'));
$prototype->with('Author', array('id', 'name'));
foreach($manager->findByPk($post, 1) as $post) {
echo "$post->title, posted by $post->author->name"; //Author object is automatically created
}The Author object is created automatically and populated with the 'Author' fields specified in the second call to $prototype->with().
The relationship between a Post and its Author is defined in an 'Association' object which is added to either or both domain objects. One could add them manually at run-time or make them part of the domain classes:
class Post extends Persistence_Object
{
public function __construct()
{
parent::__construct();
$this->addAssociation( new PostToAuthorAssociation() );
}
}Use Case 4: Custom associations
It's not uncommon for pages displaying single blog entries to display links to the previous and the next post. I've seen code where these are done with separate queries. But since each post has only one previous and one next, and since fetching a couple post ids and titles is not much more overhead than fetching one complete post and its possibly long text, one could do it in a custom query. Something like:
SELECT Post.id, Post.title, Post.post, prev.id, prev.title, next.id, next.title FROM posts AS Post
LEFT JOIN posts AS prev ON (prev.id = Posts.id - 1)
LEFT JOIN post AS next ON (next.id = Posts.id + 1)
WHERE Post.id = '2'So, assuming that such relationships are defined in an Association, the calling code for this might look like:
$prototype = new Post();
$prototype->with('Post', array('id', 'title', 'post'));
$prototype->with('prev', array('id', 'title'));
$prototype->with('next', array('id', 'title'));
foreach($manager->findByPk($post, 2) as $post) {
echo "$post->prev->title"; //Display previous post's title
echo "$post->next->title"; //Display next post's title
}Use Case 5: Nested queries
One might wish to find display along with the current post a list of recent posts by the author. For now I'm going to ignore any LIMITs in the underlying query, but the syntax might be something like:
$postProto = new Post();
$postProto->with('Post', array('id', 'title', 'post'));
$authorProto = new Author();
$authorProto->with('Author', array('id', 'name'));
$authorProto->with('Post', array('id', 'title')); //one-to-many
$postProto->with('Author', $authorProto);
foreach($manager->findByPk($postProto, 1) as $post) {
//A list of posts by this post's author
print_r($post->author->posts);
}This time, we're passing in a prototype of the Author instead of a list of fields. Whatever fields are defined for the Author prototype will be used for the subsidiary query to find posts by the Author.
This post is intended to show how this persistence mechanism might be used, not the exact syntax. In other words, things might change.
