Forum OpenACS Q&A: 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;

It's not a problem. We like to hear about bugs in openacs :).

I'm using pg 7.02, and when I tried to view the recommendations it didn't crash, but I received a strange error message. I also have pg 7.1 beta3 installed, so I tried it on that system and I received an error on the union query in recommendations.tcl. I changed the query to the following, and after that I was able to add recommendations and view them.

select 
  r.recommendation_id, r.the_category_name, r.the_subcategory_name, r.the_subsubcategory_name,
  p.product_name, 
  c.user_class_name,
  case when r.the_category_name is NULL then 0 else 1 end as cat_name_sort,
  upper(the_category_name) || upper(the_subcategory_name) || upper(the_subsubcategory_name) as cat_sort
from ec_recommendations_cats_view r, ec_products p, ec_user_classes c
where r.active_p='t'
and r.user_class_id = c.user_class_id
and r.product_id = p.product_id 
union
select 
  r.recommendation_id, r.the_category_name, r.the_subcategory_name, r.the_subsubcategory_name,
  p.product_name, 
  null as user_class_name,
  case when the_category_name is NULL then 0 else 1 end as cat_name_sort,
  upper(the_category_name) || upper(the_subcategory_name) || upper(the_subsubcategory_name) as cat_sort
from ec_recommendations_cats_view r, ec_products p
where r.active_p='t'
and r.user_class_id is null
and r.product_id = p.product_id
order by cat_name_sort, cat_sort

If this doesn't fix it for you, let me know and I'll upgrade to pg 7.03 and see if I can track down the problem.

Hey guys...I know this is ancient, but I'm trying to get recommendations to work also. I created the view as shown above, but I still get this error when I try to add a recommendation:

ERROR:   referential integrity violation - key referenced from ec_product_recommendations not found in ec_subsubcategories

Any hints?

Thanks
Scott

Cut and paste from emacs into the textarea, and them surround the whole thing with <pre> tags.