Blog Database Structure - Categories & Meta Tags

[eluser]jedd[/eluser]
[quote author="lenwood" date="1272921118"]
This is a conceptual shift for me. Being new to building a dynamically generated website, I was just doing this the way that made the most sense to me. Can anyone comment on why I'd want to use one vs the other?
[/quote]

Can you describe in more detail the two different approaches? It sounds like they're the same (but I might have misread this last message of yours).

In general you want to minimise database calls - single calls are typically less strain on the database than multiple calls, even if you end up with the same quantity of data at the end.

[eluser]lenwood[/eluser]
[quote author="jedd" date="1272927279"]Can you describe in more detail the two different approaches?[/quote]

If I understand dmorin's post, he's loading all data within the posts, comments & categories tables, he does some processing to determine which category names are associated with each post, then generates a follow up query with LEFT JOIN statements that loads all of this data into an array.

As I was coding over the weekend, I already had code that associates comments with each post_id working in a foreach statement. My intent was to query the cat_id's associated with each post_id and finally query the category names. I'm not using any join statements, I'm trying to add the category names to an array that's already created.

I wasn't able to get my code to work. This morning I came back and read through this again and realized that maybe I was looking at it the wrong way.

As I'm typing this out I realize that perhaps the only difference is that I'm not using join statements. I may try that when I get home tonight.

[eluser]dmorin[/eluser]
My post wasn't supposed to show queries that I would use in a blog but to instead show how to write queries for the different tables and relationships that were created.

If you can't figure out the right queries, write back and let us know what type of page you're trying to create and we'll help by writing out the queries we would use. In general , I wouldn't recommend getting posts and comments in the same query since it would return the post data once for each comment (because of the join) which could result in a lot of extra data being transferred. Instead, I would get the post in one query, all of the categories in a second (using a join) and all of the comments in a third.

[eluser]lenwood[/eluser]
I figured it out. Somehow it just made sense to me when I sat down at my computer tonight.

I'm calling the category names in two views, a listing of all of my blog entries and on the individual posts. Here's my code:

Listing of all blog entries:

Code:

function list_all_blog_entries()
{
    $this->db->select("*, date_format(date_time, '%W, %M %e, %Y') as date", FALSE);
    $this->db->order_by('date_time', 'desc');
    $this->db->join('post_cat', 'post_cat.entry_id = entries.id');
    $this->db->join('categories', 'categories.id = post_cat.cat_id');
    $query = $this->db->get('entries');

    if($query->num_rows() > 0) {
        foreach ($query->result() as $row) {
            $this->db->like('entry_id', $row->id);
            $row->num_comm = $this->db->count_all_results('comments');
            $data[] = $row;
        }
    }
    return $query->result();
}

Single blog post

Code:

function single()
{
    $this->db->select("*, date_format(date_time, '%W, %M %e, %Y') as date, date_format(date_time, '%l:%i %p') as time", FALSE);
    $this->db->where('post_slug', $this->uri->segment(3));
    $this->db->join('post_cat', 'post_cat.entry_id = entries.id');
    $this->db->join('categories', 'categories.id = post_cat.cat_id');
    $query = $this->db->get('entries');
    
    if($query->num_rows() > 0) {
        $row = $query->row_array();    
        return $row;
    }
}

This code works, I'm able to call the category names directly. All feedback on my code is welcome. I'll take this as a sign that there's value in slogging through something that you don't understand, trusting that it will become clear at some point.

[eluser]lenwood[/eluser]
I just realized that there's a mistake here. When displaying the blog entry, it will only show one category. If there's more than one then it displays the blog entry once per category (2 categories makes it show up twice, etc). Is there a way to make this query store all of the category names for a given post in the same array?

[eluser]jedd[/eluser]
While I understand dmorin's logic, it may be useful to know here how much data you anticipate pulling out in total with each pass. It's possible that it'd be more elegant to simply pull everything you need in a single query.

An updated and annotated schema would be handy at this point.

[eluser]dmorin[/eluser]

While I understand dmorin’s logic, it may be useful to know here how much data you anticipate pulling out in total with each pass. It’s possible that it’d be more elegant to simply pull everything you need in a single query.

Normally I'd agree that use case is important to understanding how it should be setup, but in this case, it's a blog, we know the use case. Joining comments and posts is a terrible idea. If you get one long post that goes viral and gets 250 comments, that is going to be a terrible SQL query. At the same time, it doesn't allow you to do anything like pagination of comments. Unless your db is on a different network and the connect latency is enough that you don't want to hit it multiple times, it's no more effective (and often even less so when you're at scale) to do joins rather than multiple queries.

Edit:
When I wrote this, I assumed you were talking about the single post results and not the multi-posts per page results. For the multi-page where he's only getting a count of the comments, I agree the a join may be better.

[eluser]dmorin[/eluser]

I just realized that there’s a mistake here. When displaying the blog entry, it will only show one category. If there’s more than one then it displays the blog entry once per category (2 categories makes it show up twice, etc). Is there a way to make this query store all of the category names for a given post in the same array?

That's what joins do in one to many. They include the one portion in each row for the many. SO you have two options. If you want to keep using the join, you'll have to loop through all of the items in the array and check for the same post id, and if they are the same, track that and display them all instead of displaying the post again. The option option is to just do separate queries, like you're doing for comments.

Code:

$this->db->like('entry_id', $row->id);

Why are you doing "like" here? when comparing ids, you should use a straight "column = value".

If you just need to get the number of comments for your multi-post page, you can do that with a join also.

Code:

$this->db->select("*, date_format(date_time, '%W, %M %e, %Y') as date, count(comments.id) as num_comments", FALSE);
    $this->db->from('entries');
    $this->db->join('post_cat', 'post_cat.entry_id = entries.id');
    $this->db->join('categories', 'categories.id = post_cat.cat_id');
    $this->db->join('comments', 'entries.id = comments.entry_id', 'left');
    $this->db->group_by('entries.id, post_cat.cat_id');
    $this->db->order_by('date_time', 'desc');
    $query = $this->db->get();

    return $query->result();

It's not a very efficient query since the join will end up being huge in some cases, so you'll have to benchmark to see if it's worth it.

[eluser]danmontgomery[/eluser]
Or, use group_concat to display multiple categories

[eluser]dmorin[/eluser]

Or, use group_concat to display multiple categories

As long as you don't want/need to turn each category into a link back to the category page, that's a great solution.