Categories, sub-categories, sub-sub-categories, oh my!

So today I worked on a project for CFWebTools.  The site I was working on has products and categories. The categories have subcategories that can go 4 levels deep.  When someone searches on the site for a term like “ipod”, right now all the results that include ipod are returned.   On the results page they want a select box with all the top-level categories listed that have ipod with in that category (and it’s sub-categories) along with a count. Similar to how Amazon does it.

So in the select box you would have something like this:

Apple (10)

Games (5)

Electronics (2)

Now category Apple might have a sub-category of Ipod, Ipod might have a sub-category of Headphones, Headphones might have a sub-category of… well you get the idea.   So the way things are set up right now, in order to get an accurate count of products containing the term i-pod within the Apple category and any of the sub-categories, I would have to do 5 nested queries.  Not only would I have to find the count, but determine what the top-level category is on a sub-category that might be 4 levels deep. Ugh…

So I suggested I create a script that we can run when needed (like once a day since Categories are imported daily).  The script will do the necessary nested query madness  and label all the subcategories with a main_id, which is ultimately the id of the top-level category.    They all have a parent_id but the parent is simply one level up, so if it is a 4th level sub category, the parent_id isn’t going to tell us what the top-level category is.  By adding in a field of a main_id in the database we can create a much quicker count and even optimize some other areas of the websites.

So, I added one field to the database called main_id.

Then I run this script you see below (takes about 4 seconds). You will see several if statements that check to see if the record count is greater than 0. I had to do this because I ran into sub-categories that were orphaned so there ends up not being a parent. This alleviates that problem and lets the script run without error.  Once this script runs, we also now know all of the sub-categories that are orphaned because their main_id will be null.  * I do use cfqueryparams on all queries just removed some from the sample since the indenting was already insane.

<cfquery datasource=#application.ds# name="getcats" >
 update categories set main_id = null
</cfquery>
<cfquery datasource=#application.ds# name="getcats" >
 select Category,parent_id from categories  order by parent_id,category
</cfquery>

<cfoutput query="getcats">
 <cfif parent_id eq 0>
  <cfset parent = category>
 <cfelse>
  <cfquery datasource=#application.ds# name="checkparent">
   select category,parent_id from categories where category = <cfqueryparam value="#getcats.parent_id#" cfsqltype="CF_SQL_INTEGER" />
  </cfquery>
  <cfif checkparent.recordcount gt 0>
   <cfif checkparent.parent_id eq 0>
    <cfset parent=checkparent.category>
   <cfelse>
    <cfquery datasource=#application.ds# name="checkparent2">
     select category,parent_id from categories where category = <cfqueryparam value="#checkparent.parent_id#" cfsqltype="CF_SQL_INTEGER" />
    </cfquery>
    <cfif checkparent2.recordcount gt 0>
     <cfif checkparent2.parent_id eq 0>
      <cfset parent=checkparent2.category>
     <cfelse>
      <cfquery datasource=#application.ds# name="checkparent3">
       select category,parent_id from categories where category = <cfqueryparam value="#checkparent2.parent_id#" cfsqltype="CF_SQL_INTEGER" />
      </cfquery>
      <cfif checkparent3.recordcount gt 0>
       <cfif checkparent3.parent_id eq 0>
        <cfset parent=checkparent3.category>
       <cfelse>
        <cfquery datasource=#application.ds# name="checkparent4">
         select category,parent_id from categories where category = <cfqueryparam value="#checkparent3.parent_id#" cfsqltype="CF_SQL_INTEGER" />
        </cfquery>
        <cfif checkparent4.recordcount gt 0>
         <cfif checkparent4.parent_id eq 0>
          <cfset parent=checkparent4.category>
         <cfelse>
          <cfquery datasource=#application.ds# name="checkparent5">
           select category,parent_id from categories where category = <cfqueryparam value="#checkparent4.parent_id#" cfsqltype="CF_SQL_INTEGER" />
          </cfquery>
          <cfif checkparent5.recordcount gt 0>
           <cfif checkparent5.parent_id eq 0>
            <cfset parent=checkparent5.category>
           </cfif>
          <cfelse>
           <cfset parent = 0> 
          </cfif>
         </cfif>
        <cfelse>
         <cfset parent = 0>
        </cfif>
       </cfif>
      <cfelse> 
       <cfset parent = 0>
      </cfif>
     </cfif>
    <cfelse>
     <cfset parent = 0>
    </cfif>
   </cfif>
  <cfelse>
   <cfset parent = 0> 
  </cfif>
 </cfif>
 <cfif parent neq 0>
  <cfquery datasource="#application.ds#">
   update categories
   set main_id = <cfqueryparam value="#parent#" cfsqltype="CF_SQL_INTEGER" />
   where category =  <cfqueryparam value="#getcats.category#" cfsqltype="CF_SQL_INTEGER" />
  </cfquery>
 </cfif>
</cfoutput>

So now I can create one query that will pull a count of a given search term from all sub-categories within a top-level category and come back with one count per top-level category. No nesting of queries. Runs much faster on the live site then the nesting would.

  SELECT
     count(cat.main_id) as count, cat.main_id, cat2.name
  FROM
     products p
  INNER JOIN
     table_b b on p.id = b.prod_id
  INNER JOIN
     table_c c on c.categoryid = b.custcat_id
  INNER JOIN
     categories cat on c.categoryid = cat.categoryid
  INNER JOIN
     categories cat2 on cat2.main_id = cat2.categoryid
  WHERE
     p.prodname LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#trim(myTerm[ter])#%" />
 GROUP BY
     cat.main_id, cat2.name