From abd49b8c5c1b11c8cd00146abd4521231b0baff5 Mon Sep 17 00:00:00 2001 From: Banana Date: Sat, 2 Jan 2021 13:07:29 +0100 Subject: [PATCH] db query where needed. Reduced db queries in collection view --- TODO | 1 - webclient/lib/mancubus.class.php | 75 +++++++++++-------- webclient/lib/tentacle.class.php | 1 + webclient/lib/trite.class.php | 5 +- .../default/advancedsearch/advancedsearch.php | 8 +- .../view/default/collections/collections.php | 44 ++++++----- webclient/view/default/entry/field-date.html | 2 +- .../default/entry/field-lookupmultiple.html | 2 +- .../view/default/entry/field-number.html | 2 +- .../view/default/entry/field-selection.html | 2 +- webclient/view/default/entry/field-year.html | 2 +- .../managecollectionfields.html | 4 +- webclient/view/default/tags/tags.html | 2 +- 13 files changed, 85 insertions(+), 65 deletions(-) diff --git a/TODO b/TODO index 44c5d05..76ad323 100644 --- a/TODO +++ b/TODO @@ -7,4 +7,3 @@ * delete of a collection * sort by filter for collection display * responsive and breakpoints -* DB query log and optimization diff --git a/webclient/lib/mancubus.class.php b/webclient/lib/mancubus.class.php index fb644e1..c88e4cd 100644 --- a/webclient/lib/mancubus.class.php +++ b/webclient/lib/mancubus.class.php @@ -52,6 +52,20 @@ class Mancubus { */ private $_queryOptions; + /** + * Store the all the values for an entry from lookup table + * + * @var array + */ + private $_cacheLookupValuesForEntry = array(); + + /** + * Store entryFields for run time + * + * @var array + */ + private $_cacheEntryFields = array(); + /** * Mancubus constructor. * @@ -313,20 +327,9 @@ class Mancubus { public function getEntriesByFieldValue($fieldId, $fieldValue) { $ret = array(); - $fieldData = array(); - $queryStr = "SELECT `identifier`, `type`, `id`, `searchtype` FROM `".DB_PREFIX."_sys_fields` - WHERE `id` = '".$this->_DB->real_escape_string($fieldId)."'"; - if(QUERY_DEBUG) error_log("[QUERY] ".__METHOD__." query: ".var_export($queryStr,true)); - try { - $query = $this->_DB->query($queryStr); - if($query !== false && $query->num_rows > 0) { - if(($result = $query->fetch_assoc()) != false) { - $fieldData = $result; - } - } - } - catch (Exception $e) { - error_log("[ERROR] ".__METHOD__." mysql catch: ".$e->getMessage()); + $_entryFields = $this->_getEntryFields(); + if(isset($_entryFields[$fieldId])) { + $fieldData = $_entryFields[$fieldId]; } if(empty($fieldData)) return $ret; @@ -458,11 +461,14 @@ class Mancubus { * @return array */ private function _getEntryFields() { - $ret = array(); + + if(!empty($this->_cacheEntryFields)) { + return $this->_cacheEntryFields; + } if(!empty($this->_collectionId)) { $queryStr = "SELECT `cf`.`fk_field_id` AS id, `sf`.`type`, `sf`.`displayname`, `sf`.`identifier`, - `sf`.`value` AS preValue, `sf`.`apiinfo` + `sf`.`value` AS preValue, `sf`.`apiinfo` , `sf`.`searchtype` FROM `".DB_PREFIX."_collection_fields_".$this->_DB->real_escape_string($this->_collectionId)."` AS cf LEFT JOIN `".DB_PREFIX."_sys_fields` AS sf ON `cf`.`fk_field_id` = `sf`.`id` ORDER BY `cf`.`sort`"; @@ -471,7 +477,7 @@ class Mancubus { $query = $this->_DB->query($queryStr); if($query !== false && $query->num_rows > 0) { while(($result = $query->fetch_assoc()) != false) { - $ret[$result['id']] = $result; + $this->_cacheEntryFields[$result['id']] = $result; } } } @@ -480,7 +486,7 @@ class Mancubus { } } - return $ret; + return $this->_cacheEntryFields; } /** @@ -526,21 +532,28 @@ class Mancubus { $ret = array(); if(!empty($entryId) && !empty($fieldData) && !empty($this->_collectionId)) { - $queryStr = "SELECT `value` - FROM `".DB_PREFIX."_collection_entry2lookup_".$this->_DB->real_escape_string($this->_collectionId)."` - WHERE `fk_field` = '".$this->_DB->real_escape_string($fieldData['id'])."' - AND `fk_entry` = '".$this->_DB->real_escape_string($entryId)."'"; - if(QUERY_DEBUG) error_log("[QUERY] ".__METHOD__." query: ".var_export($queryStr,true)); - try { - $query = $this->_DB->query($queryStr); - if($query !== false && $query->num_rows > 0) { - while(($result = $query->fetch_assoc()) != false) { - $ret[] = $result['value']; - } + + // avoid db query for each wanted value + if(isset($this->_cacheLookupValuesForEntry[$this->_collectionId])) { + if(isset($this->_cacheLookupValuesForEntry[$this->_collectionId][$entryId][$fieldData['id']])) { + $ret = $this->_cacheLookupValuesForEntry[$this->_collectionId][$entryId][$fieldData['id']]; } } - catch (Exception $e) { - error_log("[ERROR] ".__METHOD__." mysql catch: ".$e->getMessage()); + else { + $queryStr = "SELECT `fk_field`, `value`, `fk_entry` + FROM `".DB_PREFIX."_collection_entry2lookup_".$this->_DB->real_escape_string($this->_collectionId)."`"; + if(QUERY_DEBUG) error_log("[QUERY] ".__METHOD__." query: ".var_export($queryStr,true)); + try { + $query = $this->_DB->query($queryStr); + if($query !== false && $query->num_rows > 0) { + while(($result = $query->fetch_assoc()) != false) { + $this->_cacheLookupValuesForEntry[$this->_collectionId][$result['fk_entry']][$result['fk_field']][$result['value']] = $result['value']; + } + } + } + catch (Exception $e) { + error_log("[ERROR] ".__METHOD__." mysql catch: ".$e->getMessage()); + } } } diff --git a/webclient/lib/tentacle.class.php b/webclient/lib/tentacle.class.php index cbe4bfe..7749678 100644 --- a/webclient/lib/tentacle.class.php +++ b/webclient/lib/tentacle.class.php @@ -59,6 +59,7 @@ class Tentacle { FROM `".DB_PREFIX."_tool` WHERE ".$this->_User->getSQLRightsString()." AND `action` = '".$this->_DB->real_escape_string($identifier)."'"; + if(QUERY_DEBUG) error_log("[QUERY] ".__METHOD__." query: ".var_export($queryStr,true)); try { $query = $this->_DB->query($queryStr); if ($query !== false && $query->num_rows > 0) { diff --git a/webclient/lib/trite.class.php b/webclient/lib/trite.class.php index 7a2ea8b..31f3046 100644 --- a/webclient/lib/trite.class.php +++ b/webclient/lib/trite.class.php @@ -117,6 +117,7 @@ class Trite { LEFT JOIN `".DB_PREFIX."_group` AS g ON `c`.`group` = `g`.`id` WHERE ".$this->_User->getSQLRightsString($right, "c")." AND `c`.`id` = '".$this->_DB->real_escape_string($id)."'"; + if(QUERY_DEBUG) error_log("[QUERY] ".__METHOD__." query: ".var_export($queryStr,true)); try { $query = $this->_DB->query($queryStr); if ($query !== false && $query->num_rows > 0) { @@ -124,7 +125,6 @@ class Trite { $this->_id = $this->_collectionData['id']; } } catch (Exception $e) { - if(DEBUG) error_log("[DEBUG] ".__METHOD__." mysql query: ".$queryStr); error_log("[ERROR] ".__METHOD__." mysql catch: ".$e->getMessage()); } } @@ -165,6 +165,7 @@ class Trite { LEFT JOIN `".DB_PREFIX."_group` AS g ON `c`.`group` = `g`.`id` WHERE ".$this->_User->getSQLRightsString("read", "c")." ORDER BY `c`.`name`"; + if(QUERY_DEBUG) error_log("[QUERY] ".__METHOD__." query: ".var_export($queryStr,true)); try { $query = $this->_DB->query($queryStr); @@ -194,6 +195,7 @@ class Trite { FROM `".DB_PREFIX."_collection_fields_".$this->_id."` AS cf LEFT JOIN `".DB_PREFIX."_sys_fields` AS sf ON `cf`.`fk_field_id` = `sf`.`id` ORDER BY `cf`.`sort`"; + if(QUERY_DEBUG) error_log("[QUERY] ".__METHOD__." query: ".var_export($queryStr,true)); $query = $this->_DB->query($queryStr); try { if($query !== false && $query->num_rows > 0) { @@ -202,7 +204,6 @@ class Trite { } } } catch (Exception $e) { - if(DEBUG) error_log("[DEBUG] ".__METHOD__." mysql query: ".$queryStr); error_log("[ERROR] ".__METHOD__." mysql catch: ".$e->getMessage()); } diff --git a/webclient/view/default/advancedsearch/advancedsearch.php b/webclient/view/default/advancedsearch/advancedsearch.php index baefff0..7443331 100644 --- a/webclient/view/default/advancedsearch/advancedsearch.php +++ b/webclient/view/default/advancedsearch/advancedsearch.php @@ -61,9 +61,11 @@ if(!empty($_collection)) { $_ms = count($_matches[0]); for($i=0;$i<$_ms;$i++) { $_cn = trim(str_replace(':','',$_matches[1][$i])); - $_sData[$i]['colName'] = $_cn; - $_sData[$i]['colValue'] = trim(str_replace($_matches[1][$i],'',$_matches[0][$i])); - $_sData[$i]['fieldData'] = $TemplateData['collectionFields'][$_cn]; + if(isset($TemplateData['collectionFields'][$_cn])) { + $_sData[$i]['colName'] = $_cn; + $_sData[$i]['colValue'] = trim(str_replace($_matches[1][$i],'',$_matches[0][$i])); + $_sData[$i]['fieldData'] = $TemplateData['collectionFields'][$_cn]; + } } $TemplateData['entries'] = $Mancubus->getEntries($_sData); diff --git a/webclient/view/default/collections/collections.php b/webclient/view/default/collections/collections.php index dd10b1c..4372912 100644 --- a/webclient/view/default/collections/collections.php +++ b/webclient/view/default/collections/collections.php @@ -27,24 +27,24 @@ if(isset($_GET['collection']) && !empty($_GET['collection'])) { $_collection = Summoner::validate($_collection,'digit') ? $_collection : false; } -// field id to search within +// field identifier to search within $_fid = false; if(isset($_GET['fid']) && !empty($_GET['fid'])) { $_fid = trim($_GET['fid']); - $_fid = Summoner::validate($_fid,'digit') ? $_fid : false; + $_fid = Summoner::validate($_fid,'nospace') ? $_fid : false; } // field value to look up $_fv = false; if(isset($_GET['fv']) && !empty($_GET['fv'])) { $_fv = trim($_GET['fv']); - $_fv = Summoner::validate($_fv,'text') ? $_fv : false; + $_fv = Summoner::validate($_fv) ? $_fv : false; } $_search = false; if(isset($_POST['navSearch'])) { $_search = trim($_POST['navSearch']); - $_search = Summoner::validate($_search,'text') ? $_search : false; + $_search = Summoner::validate($_search) ? $_search : false; } require_once(Summoner::themefile('system/pagination_before.php',UI_THEME)); @@ -67,26 +67,30 @@ if(!empty($_collection)) { $TemplateData['entryLinkPrefix'] = "index.php?p=entry&collection=".$Trite->param('id'); $TemplateData['searchAction'] = 'index.php?p=collections&collection='.$Trite->param('id'); - if (!empty($_fv) && !empty($_fid)) { - $TemplateData['entries'] = $Mancubus->getEntriesByFieldValue($_fid, $_fv); - $TemplateData['search'] = $_fv; + $_fd = $Trite->getCollectionFields(); + $_sdata = array(); + if (!empty($_fv) && !empty($_fid)) { + $_sdata[0] = array( + 'colName' => $_fd[$_fid]['identifier'], + 'colValue' => $_fv, + 'fieldData' => $_fd[$_fid] + ); + $_search = $_fv; $TemplateData['pagination']['currentGetParameters']['fid'] = $_fid; $TemplateData['pagination']['currentGetParameters']['fv'] = $_fv; - } else { - $_fd = $Trite->getCollectionFields(); - $TemplateData['entries'] = $Mancubus->getEntries( - array( - 0 => array( - 'colName' => $Trite->param('defaultSearchField'), - 'colValue' => $_search, - 'fieldData' =>$_fd[$Trite->param('defaultSearchField')] - ) - ) + } + else { + $_sdata[0] = array( + 'colName' => $Trite->param('defaultSearchField'), + 'colValue' => $_search, + 'fieldData' =>$_fd[$Trite->param('defaultSearchField')] ); - if (!empty($_search)) { - $TemplateData['search'] = $_search; - } + } + + $TemplateData['entries'] = $Mancubus->getEntries($_sdata); + if (!empty($_search)) { + $TemplateData['search'] = $_search; } } else { diff --git a/webclient/view/default/entry/field-date.html b/webclient/view/default/entry/field-date.html index 8909717..7a6769c 100644 --- a/webclient/view/default/entry/field-date.html +++ b/webclient/view/default/entry/field-date.html @@ -1,4 +1,4 @@

: - +

diff --git a/webclient/view/default/entry/field-lookupmultiple.html b/webclient/view/default/entry/field-lookupmultiple.html index e15139c..f317544 100644 --- a/webclient/view/default/entry/field-lookupmultiple.html +++ b/webclient/view/default/entry/field-lookupmultiple.html @@ -2,7 +2,7 @@ : '.$_fv.', '; + echo ''.$_fv.', '; } ?>

diff --git a/webclient/view/default/entry/field-number.html b/webclient/view/default/entry/field-number.html index 951b94e..eb20d83 100644 --- a/webclient/view/default/entry/field-number.html +++ b/webclient/view/default/entry/field-number.html @@ -1,4 +1,4 @@

: - +

diff --git a/webclient/view/default/entry/field-selection.html b/webclient/view/default/entry/field-selection.html index 3667f9a..4f149de 100644 --- a/webclient/view/default/entry/field-selection.html +++ b/webclient/view/default/entry/field-selection.html @@ -1,4 +1,4 @@

: - +

diff --git a/webclient/view/default/entry/field-year.html b/webclient/view/default/entry/field-year.html index 0376451..c3dfbf5 100644 --- a/webclient/view/default/entry/field-year.html +++ b/webclient/view/default/entry/field-year.html @@ -1,4 +1,4 @@

: - +

diff --git a/webclient/view/default/managecollectionfields/managecollectionfields.html b/webclient/view/default/managecollectionfields/managecollectionfields.html index c343adc..fce9fff 100644 --- a/webclient/view/default/managecollectionfields/managecollectionfields.html +++ b/webclient/view/default/managecollectionfields/managecollectionfields.html @@ -7,8 +7,8 @@ Just use drag and drop below to add, remove or order your fields.
Removing a field will remove the stored data from the collection.
- Make sure at least the title - field is available. + Make sure at least the title, cover image + and description fields are available.

$v) { ?> diff --git a/webclient/view/default/tags/tags.html b/webclient/view/default/tags/tags.html index 4034f18..14a915d 100644 --- a/webclient/view/default/tags/tags.html +++ b/webclient/view/default/tags/tags.html @@ -19,7 +19,7 @@

$ev) { ?> - , + ,
-- 2.39.5