Menu
▾
▴
[Aplaws-commit] r962 - trunk/ccm-ldn-dublin/pdl/com/arsdigita/london/cms/dublin
|
From: <ss...@vh...> - 2005-10-20 12:10:37
|
Author: sshinde Date: 2005-10-20 14:10:15 +0200 (Thu, 20 Oct 2005) New Revision: 962 Modified: trunk/ccm-ldn-dublin/pdl/com/arsdigita/london/cms/dublin/query-getRelatedItems.pdl Log: Fix for issue tracker https://enterprise.redhat.com/issue-tracker/?module=issues&action=view&tid=81667&gid=412 Select distinct bundle IDs so that entries are not repeated when displayed on pages. Leaving previous query commented out just in case if need arises to revert back. Modified: trunk/ccm-ldn-dublin/pdl/com/arsdigita/london/cms/dublin/query-getRelatedItems.pdl =================================================================== --- trunk/ccm-ldn-dublin/pdl/com/arsdigita/london/cms/dublin/query-getRelatedItems.pdl 2005-10-20 11:30:54 UTC (rev 961) +++ trunk/ccm-ldn-dublin/pdl/com/arsdigita/london/cms/dublin/query-getRelatedItems.pdl 2005-10-20 12:10:15 UTC (rev 962) @@ -22,6 +22,60 @@ // * Matching LGCL category ID // * Not in a subcat of the current Nav cat // * Matching first Dublin keywords +// Original Query with duplicate resutls problem +//query getRelatedItems { +// BigDecimal[1..1] itemID; +// BigDecimal[1..1] workingID; +// String[1..1] title; +// String[1..1] type; +// String[1..1] objectType; +// +// do { +// select id.item_id, +// id.master_id, +// pd.title, +// t.label, +// o.object_type +// from cms_bundles bd, +// cat_object_category_map bsm, +// cat_object_category_map bdm, +// trm_terms tsd, +// cms_items id, +// acs_objects o, +// content_types t, +// cms_pages pd, +// ldn_dublin_core_items dd, +// ldn_dublin_core_item_map ddm +// where bsm.object_id = :bundleID +// and bsm.category_id = bdm.category_id +// and bd.bundle_id = bdm.object_id +// and bdm.category_id = tsd.model_category_id +// and tsd.domain = :subjectDomain +// and bd.bundle_id = id.parent_id +// and id.item_id = ddm.item_id +// and ddm.dublin_id = dd.dublin_id +// and dd.keywords like :keyword || '%' +// and id.type_id = t.type_id +// and id.version = 'live' +// and id.item_id = o.object_id +// and pd.item_id = id.item_id +// and id.item_id != :itemID +// and bdm.object_id not in ( +// select bnm.object_id +// from cat_object_category_map bnm, +// cat_cat_subcat_trans_index sti +// where bnm.category_id = sti.subcategory_id +// and sti.category_id = :navCategoryID +// ) +// } map { +// itemID = id.item_id; +// workingID = id.master_id; +// title = pd.title; +// type = t.label; +// objectType = o.object_type; +// } +//} + query getRelatedItems { BigDecimal[1..1] itemID; BigDecimal[1..1] workingID; @@ -30,47 +84,51 @@ String[1..1] objectType; do { - select id.item_id, - id.master_id, - pd.title, - t.label, - o.object_type - from cms_bundles bd, - cat_object_category_map bsm, - cat_object_category_map bdm, - trm_terms tsd, - cms_items id, - acs_objects o, - content_types t, - cms_pages pd, - ldn_dublin_core_items dd, - ldn_dublin_core_item_map ddm - where bsm.object_id = :bundleID - and bsm.category_id = bdm.category_id - and bd.bundle_id = bdm.object_id - and bdm.category_id = tsd.model_category_id - and tsd.domain = :subjectDomain - and bd.bundle_id = id.parent_id - and id.item_id = ddm.item_id - and ddm.dublin_id = dd.dublin_id - and dd.keywords like :keyword || '%' - and id.type_id = t.type_id - and id.version = 'live' - and id.item_id = o.object_id - and pd.item_id = id.item_id - and id.item_id != :itemID - and bdm.object_id not in ( - select bnm.object_id - from cat_object_category_map bnm, - cat_cat_subcat_trans_index sti - where bnm.category_id = sti.subcategory_id - and sti.category_id = :navCategoryID - ) + select ci.item_id, + ci.master_id, + cp.title, + ct.label, + ao.object_type + from + ( + select distinct bdm.object_id as unique_bundle_id + from + cat_object_category_map bsm, + cat_object_category_map bdm, + trm_terms tsd, + cms_bundles cb + where bsm.object_id = :bundleID + and bsm.category_id = bdm.category_id + and bdm.category_id = tsd.model_category_id + and tsd.domain = :subjectDomain + and cb.bundle_id = bdm.object_id + and bdm.object_id not in ( + select bnm.object_id + from cat_object_category_map bnm, + cat_cat_subcat_trans_index sti + where bnm.category_id = sti.subcategory_id + and sti.category_id = :navCategoryID + )) unique_bundles, + cms_items ci, + cms_pages cp, + acs_objects ao, + content_types ct, + ldn_dublin_core_items dci, + ldn_dublin_core_item_map dcim + where ci.parent_id = unique_bundles.unique_bundle_id + and ci.item_id = cp.item_id + and ci.item_id = ao.object_id + and ci.item_id != :itemID + and ci.type_id = ct.type_id + and ci.item_id = dcim.item_id + and dcim.dublin_id = dci.dublin_id + and dci.keywords like :keyword || '%' + and ci.version = 'live' } map { - itemID = id.item_id; - workingID = id.master_id; - title = pd.title; - type = t.label; - objectType = o.object_type; + itemID = ci.item_id; + workingID = ci.master_id; + title = cp.title; + type = ct.label; + objectType = ao.object_type; } } |
