Yii framework + WP style (using WP db tables) post hierarchy: I need to order categories and subcategories

Question

I have started a project in Yii framework (Yii level: beginner). I decided to use WP style db tables to store my entries and assign them to different categories, subcategories and have multiple tags assigned to entries.

So basically, here are the tables (note the names are singular here):

tbl_entry (entries) tbl_terms (name, slug of categories, subcategories, tags) tbl_term_taxonomy (category, subcategory, tag taxonomies) tbl_term_relationship (based on ID of an entry, a relationship is created)

What I have got so far is that I'm able to fetch them all (cats, subcats, tags). I'm also able to tell apart categories and subcategories but I cannot have them hierarchically ordered.

I need them to be:

Category 1
    Subcategory of Cat 1
    Another subcat of Cat 1
Category 2
    Subcategory of Cat 2
    Another subcat of Cat 2
    Yet another subcat of Cat 2
Catwegory 3
    etc.

Currently, they display something like this:

    Another subcat of Cat 1
Category 2
    Subcategory of Cat 2
    Another subcat of Cat 2
Category 1
    Subcategory of Cat 1
    Another subcat of Cat 1
    Yet another subcat of Cat 2
Catwegory 3

The point being: they display in no meaningful order, more precisely, they're being stacked one below the other as they're being added to the database.

I'm not sure if I should be able to group something and if yes, what table and column?

I have relations in each table's class in place and have tried to put group clause there but no success.

==================== E D I T ===============

I forgot to state several things. I'm currently trying to make this work (display cats, subcats and tags) for a single entry when displayed.

Controller EntryController
    public function actionView($id)
    {
        $entry = $this->loadModel();
        $relations = $entry->relations;

        $this->render('view', array(
            'entry' => $entry,
            'relations' => $relations,
        ));
    }

Model class Entry.php where the main relationship with Termrelationship.php is established:

public function relations()
{
    return array(
        'relations' => array(self::HAS_MANY, 'Termrelationship', 'id',),
    );
}

The view.php. Note: since they all are just relations (cats, subcats, tags) and as coming from the relationships table they don't have any hierarchy set up in that table itself. I had to compare taxonomy names to give them different markup (and place them in another block (tags)). Subcats appear indented here: http://screencast.com/t/CvCBJoQqD0WP just because they're wrapped in a SMALL tag. That is fine, but I'd also like to be able to list a main/parent category, then all subcats of that category, then start a new category, etc...

<div class="row">
    <div class="col-md-6">

        <blockquote>
        <?php
            foreach($relations as $relation):
                if($relation->taxonomy['taxonomy']=='category'):
                    echo $relation->taxonomy->term->name . ' (Parent: '.$relation->taxonomy['parent'].')';
                endif;
                    if($relation->taxonomy['taxonomy']=='subcategory'):
                        echo '<small>'.$relation->taxonomy->term->name . ' (Parent: '.$relation->taxonomy['parent'].')</small>';
                    endif;
            endforeach;
        ?>
        </blockquote>

    </div>

    <div class="col-md-6">
        <?php
            foreach($relations as $relation):
                if($relation->taxonomy['taxonomy']=='tag'):
                    echo '<span class="label label-default">'.$relation->taxonomy->term->name.'</span> ';
                endif;
            endforeach;
        ?>
    </div>
</div>

How do you retrieve them from the Database? Do you use the CActiveRecord method findAll()? Do you use a custom query? It would help if you could post your tries at that as well. — Nikolas Grottendieck
@NikolasGrottendieck I've just updated my question, see edits in the bottom! Thank you — developer10
How deep would the tree go in total? Are there only 2 or less levels involved? — Nikolas Grottendieck
@NikolasGrottendieck That is a good question. I haven't decided yet but most probably I'll stick to 2 levels (cat and subcat) plus tags to avoid the need of having more levels. It would be great, however, to make this work in a way that is supports unlimited levels. I still need this to support an entry being attached to multiple cats and subcats. — developer10
I am currently a little short on time or else I'd see about writing an actual full answer but I think if you used CDbCriteria you could select level 0 and then use the relationship data to get you the lower levels for each parent. You may need to fiddle with sorting a little. — Nikolas Grottendieck

Nikolas Grottendieck Nikolas Grottendieck · Accepted Answer · 2013-10-30T13:50:57

After taking a look at the explanation for relational active records, you can define the proper order for relationship data in the relations() function in each model as well as a with definition to eagerly load child items automatically. Or you could use the ->with(array('…'))->find() operation to make eager loading possible manually to a self set degree.

// Entry actionView() Option one, manually setting depth
// should load a single entry with relationships 2 levels deep + parent/current
$entry = Entry::model()->with(array('relations.relations'))->findByPk($id); 

// Entry Model, Option two fully specifying the relationship
public function relations()
{
    return array(
        'relations' => array(self::HAS_MANY, 'Termrelationship', 'id', 'order' => 'entry.name', 'with' => 'relations'),
    );
}

Btw. are you certain that the id part in relations() is the correct foreign key reference? You may even wish to use the through modifier for relationships to define additional information about the relationship structure.

Yii framework + WP style (using WP db tables) post hierarchy: I need to order categories and subcategories

1 Answers