我有这个模型：
public function index_loop() {
$post_user = $this->db->query("SELECT posts.post_title, posts.post_content, posts.post_date, users.username FROM posts LEFT JOIN users ON posts.user_id = users.user_id ORDER BY posts.post_ID DESC");
//$categories = $this->db->query("SELECT categories.cat_name, categories.cat_id FROM
//$comments = $this->db->query("SELECT COUNT(comment_id) FROM comments WHERE
return $post_user->result_array();
}
我需要的是显示每个帖子和评论的类别（尽管我猜如果我发现传递的类别比评论的类别是相同的方式）
在视图文件中：
<?php foreach($posts as $zz) { ?>
<div class="article">
<h2><?php echo $zz['post_title']; ?></h2>
<p>Posted by <a href=""><?php echo $zz['username']; ?></a> | Filed under <a href="#">templates</a>, <a href=>internet</a></p>
<p><?php echo $zz['post_content']; ?></p>
<p><a href=>Read more</a> | <a href=>Comments (5)</a> | <?php echo $zz['post_date']; ?></p>
</div> <?php } ?>
所以，如果我想在每个博客上循环类别，我需要那个博客ID，如果我从模型中进行所有查询，我该如何获取它？
评论也一样
我做一个大型复杂的数据库查询（这很难，但我可以做到）或者我可以做两个或三个单独的较小的查询，这是好的吗？
最佳答案
codeigniter允许您将数据库结果作为对象（例如，模型对象）返回，这使数据更易于使用。您可以向Posts表发出初始查询，在结果集中包含posts.id字段，并将Post_model类的名称传递给$db->query->result()函数，以告诉codeigniter您希望将结果作为Post_model类的实例返回。
然后，您可以在Post_model类上定义方法，将其分别定义为GetCategoriesbypost_id和GetCommentsbypost_id，然后调用这些方法为查询返回的每个$categories填充$comments和Post_model集合。
下面是一个例子，我希望它能有所帮助：
public class Post_model extends CI_Model
{
// All the properties in the Posts table, as well as a couple variables to hold the categories and comments for this Post:
public $id;
public $post_title;
public $post_content;
public $post_date;
public $username;
public $categories;
public $comments;
public function index_loop()
{
return $this->GetAllPosts();
}
// function to get all posts from the database, including comments and categories.
// returns an array of Post_model objects
public function GetAllPosts()
{
// define an empty array to hold the results of you query.
$all_posts = array();
// define your sql query. NOTE the POSTS.ID field has been added to the field list
$sql = "SELECT posts.id,
posts.post_title,
posts.post_content,
posts.post_date,
users.username
FROM posts LEFT JOIN users ON posts.user_id = users.user_id
ORDER BY posts.post_id DESC";
// issue the query
$query = $this->db->query($sql);
// loop through the query results, passing a string to result() which represents a class to instantiate
//for each result object (note: this class must be loaded)
foreach($query->result("Post_model") as $post)
{
$post->categories = $this->GetPostCategories($post->id);
$post->comments = $this->GetPostComments($post->id);
$all_posts[] = $post;
}
return $all_posts;
}
// function to return categories for a given post_id.
// returns an array of Category_model objects.
public function GetPostCategories($post_id)
{
$sql = "SELECT category.id, ... WHERE post_id = ?";
$query = $this->db->query($sql, array($post_id));
$categories = array();
foreach($query->result("Category_model") as $category)
{
$categories[] = $category;
}
return $categories;
}
// function to return comments for a given post_id.
//returns an array of Comment_model objects
public function GetPostComments($post_id)
{
$sql = "SELECT comment.id, ... WHERE post_id = ?";
$query = $this->db->query($sql, array($post_id));
$comments = array();
foreach($query->result("Comment_model") as $comment)
{
$comments[] = $comment;
}
return $comments;
}
}
然后，在您的视图中，您可以将$posts数组作为post-model对象而不是result-array访问：
<?php foreach($posts as $zz) { ?>
<div class="article">
<h2><?php echo $zz->post_title; ?></h2>
<p>Posted by <a href=""><?php echo $zz->username; ?></a> |
Filed under
<?php foreach($zz->categories as $category) {
echo '<a href="#">{$category->name}</a>, ';
}
?>
</p>
<p><?php echo $zz->post_content; ?></p>
<p><a href=>Read more</a> | <a href=>Comments (5)</a> | <?php echo $zz->post_date; ?></p>
</div> <?php } ?>
至于效率问题，它将取决于很多因素（数据库是否与web服务器位于同一台机器上？有多少个职位？等等）。单个大型查询的执行速度通常比几个较小的查询快，但它需要进行分析以确定性能提升是否值得追求。我总是喜欢尝试编写可读/可理解的代码，而不是以增加复杂性为代价进行优化。