CodeIgniter Forums
Getting the number of images of an item : Join and Count - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forum-5.html)
+--- Forum: General Help (https://forum.codeigniter.com/forum-24.html)
+--- Thread: Getting the number of images of an item : Join and Count (/thread-72788.html)



Getting the number of images of an item : Join and Count - demyr - 02-12-2019

Hi there,

As you may guess from topic title I have got 2 tables : (let's say) posts and post_images.

POSTS TABLE consists of:
     post_id - posts_name - post_text - post_date


POST_IMAGES consists of:
    post_image_id - post_id - post_url

And on my admin panel I have got a page which lists posts as a table (as a result of this Model and Controller).

Model: 

PHP Code:
function my_posts(){
 
   $mp $this->db->SELECT('*')
 
   ->from('posts')
 
   ->get()
 
   ->result();

 
   return $mp;
 
      

Controller:

PHP Code:
public function posts(){    

    $this
->load->model('Backend_model');
 
   $data['posts'] = $this->Backend_model->my_posts();
 
   $this->load->view('admin/posts'$data);
 
 

result page :

post_name   -   post_date    -    how-many-images-does-this-post-have
 example1        01.12.2019                   ??????
 example2        02.12.2019                   ??????

How can I get the image number of each blog post?

P.S. I have tried lots of examples on stackoverfow and here about joins-counts and grouping even, however as I couldn't succeed it, I am posting here as plain as possible

Thanks in advance


RE: Getting the number of images of an item : Join and Count - neuron - 02-12-2019

PHP Code:
function my_posts(){
 
   $mp $this->db->SELECT('p.*, count(pi.post_image_id) as image_count')
 
   ->from('posts as p')
    ->
join('post_images as pi''pi.post_id = p.post_id''LEFT')
    ->
group_by('p.post_id')
 
   ->get()
 
   ->result();

 
   return $mp;
 
      

when you do left join all the rows from posts are retrieved.
left join is used in case if there is no image for the post, if left is not used then that post is won't be retrieved.
if there are N images for the post the there are will be N records(rows) for the post, but as you see I used to GROUP BY clause which groups by post_id that means those N recordes are become 1 row. 
COUNT(pi.post_image_id) counts the number of image in the group.
if you join another table you may need change it to COUNT(DISTINCT pi.post_image_id)


RE: Getting the number of images of an item : Join and Count - demyr - 02-12-2019

(02-12-2019, 02:34 AM)neuron Wrote:
PHP Code:
function my_posts(){
 
   $mp $this->db->SELECT('p.*, count(pi.post_image_id) as image_count')
 
   ->from('posts as p')
 
   ->join('post_images as pi''pi.post_id = p.post_id''LEFT')
 
   ->group_by('p.post_id')
 
   ->get()
 
   ->result();

 
   return $mp;
 
      

when you do left join all the rows from posts are retrieved.
left join is used in case if there is no image for the post, if left is not used then that post is won't be retrieved.
if there are N images for the post the there are will be N records(rows) for the post, but as you see I used to GROUP BY clause which groups by post_id that means those N recordes are become 1 row. 
COUNT(pi.post_image_id) counts the number of image in the group.
if you join another table you may need change it to COUNT(DISTINCT pi.post_image_id)

#neuron Thank you so much for your detailed and explanatory answer. Probably my last question will be this: How to show this on my View?

I used to have this foreach structure within my view:

PHP Code:
<?php $countit=1; foreach($posts as $post) { ?>
          <tr class="blog-tr">
            <td class="w15"><?php echo $countit++; ?></td>
            <td class="w20"><?php echo $post->post_name?></td>
            <td class="w20"><?php echo $post->post_date?></td>
            <td class="w20"> there must be the number here. but how :) </td> 



RE: Getting the number of images of an item : Join and Count - demyr - 02-12-2019

#neuron

Oh, my mistake, sorry. I found it out while I was thinking what is the "as image_count" for?  Smile

It was the answer :


PHP Code:
$post->image_count

Thank you very much again!