Forum OpenACS Q&A: Response to Ecommerce and Recommendations

I don't know what's worse, trying to emulate an outer-joins through the use of multiple unions or by using multiple subselects in the target list to do it. Of course in this case, there isn't any other choice since you can't do a union in a view in pg 7.0.x. In 7.1 you can use outer-joins, but to remain compatible with pg 7.0.x, you can use this view which is definitly more resource intensive:

create view ec_recommendations_cats_view as
select 
  recs.*,
  (select (case when subs.subcategory_id is null then 
                     subsubs.subcategory_id 
                else subs.subcategory_id end) 
     from ec_subcategories_augmented subs, 
          ec_subsubcategories_augmented subsubs
    where recs.subcategory_id = subs.subcategory_id
      and recs.subsubcategory_id = subsubs.subcategory_id) as the_subcategory_id,
  (select (case when subs.subcategory_name is null then 
                     subsubs.subcategory_name 
                else subs.subcategory_name end) 
     from ec_subcategories_augmented subs, 
          ec_subsubcategories_augmented subsubs
    where recs.subcategory_id = subs.subcategory_id
      and recs.subsubcategory_id = subsubs.subcategory_id) as the_subcategory_name,
  (select (case when cats.category_id is null then 
               (case when subs.category_id is null then 
                     subsubs.category_id 
                else subs.category_id end) 
           else cats.category_id end) 
     from ec_categories cats, 
          ec_subcategories_augmented subs, 
          ec_subsubcategories_augmented subsubs
    where recs.category_id = cats.category_id
      and recs.subcategory_id = subs.subcategory_id
      and recs.subsubcategory_id = subsubs.subcategory_id) as the_category_id,
  (select (case when cats.category_name is null then 
               (case when subs.category_name is null then 
                     subsubs.category_name 
                else subs.category_name end) 
           else cats.category_name end) 
     from ec_categories cats, 
          ec_subcategories_augmented subs, 
          ec_subsubcategories_augmented subsubs
    where recs.category_id = cats.category_id
      and recs.subcategory_id = subs.subcategory_id
      and recs.subsubcategory_id = subsubs.subcategory_id) as the_category_name,
  (select subsubcategory_id 
     from ec_subsubcategories_augmented 
    where subcategory_id = recs.subsubcategory_id) as the_subsubcategory_id,
  (select subsubcategory_name 
     from ec_subsubcategories_augmented 
    where subcategory_id = recs.subsubcategory_id) as the_subsubcategory_name
from 
  ec_product_recommendations recs;