Forum OpenACS Q&A: retrieving complex queries

Collapse
Posted by David Kuczek on
Within our system a user gets credit for activity on the platform
(for example bboard participation). This and some other criteria lead
into a ranking of the users. Such a query would be very slow if you
have a lot of users and even slower if you wanted to show the result
on quite some pages...

Is there any code inside openacs that performs a complex query once
every hour or so, saves it in a different table and gives it out to
the user whenever he wishes? And how do I use it?

Is there any other way to do this kind of job?

Collapse
Posted by Roberto Mello on
In the aD Clickstream module, they used materialized views, which PG
doesn't support. But I faked them by creating tables with the _mv suffix.

A table kept track of all the _mv's (and their definition). A function
would drop and recreate the MV's one by one, being called by a
scheduled proc on AOLserver. This same approach would probably work
for you too.

Collapse
Posted by Don Baccus on
Roberto - sounds like something to add to the porting guide, if there's no mention there already ...
Collapse
Posted by Roberto Mello on
Hmmm... That's a great idea Don. Hadn't thought of it. I'll do it as
soon as 3.2.5 is released ;)