How to *use* queries properly in model?

[eluser]djuric[/eluser]
Hey everyone,

I have problem with using and pulling information from database with Codeignater. So, I'm making simple blog.
This blog of mine has posts, has categories, tags, comments and users.

Here is database just for reference http://pastebin.com/p15dqAhC

What I currently have in model is this:

Code:

<?php

class Clean_blue_model extends CI_Model {

  public function __construct() {
   parent::__construct();
   $this->load->database();
}



public function index_loop() {
  $post_user = $this->db->query("SELECT posts.post_title, posts.post_content, posts.post_date, users.username, posts.post_id 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();
}

see how I commented out $categories and $comments lol. That's the problem here.

I want to show posts in index page in format like this:

Post title
Post content
User
Date Posted
Categories
Number of comments


And to explain actual problem as simple as possible:

I can't write this long query. I believe it's possible but I don't want to. I would need to do this To select Post Content, Post title, Date Posted which is in ONE table, Categories which are in TWO tables ( one linking with posts and another one with cat_id and cat_name), User which is in his ONE table, and number of comments in ONE table.
So it's actually I would have to span query across 5 tables.

To loop categories in home page for EACH post I need that posts ID. How can I refer to that ID and where? In model, how to run multiple queries, to run one query for puling ID just to use it to loop categories for each post?

I hope I didn't make this more complicated than it is. Thanks for help

[eluser]Rodrigo Alexandre[/eluser]
http://ellislab.com/codeigniter/user-gui...tml#select

search for join

[eluser]Aken[/eluser]
You can either:

1) Create one fancy model method with a complicated join query to get the data you want.
Pros: Single query for performance. Cons: More complicated.

2) Create individual methods for each part (post data, author data, categories, comments). Then use one method to call all your data and put it together.
Pros: Separated content, good if you want to use individual parts elsewhere, too. Cons: More queries is worse for performance; code is more spread out, so finding bugs might suck.

[eluser]djuric[/eluser]
ok @Aken thanks a lot for this

I choose option 2

Can you please, just for the sake of better understanding, write 2 methods, how to get Posts and it's categories from database ( you can make up your own database doesn't matter)
and your view of view file ( controller just for passing values I guess)


I will be very very grateful

[eluser]Aken[/eluser]
Unfortunately no. Maybe someone else can assist with that. I suggest searching around for various CodeIgniter tutorials. You'll see plenty of examples.