| |
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' |