Bug #6733

Optimise queries for linked tables

Details

Request #6733	Optimise queries for linked tables
Submitted	2006-02-09 13:50 UTC
From	anselm at netuxo dot co dot uk
Assigned	justinpatrin
Status	Assigned
Package	DB_DataObject_FormBuilder
PHP Version	4.3.11
OS	All
Roadmaps	(Not assigned)

Comments

[2006-02-09 13:50 UTC] anselm at netuxo dot co dot uk

Description:
------------
When a table is used as a linked field in FormBuilder, the values of interest are those describied in the linkDisplayFields for that table, and the primary key.

However, FormBuilder queries all of the object's fields when creating the form. At times this can add a certain overhead to the execution time !

[2006-02-09 13:57 UTC] anselm at netuxo dot co dot uk

I have written the following patch. It doesn't take all cases into consideration (in particular, if linked fields are themsleves linked fields) ; but helps in the general case :

Index: FormBuilder.php
===================================================================
@@ -1742,8 +1742,53 @@
function prettyName($name) {
return ucwords(strtolower(preg_replace('/[^a-z0-9]/Si', ' ', preg_replace('/([a-z0-9])([A-Z])/S', '\1 \2', $name))));
}
-
+
/**
+ * DB_DataObject_FormBuilder::limitDataObjectSelect()
+ *
+ * Prepares a DataObject such that only the specified fields are
+ * queried when that DataObject's find() method is called.
+ *
+ * May be called statically.
+ *
+ * Will use display field configurations from these locations, in this order:
+ * 1) $displayFields parameter
+ * 2) the fb_linkDisplayFields member variable of the dataobject
+ * 3) the linkDisplayFields member variable of this class (if not called statically)
+ * 4) all fields returned by the DO's table() function
+ *
+ * @param DB_DataObject the dataobject to prepare
+ * @param mixed field to use to display, may be an array with field names or a single field.
+ * Will only be used for this DO, not linked DOs. If you wish to set the display fields
+ * all DOs the same, set the option in the FormBuilder class instance.
+ * @param boolean Set to true (default) to automatically add the primary key(s) of the table
+ * @access public
+ */
+ function limitDataObjectSelect(&$do, $displayFields, $addKey=true) {
+ // Work out the display fields to use
+ if ($displayFields === false) {
+ if (isset($do->fb_linkDisplayFields)) {
+ $displayFields = $do->fb_linkDisplayFields;
+ } elseif (isset($this) && isset($this->linkDisplayFields) && $this->linkDisplayFields) {
+ $displayFields = $this->linkDisplayFields;
+ }
+ if (!$displayFields) {
+ $displayFields = array_keys($do->table());
+ }
+ }
+
+ // Add the primary key
+ if ($addKey) {
+ $displayFields = array_merge($displayFields, $do->keys());
+ }
+ $displayFields = array_unique($displayFields);
+
+ // And make sure only these fields are selected
+ $do->selectAdd();
+ $do->selectAdd(implode(',', $displayFields));
+ }
+
+ /**
* DB_DataObject_FormBuilder::getDataObjectString()
*
* Returns a string which identitfies this dataobject.
@@ -1920,6 +1965,7 @@
$list[''] = $this->selectAddEmptyLabel;
}
// FINALLY, let's see if there are any results
+ $this->limitDataObjectSelect($opts, array_merge(array($valueField), $displayFields));
if ($opts->find() > 0) {
while ($opts->fetch()) {
$list[$opts->$valueField] = $this->getDataObjectString($opts, $displayFields);