Home > Articles

  • Print
  • + Share This

Match the Subcategory

32 /*  Now, pull the matching subcategory
33    with parent ID matching above */
34  SELECT TOP 1
35   @nid2 = n.navigation_id
36  FROM
37   products p,
38   product_tax_mapping ptm,
39   reference_dimension rd,
40   navigation n
41  WHERE
42   p.product_id=@prodid
43   and ptm.product_id = p.product_id
44   and ptm.reference_id = rd.reference_id
45   and n.facet_column='subcategory'
46   and rd.subcategory = n.facet_column_value
47   and n.parent_navigation_id = @nid
48  ORDER BY
49   n.facet_column_value ASC

Above we have essentially the same algorithm, with the subtle difference that we want to match on the second level of the reference_dimension, the subcategory. This introduces the obvious alterations seen on lines 45 and 46. subcategory has replaced category. We also add the extra wrinkle that the previously determined navigation_id must be listed as the parent_navigation_id for the current record. There is a java subcategory under the programming category. There is also a java subcategory under web development. If we pulled the programming category, we want to make sure that we pull the top subcategory from the proper place.

One more difference can be seen. This time we have ordered by facet_column_value, the name of the subcategory. Our editors are not quite intrepid enough to be expected to assign sibling priorities to the vast number of subcategories in the database. So we do it alphabetically here. I suppose that the editorial staff must have something else to do.

50 /*  Finally, pull the tab_navigation_id corresponding
51    to the productID */
52 IF @nid2 is NULL
53 BEGIN
54  SELECT @nid2 = @nid
55 END

Why perform this check in lines 52–55? A product is not required to be tagged to the subcategory level. If no subcategory navigation_id could be found, we'll just return the category navigation_id. If that was null too, we return a null. That naughty editorial staff forgot to tag this product. We handle that case in State.asp.

  • + Share This
  • 🔖 Save To Your Account

Related Resources

There are currently no related titles. Please check back later.