[Mapbender-commits] r10130 - trunk/mapbender/http/php

[svn_mapbender at osgeo.org]

Author: armin11
Date: 2019-05-21 03:13:39 -0700 (Tue, 21 May 2019)
New Revision: 10130

Modified:
   trunk/mapbender/http/php/tagCloud.php
Log:
Count only resources which are searchable - TODO: maybe filter for publishing organization has to be set too!

Modified: trunk/mapbender/http/php/tagCloud.php
===================================================================
--- trunk/mapbender/http/php/tagCloud.php	2019-05-21 08:15:54 UTC (rev 10129)
+++ trunk/mapbender/http/php/tagCloud.php	2019-05-21 10:13:39 UTC (rev 10130)
@@ -131,15 +131,21 @@
 		$categoryFilter = "inspire_category";
 	}
 	$sql = "select a.".$categoryFilter."_code_".$languageCode.", a.".$categoryFilter."_description_".$languageCode.", a.".$categoryFilter."_uri, a.".$categoryFilter."_id,sum(a.count) from (";
-	$sql .= "(select ".$categoryFilter."_code_".$languageCode.",".$categoryFilter."_description_".$languageCode.",".$categoryFilter."_uri,".$categoryFilter."_id,  count(*) from ".$categoryFilter." INNER JOIN  layer_".$categoryFilter."  ON (layer_".$categoryFilter.".fkey_".$categoryFilter."_id = ".$categoryFilter.".".$categoryFilter."_id) GROUP BY ".$categoryFilter.".".$categoryFilter."_code_".$languageCode.",".$categoryFilter.".".$categoryFilter."_id) ";
-	$sql .= "union ";
-	$sql .= "(select ".$categoryFilter."_code_".$languageCode.",".$categoryFilter."_description_".$languageCode.",".$categoryFilter."_uri,".$categoryFilter."_id, count(*) from  ".$categoryFilter." INNER JOIN  wfs_featuretype_".$categoryFilter."  ON (wfs_featuretype_".$categoryFilter.".fkey_".$categoryFilter."_id = ".$categoryFilter.".".$categoryFilter."_id) GROUP BY ".$categoryFilter.".".$categoryFilter."_code_".$languageCode.",".$categoryFilter.".".$categoryFilter."_id)";
-	$sql .= "union";
-	$sql .= "(select ".$categoryFilter."_code_".$languageCode.",".$categoryFilter."_description_".$languageCode.",".$categoryFilter."_uri,".$categoryFilter."_id, count(*) from ".$categoryFilter." INNER JOIN  wmc_".$categoryFilter."  ON (wmc_".$categoryFilter.".fkey_".$categoryFilter."_id = ".$categoryFilter.".".$categoryFilter."_id)
- GROUP BY ".$categoryFilter.".".$categoryFilter."_code_".$languageCode.",".$categoryFilter.".".$categoryFilter."_id)";
-	$sql .= "union";
-	$sql .= "(select ".$categoryFilter."_code_".$languageCode.",".$categoryFilter."_description_".$languageCode.",".$categoryFilter."_uri,".$categoryFilter."_id, count(*) from ".$categoryFilter." INNER JOIN  mb_metadata_".$categoryFilter."  ON (mb_metadata_".$categoryFilter.".fkey_".$categoryFilter."_id = ".$categoryFilter.".".$categoryFilter."_id)
- GROUP BY ".$categoryFilter.".".$categoryFilter."_code_".$languageCode.",".$categoryFilter.".".$categoryFilter."_id)";
+	$sql .= "(select ".$categoryFilter."_code_".$languageCode.",".$categoryFilter."_description_".$languageCode.",".$categoryFilter."_uri,".$categoryFilter."_id,  count(*) from ".$categoryFilter." INNER JOIN  layer_".$categoryFilter."  ON (layer_".$categoryFilter.".fkey_".$categoryFilter."_id = ".$categoryFilter.".".$categoryFilter."_id) ";
+	$sql .= " WHERE layer_".$categoryFilter.".fkey_layer_id IN (select layer_id from layer where layer_searchable = 1)";
+	$sql .= " GROUP BY ".$categoryFilter.".".$categoryFilter."_code_".$languageCode.",".$categoryFilter.".".$categoryFilter."_id)";
+	$sql .= " union ";
+	$sql .= "(select ".$categoryFilter."_code_".$languageCode.",".$categoryFilter."_description_".$languageCode.",".$categoryFilter."_uri,".$categoryFilter."_id, count(*) from  ".$categoryFilter." INNER JOIN  wfs_featuretype_".$categoryFilter."  ON (wfs_featuretype_".$categoryFilter.".fkey_".$categoryFilter."_id = ".$categoryFilter.".".$categoryFilter."_id)";
+	$sql .= " WHERE wfs_featuretype_".$categoryFilter.".fkey_featuretype_id IN (select featuretype_id from wfs_featuretype where featuretype_searchable = 1)";
+	$sql .= " GROUP BY ".$categoryFilter.".".$categoryFilter."_code_".$languageCode.",".$categoryFilter.".".$categoryFilter."_id)";
+	$sql .= " union ";
+	$sql .= "(select ".$categoryFilter."_code_".$languageCode.",".$categoryFilter."_description_".$languageCode.",".$categoryFilter."_uri,".$categoryFilter."_id, count(*) from ".$categoryFilter." INNER JOIN  wmc_".$categoryFilter."  ON (wmc_".$categoryFilter.".fkey_".$categoryFilter."_id = ".$categoryFilter.".".$categoryFilter."_id)";
+	$sql .= " WHERE wmc_".$categoryFilter.".fkey_wmc_serial_id IN (select wmc_serial_id from mb_user_wmc where wmc_public = 1)";		
+	$sql .= " GROUP BY ".$categoryFilter.".".$categoryFilter."_code_".$languageCode.",".$categoryFilter.".".$categoryFilter."_id)";
+	$sql .= " union ";
+	$sql .= "(select ".$categoryFilter."_code_".$languageCode.",".$categoryFilter."_description_".$languageCode.",".$categoryFilter."_uri,".$categoryFilter."_id, count(*) from ".$categoryFilter." INNER JOIN  mb_metadata_".$categoryFilter."  ON (mb_metadata_".$categoryFilter.".fkey_".$categoryFilter."_id = ".$categoryFilter.".".$categoryFilter."_id)";
+	$sql .= " WHERE mb_metadata_".$categoryFilter.".fkey_metadata_id IN (select metadata_id from mb_metadata where searchable = true) ";
+	$sql .= " GROUP BY ".$categoryFilter.".".$categoryFilter."_code_".$languageCode.",".$categoryFilter.".".$categoryFilter."_id)";
 	$sql .= ") as a";
 	$sql .= " WHERE a.".$categoryFilter."_code_".$languageCode." <> '' GROUP BY a.".$categoryFilter."_code_".$languageCode.", a.".$categoryFilter."_description_".$languageCode.", a.".$categoryFilter."_uri, a.".$categoryFilter."_id "; 		$sql .= "ORDER BY ";
 	switch ($orderBy) {
@@ -156,8 +162,8 @@
 	$sql .= " DESC LIMIT $1";
 	$showName = $categoryFilter.'_code_'.$languageCode;
 }
+//$e = new mb_exception($sql);
 
-
 $v = array($maxObjects);
 $t = array('i');
 $res = db_prep_query($sql,$v,$t);