Tag finder: Tags... amplifier application bd blog book computing dailymotion debian dev electronic forum friends geek git github javascript jquery linux net nodejs ooc php pomm postgresql python screen shell silex speaker store symfony ubuntu valve vim whitepaper youtube

This is the second part of the The PommBundle Tutorial where we were wondering how to display only published articles on the homepage. You can find the git repo of this tutorial on github .

The published articles

Unpublished articles do not exist

Of course, the good old way to retreive only published data would be to create a findPublishedArticles method in our Article class.

#src/NssWebsite/BlogBundle/Model/ArticleMap.php
[...]

public function findPublished()
{
    return $this->findWhere('published_at IS NOT NULL');
}

but, think about this: it is very likely we are going to want to have a lot of list of published articles: «hot topics», «last articles» and more. All of theses queries have several things in common: they want published articles only, They do not care about the whole content of each article, a short excerpt will be enough. If we create a view that fits that conditions, we can create a PublishedArticleMap class that uses this view knowing we will never INSERT, UPDATE or DELETE a PublishedArticle.

nss_db=> CREATE VIEW published_article AS SELECT * FROM article WHERE published_at IS NOT NULL ORDER BY published_at;
CREATE VIEW
nss_db=> SELECT slug, author, published_at FROM published_article;
                 slug                  |    author     |        published_at        
---------------------------------------+---------------+----------------------------
 this-is-my-first-article-the-begining | Homer Simpson | 2011-03-25 00:01:53.236646
 this-is-a-second-article              | Lisa Simpson  | 2011-03-27 00:01:53.236646
(2 rows)

Keep in mind that using ORDER BY in our VIEW will sort all the rows even if you query your view specifying a new ORDER BY. This is very minimal impact but still is. Using this class for the public area of the site means you will never access the unpublished data neither you will write or modify data there. So far so good !

Let’s define our PublishedArticleMap class:

<?php #src/NssWebsite/BlogBundle/Model/PublishedArticle.php

namespace NssWebsiteBlogBundleModel;

use PommModelMapBaseNssBlogArticleMap;

class PublishedArticleMap extends BaseNssBlogArticleMap
{
    public function initialize()
    {
        parent::initialize();

        $this->object_class = 'NssWebsiteBlogBundleModelArticle';
        $this->object_name = 'nss_blog.published_article';
    }
}

And in our Controller we can now just:

#src/NssWebsite/BlogBundle/Controller/DefaultController.php
    public function indexAction()
    {
        $articles = $this->get('pomm')
            ->getConnection()
            ->getMapFor('NssWebsiteBlogBundleModelPublishedArticle')
            ->findAll();

        return array('articles' => $articles);
    }

Of course, the PublishedArticle class does not exist as our PublishedArticleMap class only returns Article collection instances.

We do not need content!

Instead of querying the database for the content of all articles and then throw away 99% of the data because we do not need it, let’s just query exactly what we need. Pomm uses the getSelectFields internaly to query the database. Overloading this method will change the way Pomm performs its findAll findWhere and findByPk methods:

#src/NssWebsite/BlogBundle/Model/PublishedArticleMap.php

    public function getSelectFields($alias = '', $size = 30)
    {
        $fields = array();
        $alias = $alias === '' ? $alias : $alias.'.';

        foreach($this->field_definitions as $name => $type) {
            if ($name !== 'content') {
                $fields[] = sprintf("%s%s", $alias, $name);
            } else {
                $fields[] = sprintf("cut_nicely(%s%s, %d) AS excerpt", $alias, $name, $size);
            }
        }

        return $fields;
    }

In our template, the excerpt new field will be accesses like any other:

<div class="entry"><p>{{ article.getExcerpt }} ... Read more !</p></div>

We want comments count

Yes we want and findAll is not going to give it to us. So this time, we have to create a custom function for that… unless. Oh wait ! What about changing the view so it would give it to us ?

nss_db=> CREATE OR REPLACE VIEW published_article AS 
SELECT 
     a.slug, 
     a.title, 
     a.author, 
     a.content, 
     a.created_at, 
     a.updated_at, 
     a.published_at, 
     count(c.id) AS comment_count 
FROM article a 
        LEFT JOIN comment c ON a.slug = c.article_slug 
WHERE 
        published_at IS NOT NULL 
GROUP BY 
a.slug, 
     a.title, 
     a.author, 
     a.content, 
     a.created_at, 
     a.updated_at, 
     a.published_at 
ORDER BY published_at
;

We need to tell our PublishedArticleMap the change:

#src/NssWebsite/BlogBundle/Model/PublishedArticleMap.php
    public function initialize()
    {
        [...]
        $this->addField('comment_count', 'IntType');

Show must go on

Let’s now write the show action that retreive one article from its slug.

#src/NssWebsite/BlogBundle/Controller/DefaultController.php
    /**
     * @extra:Route("/article/{slug}", name="article_show")
     * @extra:Template("NssWebsiteBlogBundle:Default:show.html.twig")
     **/
    public function showAction($slug)
    {
        $article = $this->get('pomm')
            ->getConnection()
            ->getMapFor('NssWebsiteBlogBundleModelPublishedArticle')
            ->findByPk(array('slug' => $slug));

        return array('article' => $article);
    }

Oh oh … As we have overloaded the method getSelectFields the findByPk method will return an Article with an excerpt instead of the real content of our article. We need to write our own findByPk in the PublishedArticleMap class:

#src/NssWebsite/BlogBundle/Model/PublishedArticleMap.php
    public function findByPk(Array $pk)
    {
        $fields = join(', ', array_keys($this->field_definitions));
        $sql = sprintf("SELECT %s FROM %s WHERE slug=?", $fields, $this->object_name);
        $collection = $this->query($sql, $pk);

        return $collection->isEmpty() ? null : $collection[0];
    }

Of course, we need to display the article’s comments underneath the content:

#src/NssWebsite/BlogBundle/Controller/DefaultController.php
    /**
     * @extra:Route("/article/{slug}", name="article_show")
     * @extra:Template("NssWebsiteBlogBundle:Default:show.html.twig")
     **/
    public function showAction($slug)
    {
        $connection = $this->get('pomm')->getConnection();
        $article = $connection->getMapFor('NssWebsiteBlogBundleModelPublishedArticle')
            ->findByPk(array('slug' => $slug));

        $comments = $connection->getMapFor('NssWebsiteBlogBundleModelComment')
            ->findWhere('article_slug=?', array($article->getSlug()));

        return array('article' => $article, 'comments' => $comments);
    }

You will have the same error we had last time about the CommentMap not finding NssBlogComment, I let you add the initialize method in your CommentMap class. That’s all for today, next time we will add a comment. Take care.