Forum OpenACS Q&A: Ecommerce and Recommendations

Collapse
Posted by Dave Bauer on
Has anyone got the recommendation system working? What did you do
about ec_recommendations_cats_view?

I don't have enough SQL experience to even understand what it is
trying to do.

It is only used in two files in
admin/ecommerce/products/recommendation.tcl and recommendations.tcl

I suspect that there is a more resource-intensive way to do what it
does. I don't think that creates a problem though because its only
used on administration pages, not user pages.

Collapse
Posted by Dan Wickstrom on
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;

Collapse
Posted by Dave Bauer on
I hate to be a pain. But I can't seem to get this to return any rows when there are recommendations in the database.

First, I tried it with Postgresql 7.0.2 and it crashed the backend.

So I upgraded to 7.0.3 and the query on recommendations.tcl runs, but doesn't display any recommendations.

recommendation.tcl give a "query returned no rows" error.

I tried "select * from ec_recommendations_cats_view;" from psql and that did not return any rows either.

There were 3 recommendations in the database on different categories.

Collapse
Posted by Dan Wickstrom on
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.

Collapse
Posted by Dave Bauer on
That's it! Great. I will try to learn from this. That view is the most complicated thing I have ever seen.

I think 7.0.2 crashed because I need more memory in this machine. Its running 1 other OpenACS site and 3 other static AOLserver sites. It only has about 12MB free RAM.

Collapse
Posted by Scott Mc Williams on
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
Collapse
Posted by David Kuczek on
Dan,

this is not really related to this topic, but how do you post such
nicely formated source code into bboard????????

Thanks

Collapse
Posted by Dan Wickstrom on
Cut and paste from emacs into the textarea, and them surround the whole thing with <pre> tags.
Collapse
Posted by Jerry Asher on
While you're in emacs, it's helpful to m-x replace-string < with &lt;