Display posts according to category ID from pivot table in CodeIgniter

Question

I currently have three tables like this :

ci_posts: id, title, slug, content.

ci_terms: term_id, title, slug.

ci_relationship: id, post_id, term_id

I'm trying to retrieve all the posts according to the specific clicked category.

This is my method that I'm using in my model but I'm unable to make it work as I want:

public function get_posts_category($id){

    $this->db->select('*');
    $this->db->from($this->table);
    $this->db->join('ci_relationship', 'ci_relationship.post_id = ci_posts.id', 'INNER');
    //$this->db->join('ci_users', 'ci_users.id = ci_relationship.id', 'INNER');
    //$this->db->join('ci_terms', 'ci_relationship.term_id = ci_terms.term_id', 'INNER');
    $this->db->order_by('post_id', 'DESC');
    $this->db->group_by('post_id');
    //$this->db->where('type', 'category');
    $this->db->where('term_id', $id);

    $query = $this->db->get();

    if($query->num_rows() >= 1){
        return $query->result();
    } else {
        return false;
    }

}

As in the snippet above the $this->db->where('term_id', $id) should make it possible to display all the posts that match the clicked category which is stored in the ci_relationship table and created in the ci_terms table but the current output shows nothing even when there are several ci_posts and ci_terms related to each other in the ci_relationship table.

Can somebody explain me where my mistake is?

UPDATE THIS IS HOW I ACCESS TO THEM:

<?php if($categories) : ?>
  <div class="panel panel-default">
    <div class="panel-heading">
      <h1 class="panel-title">Categories</h1>
    </div>
    <div class="panel-body">
      <?php foreach($categories as $cats) : ?>
        <a href="<?= base_url('posts/category/'.get_category_slug($cats->term_id)) ?>"><span class="label label-orange"><?= get_category($cats->term_id); ?></span></a>
      <?php endforeach; ?>
    </div>
  </div>
<?php endif; ?>

This is my category method in my Post controller:

    public function category($id){

        $data['posts'] = $this->Post_model->get_posts_category($id);
        $category = $this->Terms_model->get($id);

        // Get Categories
        $data['categories'] = $this->Post_model->get_categories();

        // Meta
//      $data['title']  = $this->settings->title.' | '. ucfirst($category->title);

        // Load template
        $this->template->load('public', 'default', 'posts/category', $data);
    }

This is the method in the controller to get the tags when they exist on ci_relationship table below :

// Get categories
public function get_categories(){

    $this->db->select('*');
    $this->db->from('ci_relationship');
    $this->db->group_by('term_id');
    $this->db->where('type', 'category');

    $query = $this->db->get();

    if($query->num_rows() >= 1){
        return $query->result();
    } else {
        return false;
    }

}

but the above code is just to displayed, the actual function to display the posts according to the category clicked is still the get_posts_category method.

thanks in advance

Answer 1

Hope this will help you :

Your get_posts_category should be like this :

public function get_posts_category($id)
{

    $this->db->select('*, count(ci_posts.id)');
    $this->db->from('ci_posts');
    $this->db->join('ci_relationship', 'ci_relationship.post_id = ci_posts.id');
    //$this->db->join('ci_users', 'ci_users.id = ci_relationship.id', 'INNER');
    //$this->db->join('ci_terms', 'ci_relationship.term_id = ci_terms.term_id', 'INNER');
    $this->db->order_by('ci_posts.id', 'DESC');
    $this->db->group_by('ci_posts.id');
    //$this->db->where('type', 'category');
    $this->db->where('ci_relationship.term_id', $id);

    $query = $this->db->get();

    if($query->num_rows() > 0)
    {
        return $query->result();
    } 
    else 
    {
        return false;
    }
}