Count associated records with tree model (full depth)

Question

We have two models. Ebooks HABTM Tags, where tags follows the tree behavior.

For each tag we need two numbers. First, the number of ebooks associated to the tag, and secondly the number of ebooks associated to the tag + the number of associated ebooks for each descendant.

How can we get the tags with these numbers in an array in tree format?

Thank you very much for any help.

Update: There is a datetime parameter Ebook.published which defines when the book is to be counted or not. All the ebooks that have codeEbook.published < NOW() should be counted.

Answer 1

Actually, I found a simpler solution since I already have build the function that returns the tags tree. I used a query for each tag to get the actual count of that moment. Here is what I 've build. Feel free to use it according your needs.

In Tag model

// Returns the number of published ebooks of selected tag
function count_direct_published($tag_id = 0){
    $temp = $this->query('SELECT count(*) as count FROM ebooks_tags LEFT JOIN ebooks ON ebooks_tags.ebook_id = ebooks.id WHERE ebooks.published < NOW() AND ebooks_tags.tag_id = '.$tag_id);
    return $temp[0][0]['count'];
}


// Returns an array in tree format with $id tag and all his children
// $id = 0 start from the top (parent_id = null), or, from $id = the top's tag id
// $limit = boolean (default false)
// $level = Is the limit of depth applied only if $limit = true
// $ext = true Means this is the first time the function is called
// You can run tree_builder(), returns all the tree,
function tree_builder($id = 0, $limit = false, $level = 1, $ext = 1){
    if($ext == 1){
        $ext = 0;
        $undo = true; 
    }else{
        $undo = false;
    }
$this->recursive=-1;

    $this->contain('EbooksTag');

    $var = array();
    $count_all = 0;
    // If limit = too big , exit
    if($limit !== false && $level > $limit){
        return '';
    }
    // Or else, 
    // If $id=0, find all the children
    if($id == 0){
        $tags = $this->find('all',array('conditions'=>array( 'Tag.parent_id IS NULL'), 'order'=>array('Tag.gre')));
    // If $id!=0 && runs internally
    }elseif($id != 0 && !$undo ){
        $tags = $this->find('all',array('conditions'=>array( 'Tag.parent_id'=>$id ), 'order'=>array('Tag.gre')));
    }
    // If $id!=0 && is called from outside 
    elseif($id != 0 && $undo){
        $tags = $this->find('all',array('conditions'=>array( 'Tag.id'=>$id )));

    }

    foreach($tags as $key => $tag){
        $var[] = $tag;
        $next = $this->tree_builder($tag['Tag']['id'], $limit, $level+1, $ext);
        end($var); // move the internal pointer to the end of the array
        $last_key = key($var); // fetches the key of the element pointed to by the internal pointer
        $var[$last_key]['children'] = $next['var'];
        $counter_direct = $this->count_direct_published($id);
        $var[$last_key]['Tag']['count_all'] = $next['count_all']+$counter_direct;
        $count_all += $var[$last_key]['Tag']['count_all'];

    }

    if( $undo )
    {
        return $var;
    }else{
        return array('count_all'=> $count_all, 'var' => $var);
    }

}

In tags_controller.php

$this->set('tags', $this->Tag->tree_builder());

In the view

<?php foreach($tags as $tag){?>
    <?php // Ο Γονέας σε dropdown box ?>
    <div class="main-categ">
    <?php echo $tag['Tag']['gre']; ?>
    <?php echo $html->image('layout/arrows.png', array('alt'=> "Expand")); ?>
    </div>



    <div class="collapse"> 
        <?php // Τα στοιχεία του γονέα ?>
        <div class="tag-1">
            <span class="tag-1">
                <?php // Αν ?>
                <?php if($tag['Tag']['count_direct']>0){
                    // Display link
                     echo $html->link($tag['Tag']['gre'],array('action'=>'view',$tag['Tag']['id']));
                     echo ' ('.$tag['Tag']['count_direct'].')';
                }else{
                    // Display text
                     echo $tag['Tag']['gre'];

                }  ?>
            </span> 
            &nbsp;&nbsp;&nbsp;&nbsp;
            <?php echo $html->link( 'view all' ,array('action'=>'view_all',$tag['Tag']['id'])); ?>
             (<?php echo $tag['Tag']['count_all']; ?>)
        </div>  

        <?php // Για κάθε πρώτο παιδί ?>
        <?php foreach($tag['children'] as $tag_1){ ?>
        <div>
            <span class="tag-2">
                <?php if($tag_1['Tag']['count_direct']>0){
                    // Display link
                     echo $html->link($tag_1['Tag']['gre'],array('action'=>'view',$tag_1['Tag']['id']));
                     echo ' ('.$tag_1['Tag']['count_direct'].')';
                }else{
                    // Display text
                     echo $tag_1['Tag']['gre'];

                }  ?>
            </span>
            &nbsp;&nbsp;&nbsp;&nbsp; 
            <?php echo $html->link( 'view all' ,array('action'=>'view_all',$tag_1['Tag']['id'])); ?>
             (<?php echo $tag_1['Tag']['count_all']; ?>)

                <?php // Τα δεύτερα παιδιά ?>
                <?php $i=0; ?>
                <?php foreach($tag_1['children'] as $tag_2){ ?>
                    <?php if($i==0){ echo '<ul class="split">'; $i++; } ?>
                    <li>
                    <?php if($tag_2['Tag']['count_direct']>0){
                            // Display link
                             echo $html->link($tag_2['Tag']['gre'],array('action'=>'view',$tag_2['Tag']['id']));
                             echo ' ('.$tag_2['Tag']['count_direct'].')';
                        }else{
                            // Display text
                             echo $tag_2['Tag']['gre'];

                        }  ?>                       
                    </li>
                <?php } ?>
                <?php if($i==1) echo '</ul>'; ?>

            <div class="clear"></div>       
        </div>

        <?php } ?>      

    </div>

Perhaps its not the best solution but it works. Hope that helps