[TikiWiki-commits] [Git][tikiwiki/tiki][28.x] unified relation optimization

Victor Emanouilov pushed to branch 28.x at Tiki Wiki CMS Groupware / Tiki


Commits:
66044c0a by Victor Emanouilov at 2025-11-21T12:08:11+02:00
unified relation optimization
---
* [FIX] cache relation fields only when rebuilding and we need the cache

* [FIX] relation cache: skip populating if already populated

* [FIX] relation caching of trackeritem formatted titles - do it once per item in bulk, reuse item cache when formatting the titles

* [FIX] phpcs

* [ENH] search index: relation formatted values pre-cache to speed up indexing - optional via preference

See merge request tikiwiki/tiki!9051

- - - - -


7 changed files:

- lib/attributes/relationlib.php
- lib/core/Search/ContentSource/TrackerItemSource.php
- lib/core/Search/Indexer.php
- lib/core/Search/Manticore/PdoClient.php
- lib/core/Tracker/Field/Relation.php
- lib/prefs/unified.php
- templates/admin/include_search.tpl


Changes:

=====================================
lib/attributes/relationlib.php
=====================================
@@ -477,6 +477,12 @@ class RelationLib extends TikiDb_Bridge
         return $this->table->update(['metadata_itemId' => $metadata_item_id], ['relationId' => $id]);
     }
 
+    public function getAllRelatedTrackerItems()
+    {
+        $rows = $this->table->fetchAll(['source_itemId', 'target_itemId'], ['source_type' => 'trackeritem', 'target_type' => 'trackeritem']);
+        return array_unique(array_merge(array_column($rows, 'source_itemId'), array_column($rows, 'target_itemId')));
+    }
+
     /**
      * @param $relation
      * @param $cond


=====================================
lib/core/Search/ContentSource/TrackerItemSource.php
=====================================
@@ -299,4 +299,47 @@ class Search_ContentSource_TrackerItemSource implements Search_ContentSource_Int
     {
         $this->indexer = $indexer;
     }
+
+    public static function cacheRelationFields()
+    {
+        global $prefs;
+        if (empty($prefs['unified_cache_relation']) || $prefs['unified_cache_relation'] !== 'y') {
+            return;
+        }
+        if (! empty(Tracker_Field_Relation::$relationFieldCache)) {
+            return;
+        }
+        $selectionFields = [];
+        foreach (self::getAllIndexableHandlers() as $handler) {
+            if (! $handler instanceof Tracker_Field_Relation) {
+                continue;
+            }
+            $format = $handler->getOption('format');
+            $pattern = '/\{([\w\.]+)\}/';
+            if (preg_match_all($pattern, $format, $matches)) {
+                $selectionFields = array_merge($selectionFields, $matches[1]);
+            }
+        }
+        $selectionFields = array_unique($selectionFields);
+
+        if (empty($selectionFields)) {
+            return;
+        }
+
+        $relationlib = TikiLib::lib('relation');
+        $relatedItems = $relationlib->getAllRelatedTrackerItems();
+
+        $lib = TikiLib::lib('unifiedsearch');
+        foreach (array_chunk($relatedItems, 500) as $chunk) {
+            $query = $lib->buildQuery([]);
+            foreach ($chunk as $object) {
+                $query->addObject('trackeritem', $object);
+            }
+            $query->setSelectionFields($selectionFields);
+            $result = $query->search($lib->getIndex());
+            foreach ($result as $item) {
+                Tracker_Field_Relation::$relationFieldCache[$item['object_id']] = $item;
+            }
+        }
+    }
 }


=====================================
lib/core/Search/Indexer.php
=====================================
@@ -141,6 +141,8 @@ class Search_Indexer
 
         $timer = new Timer();
 
+        Search_ContentSource_TrackerItemSource::cacheRelationFields();
+
         foreach ($this->contentSources as $objectType => $contentSource) {
             if ($progress) {
                 if (! empty($lastStats['default']['times'][$objectType]) && ! empty($lastStats['default']['counts'][$objectType])) {


=====================================
lib/core/Search/Manticore/PdoClient.php
=====================================
@@ -404,7 +404,9 @@ class PdoClient
             'total' => 0,
         ];
         if ($selectFields) {
-            $sql = 'SELECT ' . implode(', ', $selectFields);
+            $sql = 'SELECT ' . implode(', ', array_filter($selectFields, function ($field) use ($indexFields) {
+                return in_array($field, $indexFields);
+            }));
         } else {
             $sql = 'SELECT object_type, object_id' . (in_array('tracker_id', $indexFields) ? ', tracker_id' : '');
         }


=====================================
lib/core/Tracker/Field/Relation.php
=====================================
@@ -40,6 +40,7 @@ Part of the documentation is at https://dev.tiki.org/Object+Attributes+and+Relat
 class Tracker_Field_Relation extends \Tracker\Field\AbstractItemField implements \Tracker\Field\ExportableInterface, \Tracker\Field\FilterableInterface
 {
     public static $refreshedTargets = [];
+    public static $relationFieldCache = [];
 
     public static function getManagedTypesInfo(): array
     {
@@ -591,14 +592,14 @@ class Tracker_Field_Relation extends \Tracker\Field\AbstractItemField implements
 
     public function getDocumentPart(Search_Type_Factory_Interface $typeFactory, $mode = '')
     {
-        $baseKey = $this->getBaseKey();
+        global $prefs;
 
+        $baseKey = $this->getBaseKey();
         $data = $this->getFieldData();
         $value = $this->getValue();
+        $format = $this->trackerField->getOption('format');
 
         // we don't have all the data in the field definition at this point, so just render the labels here
-        $objectLib = TikiLib::lib('object');
-        $format = $this->trackerField->getOption('format');
         $labels = [];
         if ($mode !== 'formatting') {
             $objects = [];
@@ -609,8 +610,28 @@ class Tracker_Field_Relation extends \Tracker\Field\AbstractItemField implements
                     'metaItemId' => $rel->getMetadataItemId(),
                 ];
             }
-            $labels = $objectLib->get_titles($objects, $format);
-            $labels = array_values($labels);
+            if (! empty(self::$relationFieldCache)) {
+                foreach ($objects as $object) {
+                    if (isset(self::$relationFieldCache[$object['id']])) {
+                        $item = self::$relationFieldCache[$object['id']];
+                        $labels[] = preg_replace_callback('/\{([\w\.]+)\}/', function ($matches) use ($item, $format) {
+                            $key = $matches[1];
+                            if (isset($item[$key])) {
+                                return $item[$key];
+                            } elseif (! $format || $format == '{title}') {
+                                return tr('empty');
+                            } else {
+                                return '';
+                            }
+                        }, $format);
+                    } else {
+                        $labels[] = TikiLib::lib('trk')->get_isMain_value(null, $object['id']);
+                    }
+                }
+            } else {
+                $labels = TikiLib::lib('object')->get_titles($objects, $format);
+                $labels = array_values($labels);
+            }
         }
 
         $plain = implode(', ', $labels);


=====================================
lib/prefs/unified.php
=====================================
@@ -132,6 +132,14 @@ function prefs_unified_list()
             'default' => 'n',
             'tags' => ['advanced'],
         ],
+        'unified_cache_relation' => [
+            'name' => tra('Cache relation field formatted values'),
+            'description' => tr('Relation field formats related objects by searching existing index on each record rebuild. This might be very slow if you have relation fields on many tracker items. Enable this option to pre-cache all possible relation formatted values at indexing time.'),
+            'warning' => tr('This could potentially use a lot of memory (if you have relations on big trackers) or be less efficient (if you have little density of populated relation fields).'),
+            'type' => 'flag',
+            'default' => 'n',
+            'tags' => ['advanced'],
+        ],
         'unified_excluded_categories' => [
             'name' => tra('Excluded categories'),
             'description' => tra('List of category IDs to exclude from the search index'),


=====================================
templates/admin/include_search.tpl
=====================================
@@ -127,6 +127,7 @@
 
                     {preference name=unified_user_cache}
                     {preference name=unified_cache_formatted_result}
+                    {preference name=unified_cache_relation}
                     {preference name=unified_cached_formatters}
                     {preference name=unified_list_cache_default_on}
                     {preference name=unified_list_cache_default_expiry}



View it on GitLab: https://gitlab.com/tikiwiki/tiki/-/commit/66044c0a614837301bd50f01caf80f4faaf46334

-- 
View it on GitLab: https://gitlab.com/tikiwiki/tiki/-/commit/66044c0a614837301bd50f01caf80f4faaf46334
You're receiving this email because of your account on gitlab.com.

_______________________________________________
TikiWiki-cvs mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/tikiwiki-cvs