function getTree($pListHash) { global $gBitDb; $ret = array(); if ($this->mDb->isAdvancedPostgresEnabled()) { $bindVars = array(); $containVars = array(); $selectSql = ''; $joinSql = ''; $whereSql = ''; if (!empty($pListHash['contain_item'])) { $selectSql = " , tfgim3.`item_content_id` AS `in_gallery` "; $joinSql .= " LEFT OUTER JOIN `" . BIT_DB_PREFIX . "fisheye_gallery_image_map` tfgim3 ON (tfgim3.`gallery_content_id`=lc.`content_id`) AND tfgim3.`item_content_id`=? "; $bindVars[] = $pListHash['contain_item']; $containVars[] = $pListHash['contain_item']; } if (isset($pListHash['contain_item'])) { // contain item might have squeaked in as 0, clear our from pListHash unset($pListHash['contain_item']); } foreach ($pListHash as $key => $val) { $whereSql .= " {$key}=? AND "; $bindVars[] = $val; } $query = "SELECT lc.`content_id` AS `hash_key`, fg.*, lc.* {$selectSql}\n\t\t\t\t\t\tFROM `" . BIT_DB_PREFIX . "fisheye_gallery` fg\n\t\t\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON(fg.`content_id`=lc.`content_id`)\n\t\t\t\t\t\t\t{$joinSql}\n\t\t\t\t\t\tWHERE {$whereSql} NOT EXISTS (SELECT gallery_content_id FROM fisheye_gallery_image_map tfgim2 WHERE tfgim2.item_content_id=lc.content_id)\n\t\t\t\t\t\tORDER BY lc.title"; $rootContent = $gBitDb->GetAssoc($query, $bindVars); foreach (array_keys($rootContent) as $conId) { $splitVars = array(); $query = "SELECT branch AS hash_key, * {$selectSql}\n\t\t\t\t\t\t FROM connectby('`" . BIT_DB_PREFIX . "fisheye_gallery_image_map`', '`item_content_id`', '`gallery_content_id`', ?, 0, '/') AS t(cb_item_content_id int,cb_gallery_content_id int, level int, branch text)\n\t\t\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "fisheye_gallery` fg ON (fg.`content_id`=cb_item_content_id)\n\t\t\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON(lc.`content_id`=fg.`content_id`)\n\t\t\t\t\t\t\t{$joinSql}\n\t\t\t\t\t\t ORDER BY branch, lc.`title`"; $splitVars[] = $conId; if (!empty($containVars)) { $splitVars[] = $containVars[0]; } FisheyeGallery::splitConnectByTree($ret, $gBitDb->GetAssoc($query, $splitVars)); FisheyeGallery::getTreeSort($ret); } } else { if ($this->mDb->mType == 'firebird') { $bindVars = array(); $containVars = array(); $selectSql = ''; $joinSql = ''; $whereSql = ''; if (!empty($pListHash['contain_item'])) { $selectSql = " , tfgim3.`item_content_id` AS `in_gallery` "; $joinSql .= " LEFT OUTER JOIN `" . BIT_DB_PREFIX . "fisheye_gallery_image_map` tfgim3 ON (tfgim3.`gallery_content_id`=lc.`content_id`) AND tfgim3.`item_content_id`=? "; $bindVars[] = $pListHash['contain_item']; $containVars[] = $pListHash['contain_item']; } $this->getServicesSql('content_list_sql_function', $selectSql, $joinSql, $whereSql, $bindVars); if (isset($pListHash['contain_item'])) { // contain item might have squeaked in as 0, clear our from pListHash unset($pListHash['contain_item']); } foreach ($pListHash as $key => $val) { $whereSql .= " AND lc.{$key}=? "; $bindVars[] = $val; } $splitVars = array(); $query = "WITH RECURSIVE\n\t\t\t\t\t\t\t\tGALLERY_TREE AS (\n\t\t\t\t\t\t\t\tSELECT B.`content_id` AS gallery_content_id, B.`content_id` AS item_content_id, 0 AS BLEVEL, CAST( lcp.`title` AS VARCHAR(255) ) AS BRANCH, 0 AS gallery_parent_id\n\t\t\t\t\t\t\t\tFROM `" . BIT_DB_PREFIX . "fisheye_gallery` B\n\t\t\t\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lcp ON(lcp.`content_id`=B.`content_id`)\n\t\t\t\t\t\t\t\tWHERE NOT EXISTS (SELECT gallery_content_id FROM fisheye_gallery_image_map tfgim2 WHERE tfgim2.item_content_id=B.content_id)\n\n\t\t\t\t\t\t\t\tUNION ALL\n\n\t\t\t\t\t\t\t\tSELECT `item_content_id` AS gallery_content_id, `item_content_id`, G.BLEVEL + 1, G.BRANCH || '/' || `item_content_id` AS BRANCH, `gallery_content_id` AS gallery_parent_id\n\t\t\t\t\t\t\t\tFROM `" . BIT_DB_PREFIX . "fisheye_gallery_image_map` G1\n\t\t\t\t\t\t\t\tJOIN GALLERY_TREE G\n\t\t\t\t\t\t\t\tON G1.`gallery_content_id` = G.`item_content_id`\n\t\t\t\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lcg1 ON(lcg1.`content_id`=`item_content_id`) and lcg1.`content_type_guid` = 'fisheyegallery'\n\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\tSELECT T.BRANCH AS hash_key, T.BLEVEL, fg.*, lc.* {$selectSql}\n\t\t\t\t\t\t\tFROM GALLERY_TREE T\n\t\t\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "fisheye_gallery` fg ON (fg.`content_id`=T.`gallery_content_id`)\n\t\t\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON (lc.`content_id`=T.`item_content_id`)\n\t\t\t\t\t\t\tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "fisheye_gallery_image_map` fgimo ON (fgimo.`gallery_content_id`=T.gallery_parent_id) AND fgimo.`item_content_id`=T.gallery_content_id\n\t\t\t\t\t\t\t{$joinSql}\n\t\t\t\t\t\t\tWHERE lc.`content_type_guid` = 'fisheyegallery' {$whereSql}\n\t\t\t\t\t\t ORDER BY T.BRANCH, fgimo.`item_position`"; if (!empty($bindVars)) { FisheyeGallery::splitConnectByTree($ret, $gBitDb->GetAssoc($query, $bindVars)); } else { FisheyeGallery::splitConnectByTree($ret, $gBitDb->GetAssoc($query)); } } else { // this needs replacing with a more suitable list query ... $pListHash['show_empty'] = TRUE; $galList = $this->getList($pListHash); // index by content_id foreach ($galList as $galId => $gal) { $ret[$gal['content_id']] = $gal; } FisheyeGallery::splitConnectByTree($ret, $ret); FisheyeGallery::getTreeSort($ret); } } return $ret; }