jader
committed
on 19 Jan 06
Possible bug fix -- I believe ci_items is not the name of a table in OpenACS.
/www/random-photo-postgresql.xql (+1 -1)
32 32       from site_nodes sn
33 33         inner join pa_package_root_folder_map pm on (sn.object_id = pm.package_id)
34 34         inner join cr_items parent on (pm.folder_id = parent.item_id)
35 35         inner join cr_items child on (parent.tree_sortkey <= child.tree_sortkey)
36 36       where child.tree_sortkey between parent.tree_sortkey and tree_right(parent.tree_sortkey)
37 37       and tree_level(parent.tree_sortkey) = 2
38 38       and child.item_id = :photo_id
39 39     </querytext>
40 40   </fullquery>
41 41  
42 42   <fullquery name="get_random_photo_folder">
43 43     <querytext>
44 44       select ci.item_id as photo_id,
45 45           (select pp.caption
46 46           from pa_photos pp
47 47           where pp.pa_photo_id = ci.live_revision) as caption,
48 48           i.image_id as thumb_path,
49 49           i.height as thumb_height,
50 50           i.width as thumb_width,
51 51           random() as seed
52         from cr_items ci, cr_items ci2, ci_items root, cr_child_rels ccr2, images i
  52       from cr_items ci, cr_items ci2, cr_items root, cr_child_rels ccr2, images i
53 53       where $size_clause
54 54       and ci.item_id = ccr2.parent_id
55 55       and ccr2.child_id = ci2.item_id
56 56       and ci2.live_revision = i.image_id
57 57       and ci.live_revision is not null
58 58       and ci.tree_sortkey between root.tree_sortkey and tree_right(root.tree_sortkey)
59 59       and root.item_id = :root_folder_id
60 60       order by seed limit 1
61 61     </querytext>
62 62   </fullquery>
63 63
64 64   <partialquery name="photo_clause">
65 65     <querytext>
66 66       and ci.item_id = :photo_id
67 67     </querytext>
68 68   </partialquery>
69 69
70 70   <partialquery name="size_clause_normal">
71 71     <querytext>
72 72       ccr2.relation_tag = 'viewer'