Display Subcategories within Categories from Custom Tables

11 Posts

itcassy Reply #1, 12 years, 3 months ago

First off, I am a newbie when it comes to working with xpdo and custom tables within ModX. I have been pouring through posts, documentation, and tutorials (thanks bob!) for 2 days now and I cannot figure out what seems to be pretty simple. I have created my tables and generated a schema, classes, and maps according to http://bobsguides.com. I am pretty sure my relationships are setup correctly in the schema file, but as a newbie, not 100% sure.

I have a techCategory table, categoryA table and categoryB table. I simply would like to display the techCategory and any subcategories under it like the following example:

Microsoft (techCategory.tCatName)
--Exchange (categoryA.catAName)
----2010 (categoryB.catBName)
----2012 (categoryB.catBName)
--Virtualization (categoryA.catAName)

Citrix (techCategory.tCatName)

etc...

Here is from my schema file:

<object class="TechCategory" table="techCategory" extends="xPDOObject">
  <field key="tCatID" dbtype="int" precision="10" phptype="integer" null="false" index="pk"  generated="native" />
  <field key="tCatName" dbtype="varchar" precision="255" phptype="string" null="false" />
  <field key="tCatDescription" dbtype="longtext" phptype="string" null="false" />
  <field key="sortOrder" dbtype="int" precision="10" phptype="integer" null="false" />
  <index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
    <column key="tCatID" length="" collation="A" null="false" />
  </index>
  
  <composite alias="CategoryA" class="CategoryA" local="tCatID" foreign="tCatID" cardinality="one" owner="local" />
</object>
	
<object class="CategoryA" table="categoryA" extends="xPDOObject">
  <field key="catAID" dbtype="int" precision="10" phptype="integer" null="false" index="pk"  generated="native" />
  <field key="tCatID" dbtype="int" precision="10" phptype="integer" null="false" />
  <field key="catAName" dbtype="varchar" precision="255" phptype="string" null="false" />
  <field key="catADescription" dbtype="longtext" phptype="string" null="false" />
  <index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
    <column key="catAID" length="" collation="A" null="false" />
  </index>

  <aggregate alias="TechCategory" class="TechCategory" local="tCatID" foreign="tCatID" cardinality="one" owner="foreign" />
  <composite alias="CategoryB" class="CategoryB" local="catAID" foreign="catAID" cardinality="one" owner="local" />
</object>
	
<object class="CategoryB" table="categoryB" extends="xPDOObject">
  <field key="catBID" dbtype="int" precision="10" phptype="integer" null="false" index="pk"  generated="native" />
  <field key="catAID" dbtype="int" precision="10" phptype="integer" null="false" />
  <field key="catBName" dbtype="varchar" precision="255" phptype="string" null="false" />
  <index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
    <column key="catBID" length="" collation="A" null="false" />
  </index>

  <aggregate alias="CategoryA" class="CategoryA" local="catAID" foreign="catAID" cardinality="one" owner="foreign" />
</object>

I can display all of any of those tables individually - NO PROBLEM:

$path = MODX_CORE_PATH . 'components/mypackage/';
$modx->addPackage('mypackage',$path . 'model/','xxxx_');

$output = '';

$criteria = $modx->newQuery('TechCategory');
$criteria->sortby('tCatName', 'ASC');

$categories = $modx->getCollection('TechCategory', $criteria);

echo '<ul>';

foreach($categories as $category) {
  $catid = $category->get('tCatID');
  $catname = $category->get('tCatName');

  $output .= '<li><a href="[[~59? &cat=`' . $catid .'`]]" title="' . $catname . ' Courses">' . $catname . '</a></li>';
}

return $output;

echo '</ul>';

However, when I try to get all of the results I need combined, I have been 100% unsuccessful. The only progress I have made is showing the fist TechCategory and then ALL of the subcategories under it. X[ I have tried so many things that I don't even know what attempted code to post as examples of what I have done!

I have tried getCollectionGraph but I guess I just couldn't figure it out. I have tried implementing joins, getObject, all kinds of things, but I just seem to not be getting it. I am just not understanding what I am supposed to do. If there is any way anyone could help me set this up, I would be eternally grateful! I also have some money in the budget to pass on - I know everyone's time is valuable and I am not sure where else to turn. PM me if there is any interest in paid assistance.

24,544 Posts

BobRay Reply #2, 12 years, 3 months ago

I'm hesitant to second-guess what you're doing, but I wonder if it would work for you to have just one object called Category with aliases called Children and Parent. I think it would be easier to work with and more flexible.

The top-level categories would have a 0 in the 'parent' field, all others would have the ID of their parent. This would allow a main category to have as many subcategories as you like, and allow the category tree to contain as many levels as you need in the future.

I'd also suggest dropping the prefixes on the field names and just using 'name' and 'description'.

I don't think you need sortorder in the schema, but maybe I'm misunderstanding what you're doing.

Something like this (extending xPDOSimpleObject will give you an automatic indexed id field, but you might want to index the name field for searching by name). This is off the top of my head, so it may not be perfectly accurate, but should give you the idea of what I'm suggesting:

<object class="Category" table="techCategory" extends="xPDOSimpleObject">
  <field key="name" dbtype="varchar" precision="255" phptype="string" null="false" />
  <field key="description" dbtype="longtext" phptype="string" null="false" />
  <field key="parent" dbtype="int" precision="10" phptype="integer" null="false" default="0" index="index" />

  <aggregate alias="Parent" class="Category" local="parent" foreign="id" cardinality="one" owner="foreign" />
  <aggregate alias="Children" class="Category" local="id" foreign="parent"   cardinality="many" owner="local" />
</object>

For the display:

[[DisplayCategories]]

<?php
/* DisplayCategories Snippet */

/* recursive function to display sub-categories */
function displayChildren($children, $level = 1) {
    global $modx;
    foreach ($children as $child) {
       $output = "\n" .'<div class="level"' . $level , '>';
       $output = $modx->getChunk('ChildTpl', $child->toArray());
       $c = $modx->newQuery('Category');
       $c->sortby = ('name', 'ASC');
       $subs = $child->getMany('Children', $c);
       if (! empty($subs)) {
           $output .= displayChildren($subs, $level + 1);
       }
       $output .= "\n" . '</div>';
    }
    return $output;
    
}

$output = '';
$c = $modx->newQuery('Category');
$c->sortby = ('name', 'ASC');
$c->where(array('parent' => 0));
$mainCats = $modx->getCollection('Category', $c);

foreach ($mainCats and $mainCat) {
    $output .= "\n\n" . $modx->getChunk('MainTpl', $mainCat->toArray());
    $c = $modx->newQuery('Category');
    $c->sortby = ('name', 'ASC');
    $children = $mainCat->getMany('Children', $c);
    if (! empty($children)) {
        $output .= displayChildren($children);
    }
}

MainTpl:

<h3 class="main_category">[[+name]]</h3>
   <p class="main_cat_desc">[[+description]]</p>

ChildTpl:

<p class="sub_category_name">[[+name]]</p>
<p class="sub_category_desc">[[+description]]</p>

That should give you a nested set of divs that you can style however you like. With a little extra code, you could add the level to the div class if necessary and also indent the divs according to level so "View Source" would be easier to read.

I hope this makes sense and isn't too far away from what you need.

[ed. note: BobRay last edited this post 12 years, 3 months ago.]

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

11 Posts

itcassy Reply #3, 12 years, 3 months ago

Thank you SO much BobRay for your time in helping me. I actually almost purchased your guide, but I was not sure if it would go into this much and I know the manager aspect very well already.

So, if you think I should get that, please don't hesitate to let me know.

I understand most everything that you said. I have gone over your site again where you spell this out and it is the same process ModX uses for new page (resource) creation. I have also been studying the modx schema file as you advise. This is definitely an improved way of working it. There are several non-relevant reasons why it was setup that way and I am definitely open to changing things.

Here is what I have done:

Created a NEW table called category, fields are id(primary), name(varchar), description(longtext), parent(int,10)
Updated the schema and re-created classes

Here is the schema based on what you kindly sent:

<object class="Category" table="category" extends="xPDOObject">
  <field key="name" dbtype="varchar" precision="255" phptype="string" null="false" />
  <field key="description" dbtype="longtext" phptype="string" null="false" />
  <field key="parent" dbtype="int" precision="10" phptype="integer" null="false" default="0" index="index" />

  <aggregate alias="Parent" class="SubCategory" local="parent" foreign="id" cardinality="one" owner="foreign" />
  <aggregate alias="Children" class="SubCategory" local="id" foreign="parent" cardinality="many" owner="local" />
</object>

I setup the snippet and chunks exactly as you have it (changing techcategory to category)

/* DisplayCategories Snippet */

/* recursive function to display sub-categories */
function displayChildren($children, $level = 1) {
    foreach ($children as $child) {
       $output = "\n" .'<div class="level"' . $level . '>';
       $output = $modx->getChunk('ChildTpl', $child->toArray());
       $c = $modx->newQuery('Category');
			 $c->sortby('name', 'ASC');
       $subs = $child->getMany('Children', $c);
       if (! empty($subs)) {
           $level++;
           $output .= displayChildren($subs, $level);
       }
       $output .= "\n" . '</div>';
    }
    return $output;
    
}

$output = '';
$c = $modx->newQuery('Category');
$c->sortby('name', 'ASC');
$c->where(array('parent' => 0));
$mainCats = $modx->getCollection('Category', $c);

foreach ($mainCats as $mainCat) {
    $output .= "\n\n" . $modx->getChunk('MainTpl',
      $mainCat->toArray());
    $c = $modx->newQuery('Category');
    $c->sortby('name', 'ASC');
    $children = $mainCat->getMany('Children', $c);
    if (! empty($children)) {
        $output .= displayChildren($children);
    }
}

return $output;

tpl's are EXACTLY what you had.

The issue is that the children do not show.

I have gone over and over this and cannot see any glaring mistakes in the function or schema.

The one thing I cannot wrap my head around is

"<aggregate alias="Children" class="SubCategory" local="id" foreign="parent" cardinality="many" owner="local" />" I am totally missing where the SubCategory class comes into play anywhere (sorry)

24,544 Posts

BobRay Reply #4, 12 years, 3 months ago

My mistake, that was leftover from an earlier version I wrote that was more like your original code. The class should be 'Category' (the same as the main object) for both the Parent and Children aliases.

Note that you'll have to regenerate the class and map files after changing the schema (MODX doesn't actually use the schema) -- you may have to delete the existing files to do that.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

11 Posts

itcassy Reply #5, 12 years, 3 months ago

Whew...I thought I was really missing something! I actually DID try that before I replied but I got an error:

Notice: Undefined variable: modx in /home/xxxxx/public_html/core/cache/includes/elements/modsnippet/41.include.cache.php on line 17

Fatal error: Call to a member function getChunk() on a non-object in /home/xxxxx/public_html/core/cache/includes/elements/modsnippet/41.include.cache.php on line 17

(line 17 is: $output = $modx->getChunk('ChildTpl', $child->toArray());)

I can't see what's wrong with it to generate that tho sad

I triple checked all my spelling and it is just about identical to the getChunk call below it.

And yes, I do absolutely know about regenerating and am a pro at that part - lol! I always delete the existing first since it won't create the files if they are already there.

24,544 Posts

BobRay Reply #6, 12 years, 3 months ago

My mistake again. You need this line as the first line of the displayChildren() function:

global $modx;

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

11 Posts

itcassy Reply #7, 12 years, 3 months ago

That worked - thank you so so much!! I am wondering if that's why my multiple other attempts didn't work either now. Geez... it's amazing how one line can cost so many hours. Again, thank you!!

24,544 Posts

BobRay Reply #8, 12 years, 3 months ago

I'm glad you got it working.

In PHP, variables that exist outside a function, like $modx, are not available inside a function unless you either declare them as above, or pass them to the function as an argument. Sometimes you have to do it the second way, but I've never been able to figure out why.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

11 Posts

itcassy Reply #9, 12 years, 3 months ago

After I entered a more complex structure, I noticed the increment is not quite working right. I have tried multiple variations in the code but of course am not getting anywhere. I added some coloring to the levels to easily see what is happening. It is not reverting back to level1 when there are additional sub-categories. In other words, the main categories display fine, the first sub-category (level) displays fine, but only the first time IF there are sub-categories. I have included a graphic which shows it better. I have also re-copied the original coding you sent with the minor edits that have been added along the way.

$path = MODX_CORE_PATH . 'components/coursemanager/';
$modx->addPackage('coursemanager',$path . 'model/','ctrec_');
$ouput = '';

/* DisplayCategories Snippet */


/* recursive function to display sub-categories */
function displayChildren($children, $level = 1) {

    global $modx;

    foreach ($children as $child) {
       $output .= "\n" .'<div class="level' . $level . '">';
       $output .= $modx->getChunk('ChildTpl', 
	$child->toArray());
       $c = $modx->newQuery('Category');
	   $c->sortby('name', 'ASC');
       $subs = $child->getMany('Children', $c);
       if (! empty($subs)) {
           $level++;

           $output .= displayChildren($subs, $level);

       }
       $output .= "\n" . '</div>';
    }

    return $output;
}


$c = $modx->newQuery('Category');
$c->sortby('name', 'ASC');
$c->where(array('parent' => 0));
$mainCats = $modx->getCollection('Category', $c);

foreach ($mainCats as $mainCat) {
    $output .= "\n\n" . $modx->getChunk('MainTpl', 
$mainCat->toArray());
    $c = $modx->newQuery('Category');
    $c->sortby('name', 'ASC');
    $children = $mainCat->getMany('Children', $c);
    if (! empty($children)) {
        $output .= displayChildren($children);
    }

}
return $output;

It doesn't seem like it should be hard to fix it, but I am just not seeing it.

24,544 Posts

BobRay Reply #10, 12 years, 3 months ago

I don't really have a way to test it.

See how this works (note: two changes -- don't forget the + 1):

       if (! empty($subs)) {
         /*  $level++; */
 
           $output .= displayChildren($subs, $level + 1);
 
       }

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting