Forum OpenACS Development: Re: Tagcloud

Collapse
7: Re: Tagcloud (response to 1)
Posted by Malte Sussdorff on
You are about right that you'd face a permission problem, but I solved it before committing. Sadly now it takes a very long time to come up with the result:

select category_id, count(com.object_id), min(trans.name)
from categories natural left join category_object_map com natural join category_trees
natural join category_translations trans
where tree_id = '1999' and trans.locale = 'en_US'
and exists (select 1 from acs_object_party_privilege_map ppm
where ppm.object_id = com.object_id
and ppm.privilege = 'read'
and ppm.party_id = '499')
group by category_id

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=498.47..498.47 rows=1 width=326)
-> Nested Loop (cost=0.00..498.46 rows=1 width=326)
-> Nested Loop (cost=0.00..497.37 rows=1 width=330)
Join Filter: ("inner".category_id = "outer".category_id)
-> Seq Scan on category_translations trans (cost=0.00..1.39 rows=1 width=322)
Filter: ((locale)::text = 'en_US'::text)
-> Nested Loop Left Join (cost=0.00..495.95 rows=3 width=12)
Filter: (subplan)
-> Seq Scan on categories (cost=0.00..1.39 rows=1 width=8)
Filter: (tree_id = 1999)
-> Index Scan using category_object_map_pkey on category_object_map com (cost=0.00..18.88 rows=6 width=8)
Index Cond: ("outer".category_id = com.category_id)
SubPlan
-> Nested Loop (cost=18.89..79.27 rows=1 width=0)
-> Hash Join (cost=18.89..69.23 rows=1 width=4)
Hash Cond: ("outer".grantee_id = "inner".party_id)
-> Nested Loop (cost=0.00..50.25 rows=15 width=8)
-> Seq Scan on acs_privilege_descendant_map pdm (cost=0.00..3.65 rows=1 width=168)
Filter: ((descendant)::text = 'read'::text)
--More--
-> Index Scan using acs_permissions_privilege_idx on acs_permissions p (cost=0.00..46.41 rows=15 width=176)
Index Cond: (("outer".privilege)::text = (p.privilege)::text)
-> Hash (cost=18.88..18.88 rows=6 width=4)
-> Index Scan using party_member_member_idx on party_approved_member_map pamm (cost=0.00..18.88 rows=6 width=4)
Index Cond: (member_id = 499)
-> Index Scan using acs_object_context_index_pk on acs_object_context_index c (cost=0.00..10.01 rows=2 width=4)
Index Cond: ((c.object_id = $0) AND (c.ancestor_id = "outer".object_id))
-> Seq Scan on category_trees (cost=0.00..1.07 rows=1 width=4)
Filter: (1999 = tree_id)
(28 rows)