Building a website from scratch: Adding Tags (Part 3/?)

As always, this blog post is a bit late to when I wrote the code for it.

Adding tags seems like a minor thing to do, but it did require me to rewrite some parts of my code, so I'll write here about why that is.


Previously I had only a simple table called posts. It contained all data of a single post, which was url, title, date of creation and last update, and the content.

Now I need to add tags to each post. I could have just added a column tags to each post containing a list of tags, but that should ring the alarm bells of anyone who has knowledge of databases. If I want to add a list of tags, it is convenient for inserting and reading posts. However, if I would want to create an overview of all posts with a certain tag, this would be rather cumbersome, as I'd need to query all posts and check if that tag is contained in the tag list.

A better approach is to create a table storing the relation of post to tag, as it really is that.

CREATE TABLE tags (
    tag varchar,
    url varchar,
    PRIMARY KEY (tag, url)
);

This way, reading tags is easy from both sides. To find the tags for the post 'example_post':

SELECT tag FROM tags WHERE url = 'example_post';

To find the posts for the tag 'example_tag':

SELECT url FROM tags WHERE tag = 'example_tag';

This required me to redo the way I edit posts. Previously I had one struct which could be deserialized from yaml post metadata and then directly inserted into the posts table with the help of the Deserialize and Insertable derives.

#[derive(Queryable, Insertable, Serialize, Deserialize, Debug, PartialEq, Eq)]
#[table_name = "posts"]
pub struct Post {
    #[serde(skip, default)]
    pub url: String,
    pub title: String,
    #[serde(default)]
    pub version: String,
    pub published: bool,
    #[serde(skip, default = "now")]
    pub created: NaiveDateTime,
    #[serde(skip, default = "now")]
    pub updated: NaiveDateTime,
    #[serde(skip, default)]
    pub content: String,
}

Now, with the addition of a tag list in the metadata, it can still be deserialized easily, but inserting it into the database is harder.

#[derive(Serialize, Deserialize, Debug)]
pub struct PostMeta {
    pub title: String,
    #[serde(default)]
    pub version: String,
    pub published: bool,
    #[serde(default)]
    pub tags: Vec<String>,
}

For insertion, I wrote a write_to_db function, which takes a metadata struct, an url, a database connection, and writes it to the database. It does so by copying most of the metadata into the posts table, but creating tuples between tags and the post url, and inserting that into the tags table.

pub fn write_to_db(self, name: &str, db: &PgConnection) -> AResult<()> {
    let orig_post = models::Post::load_from_db(name, db);

    let edited = models::Post {
        url: name.into(),
        created: orig_post.map(|p| p.created).unwrap_or(models::now()),
        updated: models::now(),
        title: self.meta.title,
        version: self.meta.version,
        published: self.meta.published,
        content: self.content,
    };

    use diesel::dsl::*;

    {
        // inserting or updating the post into the posts table
        use crate::schema::posts::dsl::*;
        delete(posts.filter(url.eq(name))).execute(db)?;
        insert_into(schema::posts::table)
            .values(edited)
            .execute(db)?;
    }

    {
        use crate::schema::tags::dsl::*;

        // creating a vector of tag tuples based on the tag list in the metadata
        let tag_tuples = self
            .meta
            .tags
            .into_iter()
            .map(|t| models::Tag {
                tag: t,
                url: name.into(),
            })
            .collect::<Vec<_>>();

        // updating the set of tags the post has
        delete(tags.filter(url.eq(name))).execute(db)?;
        insert_into(tags).values(&tag_tuples).execute(db)?;
    }

    Ok(())
}

Now it is also easy to query all posts with a common tag!

I also created a tags_meta table, which just contains the description for each tag.

This is the code needed to render a tag overview page. Some functions are shared with the other parts of the rendering, for example create_table is also used by the overview page, and render_markdown is used in the posts.

pub fn tag(name: &str, db: &PgConnection) -> AResult<String> {
    use crate::schema::posts::dsl as p;
    use crate::schema::tags::dsl as t;
    use crate::schema::tags_meta::dsl as m;

    let sites = t::tags
        .inner_join(p::posts.on(p::url.eq(t::url)))
        .filter(p::published)
        .filter(t::tag.eq(name))
        .order_by(p::created.desc())
        .select((p::title, p::url, p::created))
        .load(db)?;

    let description = m::tags_meta
        .filter(m::tag.eq(name))
        .select(m::description)
        .load::<String>(db)?;
    let description = description
        .first()
        .ok_or("not in meta table, something is wrong with db...")?;

    let title = format!("Posts with tag {}", name.to_uppercase());

    let mut body = format!("<h1>{}</h1>", title);
    body += &render_markdown(&description);
    body += &create_table(&sites);

    let page = format!(
        include_str!("skeleton.html"),
        title = title,
        body = body,
        bottom_navigation = "",
        copyright = ""
    );
    Ok(page)
}

This is now pretty much the current state of how tags work on this site.

The source code for it can finally be found on my GitHub profile, right here.

If I skipped over important things in this post, or if it bored you to death, let me know!


Tags: WEBSITE CODING