Ok so my last post was all about creating a script that would loop through and find all sub, sub-sub, sub-sub-sub  categories and label them with a main_id which was ulimately the top-level catgory of the tree, rather than the parent.

Now I have to create a script that drills up instead of down.  I have to start with the lowest level sub-category and see if it contained products, if it does not, I mark it as having no products.  I want categories that have no products NOT to display.  This is where it gets tricky. Only the lowest level sub categories contain products. So if I have

Category A
  Sub Category A
     Sub-Sub Category AA
     Sub-Sub Category AB
  Sub Category B
     Sub-Sub Category BA
     Sub-Sub Category BB

Only Sub-subCategories AA, AB, BA, and BB would have Products since they are the lowest level categories. So, if Sub-Sub AA has no products then when viewing the categories on the front-end of the website, Sub A  should only display Category Sub-Sub AB. However, let’s say Sub-Sub BA and Sub-Sub BB have no products, then ultimately neither does Sub B so all 3 of those categories would have to be marked as having no products and when viewing Category A on the front end of the site, Sub B would not display So this gets a little tricky and requires drilling up from the lowest level category to the parent and marking the appropriate categories.  Basically if I find a product in a subcategory, I mark that category has having products as well as the parent.

So here is the script I created to do this, this script is run once a day when the categories are imported and then I just filter categories using the hasproducts field:


<cfquery datasource="#application.ds#" name="getcats">  
   select  distinct a.category, a.parent_id
   from  products b
   inner join  tablex x on  b.id = x.prod_id
   inner join categories a on a.category = x.category
  where a.main_id is not null
  
 UNION
 
 select  distinct a.category, a.parent_id
 from  categories a
 inner join tableb b  on   a.category = b.category
 Where  a.main_id is not null
</cfquery>

<cfloop query="getcats">
 <cfquery datasource="#application.ds#" name="update">
   update categories
   set hasproducts = <cfqueryparam value="1" cfsqltype="CF_SQL_INTEGER" />
   where category =  <cfqueryparam value="#getcats.category#" cfsqltype="CF_SQL_INTEGER" />
  <cfif getcats.parent_id neq 0>
    OR category =   <cfqueryparam value="#getcats.parent_id#" cfsqltype="CF_SQL_INTEGER" />
  </cfif> 
 </cfquery>
 <cfif getcats.parent_id neq 0>
  <cfquery datasource="#application.ds#" name="getparent">
    select parent_id
    from categories
    where category = <cfqueryparam value="#getcats.parent_id#" cfsqltype="CF_SQL_INTEGER" />
  </cfquery>
  <cfif getparent.parent_id neq 0>
   <cfquery datasource="#application.ds#" name="update">
     update categories
     set hasproducts = <cfqueryparam value="1" cfsqltype="CF_SQL_INTEGER" />
     where category = <cfqueryparam value="#getparent.parent_id#" cfsqltype="CF_SQL_INTEGER" />
   </cfquery>
   <cfquery datasource="#application.ds#" name="getparent2">
     select parent_id
     from categories
     where category = <cfqueryparam value="#getparent.parent_id#" cfsqltype="CF_SQL_INTEGER" />
   </cfquery>
   <cfif getparent2.parent_id neq 0>
    <cfquery datasource="#application.ds#" name="update">
      update categories
      set hasproducts = <cfqueryparam value="1" cfsqltype="CF_SQL_INTEGER" />
      where category = <cfqueryparam value="#getparent2.parent_id#" cfsqltype="CF_SQL_INTEGER" />
    </cfquery>
    <cfquery datasource="#application.ds#" name="getparent3">
      select parent_id
      from categories
      where category = <cfqueryparam value="#getparent2.parent_id#" cfsqltype="CF_SQL_INTEGER" />
    </cfquery>
    <cfif getparent3.parent_id neq 0>
     <cfquery datasource="#application.ds#" name="update">
       update categories
       set hasproducts = <cfqueryparam value="1" cfsqltype="CF_SQL_INTEGER" />
       where category = <cfqueryparam value="#getparent3.parent_id#" cfsqltype="CF_SQL_INTEGER" />
     </cfquery>
     <cfquery datasource="#application.ds#" name="getparent4">
       select parent_id
       from categories
       where category = <cfqueryparam value="#getparent3.parent_id#" cfsqltype="CF_SQL_INTEGER" />
     </cfquery>
     <cfif getparent4.parent_id neq 0>
      <cfquery datasource="#application.ds#" name="update">
        update categories
        set hasproducts = <cfqueryparam value="1" cfsqltype="CF_SQL_INTEGER" />
        where category = <cfqueryparam value="#getparent4.parent_id#" cfsqltype="CF_SQL_INTEGER" />
      </cfquery>
      <cfquery datasource="#application.ds#" name="getparent5">
        select parent_id
        from categories
        where category = <cfqueryparam value="#getparent5.parent_id#" cfsqltype="CF_SQL_INTEGER" />
      </cfquery>
      <cfif getparent5.parent_id neq 0>
       <cfquery datasource="#application.ds#" name="update">
         update categories
         set hasproducts = <cfqueryparam value="1" cfsqltype="CF_SQL_INTEGER" />
         where category = <cfqueryparam value="#getparent5.parent_id#" cfsqltype="CF_SQL_INTEGER" />
       </cfquery>
      </cfif>
     </cfif>
    </cfif>
   </cfif>
  </cfif>
 </cfif>
</cfloop>

Advertisements