在多个表上使用一对多构造MySQL查询时遇到问题

Question

I have a blog-style app that allows users to tag each post with topics. I keep this data in three separate tables: posts (for the actual blog posts), topics (for the various tags), and posts_topics (a table that stores the relationships between the two).

In order to keep the MVC structure (I'm using Codeigniter) as clean as possible, I'd like to run one MySQL query that grabs all the post data and associated topic data and returns it in one array or object. So far, I'm not having any luck.

The table structure is like this:

Posts
+--------+---------------+------------+-----------+--------------+---------------+
|post_id | post_user_id  | post_title | post_body | post_created | post_modified |
+--------+---------------+------------+-----------+--------------+---------------+
|     1  |    1          | Post 1     |  Body 1   |  00-00-00    |  00-00-00     |
|     2  |    1          | Post 1     |  Body 1   |  00-00-00    |  00-00-00     |
+--------+---------------+------------+-----------+--------------+---------------+

// this table governs relationships between posts and topics
Posts_topics
+--------------+---------------------+-------------------------+-----------------+
|post_topic_id | post_topic_post_id  | post_topic_topic_id | post_topic_created  | 
+--------------+---------------------+-------------------------+-----------------+
|     1        |    1                | 1                   |         00-00-00    |  
|     2        |    1                | 2                   |         00-00-00    |
|     3        |    2                | 2                   |         00-00-00    |  
|     4        |    2                | 3                   |         00-00-00    |   
+--------------+---------------------+-------------------------+-----------------+

Topics
+---------+-------------+-----------+----------------+
|topic_id | topic_name  | topic_num | topic_modified |
+---------+-------------+-----------+----------------+
|     1   |  Politics   | 1         | 00-00-00       |
|     2   |  Religion   | 2         | 00-00-00       |
|     3   |  Sports     | 1         | 00-00-00       |
+---------+-------------+-----------+----------------+

I have tried this simple query with n success:

select * from posts as p inner join posts_topics as pt on pt.post_topic_post_id = post_id join topics as t on t.topic_id = pt.post_topic_topic id

I've also tried using GROUP_CONCAT, but that gives me two problems: 1) I need all the fields from Topics, not just the names, and 2) I have a glitch in my MySQL so all GROUP_CONCAT data is returned as a BLOB (see here).

I'm also open to hearing any suggestions where I run two queries and try to build out an array for each result; I tried that with the code below but failed (this code also includes joining the user table, which would be great to keep that as well):

    $this->db->select('u.username,u.id,s.*');
    $this->db->from('posts as p');
    $this->db->join('users as u', 'u.id = s.post_user_id');
    $this->db->order_by('post_modified', 'desc');
    $query = $this->db->get();
    if($query->num_rows() > 0)
    {
        $posts = $query->result_array();
        foreach($posts as $p)
        {
            $this->db->from('posts_topics as p');
            $this->db->join('topics as t','t.topic_id = p.post_topic_topic_id');
            $this->db->where('p.post_topic_post_id',$p['post_id']);
            $this->db->order_by('t.topic_name','asc');
            $query = $this->db->get();
            if($query->num_rows() > 0)
            {
                foreach($query->result_array() as $t)
                {
                    $p['topics'][$t['topic_name']] = $t;
                }
            } 
        }
        return $posts;
    }

Any help greatly appreciated.

Answer 1

This query should do the trick. Just change the * to the field list you desire so you are not pulling excess data every time you run the query.

Select
  *
FROM
  posts,
  post_topics,
  topics
WHERE
  post_topic_topic_id = topic_id AND
  post_topic_post_id = post_id
 ORDER BY
 post_id, topic_id;

Select
  *
FROM
  posts,
  post_topics,
  topics,
  users
WHERE
  post_topic_topic_id = topic_id AND
  post_topic_post_id = post_id AND
  post_user_id = user_id
 ORDER BY
 post_id, topic_id;