Pomm is not an ORM and as such does not provide a method to automagically fetch relations.
This choice is made because of a simple state of most programmers habits:
$blog_posts = $orm->getBlogPosts(); // SELECT * FROM blog.post;
foreach ($blog_posts as $blog_post) {
printf(
"The post titled '%s' has been written by '%s'.\n",
$post->getTitle(),
$post->getAuthor()->getName() // SELECT * FROM blog.author WHERE author_id = $*
);
}
The code above emits as many queries in the author table as there are blog posts, this is called a nested loop query and this is a huge performance problem as annoying as hidden because most programmers do not actually see it performs that many queries.
You can do the same in SQL with just one query:
SELECT
title,
published_at,
… -- other fields
a AS author
FROM
blog.post p
LEFT JOIN blog.author a USING (author_id)
This will output lines like:
title | published_at | … | author
Gone with the wind | 2010-04-04 13:43:02… | … | ("Jules Vernes","jules.vernes@gmail.com", … )
Postgres can return a Author row directly in the set of results of the blog posts, this is by far more efficient than the previous solution but this can still cause performance problems if author records are big. It may be interesting to voluntarily limit the amount of author fields fetched in the blog post entities, maybe just the name is enough. Let’s perform such query in the blog post model:
class PostModel extends Model
{
// …
public function getWithAuthor()
{
// HINT: this is bad practice, read below.
$sql = <<<SQL
select P.post_id, P.title, …, A.name AS author_name
from blog.post P
left join blog.author A using (author_id)
SQL;
return $this->query($sql);
}
In the controller, it becomes easy to fetch blog posts with the additional author information:
$blog_posts = $this->get('pomm')['my_session']
->getModel(PostModel::class)
->getWithAuthor();
foreach ($blog_posts as $post) {
printf(
"Post '%s' has been written by '%s'.\n",
$post['title'],
$post['author_name']
);
}
But this method is not very portable since the name of each relation (table) is hardcoded and so is the projection (the fields in the SELECT part). Changing the database structure would ruin this query. Here is a way to circumvent that:
class PostModel extends Model
{
// …
public function getWithAuthor(Where $condition = null)
{
$condition = (new Where)->andWhere($condition); // empty condition and empty condition => true.
$sql = <<<SQL
select {projection}
from {post} P
left join {author} A using (author_id)
where {condition}
SQL;
$projection = $this->createProjection() // use Post fields
->setField('author_name', 'A.name', 'text'); // add a new field with its type
$author_relation = $this->getSession()
->getModel(AuthorModel::class) // get author relation name
->getStructure()
->getRelation();
$sql = strtr( // transform the SQL
$sql,
[
'{projection}' => $projection->formatFieldsWithFieldAlias("P"),
'{post}' => $this->structure->getRelation(),
'{author}' => $author_relation,
'{condition}' => $condition,
]);
return $this->query($sql, $condition->getValues(), $projection);
}
What if we need to fetch all the blog posts since yesterday with their author name?
$blog_posts = $this->get('pomm')['my_session']
->getModel(PostModel::class)
->getWithAuthor(new Where("published_at > $*::timestamptz", [new \DateTime('yesterday')]));
It is also possible to fetch back a whole nested Author instance, just change the projection in the method:
$projection = $this->createProjection()
->setField('author', 'A', 'blog.author');
NOTE: the type of the field above is the table where the row originates. Creating a table in Postgres means creating a type.
In the controller:
foreach ($blog_posts as $post) {
printf(
"Post '%s' has been written by '%s'.\n",
$post['title'],
$post['author']['name']
);
}
Postgres is the ORM.
