RYO blog engine - showing tags for several posts

Question

I am writing yet another blog engine for practice, using SQLite and Perl Dancer framework.

The tables go like this:

CREATE TABLE posts (
    p_id INTEGER PRIMARY KEY,
    p_url VARCHAR(255),
    p_title VARCHAR(255),
    p_text TEXT,
    p_date DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tags (
    t_id INTEGER PRIMARY KEY,
    t_tag VARCHAR(255),
    t_url VARCHAR(255)
);

CREATE TABLE tags_posts_junction (
    tp_tag INTEGER NOT NULL,
    tp_post INTEGER NOT NULL,
    FOREIGN KEY(tp_tag) REFERENCES tags.t_id,
    FOREIGN KEY(tp_post) REFERENCES tags.p_id
);

All the big guys like Wordpress (or stackoverflow) can show tags right on the main page, after each question, and I'd like to implement it too. The question is how do I do it.

So far the posts are stored in the database, and when I need to render a page showing latest 20 posts I pass a hash refernece (fetchall_hashref from DBI) to the template. So how do I add tags there? Of course I can do something like

my $dbh = database->prepare('SELECT * FROM posts ORDER BY p_date DESC 
                    LIMIT 20 OFFSET 0');
$dbh->execute;
my $posts = $dbh->fetchall_hashref('p_date');
foreach my $key (keys $post) {
    my $dbh = database->prepare('SELECT * FROM tags WHERE t_id IN (
                    SELECT tp_tag FROM tags_posts_junction WHERE tp_post = ?)');
    $dbh->execute($post->{"$key"}->{"p_id"});
    my $tags = $dbh->fetchall_hashref(t_id);
    $post->{"$key"}->{"$tag_hash"} = $tags;
};

But that's ugly and that's 20 more queries per page, isn't it too much? I think there should be a better way.

So the question is how do I get tags for 20 posts the least redundant way?

Answer 1

Collect all the p_ids into an array and construct your query using IN instead of =, something like this, presuming @pids is your array:

my $dbh = database->prepare('SELECT * FROM tags WHERE t_id IN (
                    SELECT tp_tag FROM tags_posts_junction WHERE tp_post IN (' .
                    join(', ', ('?')x@pids).') )');
$dbh->execute(@pids);

Though you should really look to JOINs to replace your sub-queries.