没错,我对此并不特别熟练,这实际上是我的第一个联接查询,所以请小心。我会尽可能多地提供细节,因为它可能会像煎锅一样打在你们大多数人的脸上,但它会让我抓狂!
我有一个问题,查询一个博客,我正试图写在代码点火器。我为帖子设置了一个带有2个联接的查询,它的类别使用了三个表:posts、categories和posts_categories。现在我也在尝试联接我的评论表,以进行计数。
这是我的模型中的代码,它显示了我编写的两个通用帖子:
$this->db->select('posts.id,
posts.title,
posts.slug,
posts.content,
posts.author,
posts.date,
posts.time,
posts.tags,
posts.status,
GROUP_CONCAT(categories.name SEPARATOR ''-'') AS categories
');
$this->db->group_by(array('posts.id'));
$this->db->from('posts');
$this->db->join('posts_categories', 'posts_categories.blog_entry_id = posts.id');
$this->db->join('categories', 'posts_categories.blog_category_id = categories.category_id');
$query = $this->db->get();
return $query->result_array();
结果是:
(
[0] => Array
(
[id] => 1
[title] => My first blog post!
[slug] => my-first-blog-post
[content] => This is my first blog post. Don't worry, it's just a test, my real blog won't be this boring, hopefully!
[author] => Joni
[date] => 2012-01-23
[time] => 00:00:00
[tags] => Testing
[status] =>
[categories] => Testing-More Tests-Test
)
[1] => Array
(
[id] => 2
[title] => This is another test-post
[slug] => this-is-another-test-post
[content] => Well you guessed it. another boring test post, enjoy!
[author] => Joni
[date] => 2012-01-23
[time] => 00:00:00
[tags] => Sexy
[status] =>
[categories] => Test
)
)
现在,当我修改查询以实现注释的第三个联接时,如下所示:
$this->db->select('posts.id,
posts.title,
posts.slug,
posts.content,
posts.author,
posts.date,
posts.time,
posts.tags,
posts.status,
GROUP_CONCAT(categories.name SEPARATOR ''-'') AS categories,
count(comments.id) as total_comments
');
$this->db->group_by(array('posts.id'));
$this->db->from('posts');
$this->db->join('posts_categories', 'posts_categories.blog_entry_id = posts.id');
$this->db->join('categories', 'posts_categories.blog_category_id = categories.category_id');
$this->db->join('comments', 'comments.post_id = posts.id');
$query = $this->db->get();
return $query->result_array();
我最终得到了这个
(
[0] => Array
(
[id] => 1
[title] => My first blog post!
[slug] => my-first-blog-post
[content] => This is my first blog post. Don't worry, it's just a test, my real blog won't be this boring, hopefully!
[author] => Joni
[date] => 2012-01-23
[time] => 00:00:00
[tags] => Testing
[status] =>
[categories] => Testing-More Tests-Test
[total_comments] => 3
)
)
如果你已经走到了这一步,很抱歉这么长时间了,我只想提前说声谢谢!
欢呼joni
你需要使用LEFT OUTER JOIN,否则你只会得到有注释的帖子。当您执行INNER JOIN(默认)时,它将要求左边的任何东西在连接的右边都有一个匹配的元素。如果在右侧找不到匹配项,它就会忽略它。LEFT OUTER JOIN将保留连接左侧的所有元素,而不管右侧是否有匹配项。
更改此项:
$this->db->join('comments', 'comments.post_id = posts.id');
至
$this->db->join('comments', 'comments.post_id = posts.id', 'left outer' );
->join
做什么?如果它在进行内部联接,你的问题可能是它会排除没有任何评论的帖子。你需要在那里使用左外部联接,以确保没有评论的帖子被包括在内。
通过快速查看您的查询,我假设您的第二篇文章没有任何评论,当您尝试加入评论时,它不会收到第二篇帖子。