Changeset 16034

Timestamp:

2008-06-17T14:22:52+12:00 (16 years ago)

Author:

mdewsnip

Message:

Improved SQL command for sorting documents that match a certain element, value pair. The new version is better structured, shorter, still returns the document if it doesn't have a value for the sorting metadata element, and should be more efficient as well.

File:

• gsdl/trunk/src/lib/sqlitedbclass.cpp (modified) (1 diff)

gsdl/trunk/src/lib/sqlitedbclass.cpp

@@ -112 +112 @@
 
   // Get the entries in the "document_metadata" table where the element and value matches those specified
-  text_t sql_cmd;
-  if (sort_by_metadata_element_name == "")
-  {
-    // No sorting required
-    sql_cmd = "SELECT docOID FROM document_metadata WHERE element='" + sqlite_safe(metadata_element_name) + "' AND value='" + sqlite_safe(metadata_value) + "'";
-  }
-  else
-  {
-    // Sort the documents by a certain metadata element
-    // John Thompson thinks this may not be the most efficient solution, and recommends using ON instead of WHERE
-    sql_cmd = "SELECT b.docOID FROM document_metadata AS a LEFT JOIN document_metadata AS b USING (docOID) WHERE a.element='" + sqlite_safe(metadata_element_name) + "' AND a.value='" + sqlite_safe(metadata_value) + "' AND b.element='" + sqlite_safe(sort_by_metadata_element_name) + "' ORDER BY b.value";
-  }
+  text_t sql_cmd = "SELECT docOID FROM document_metadata WHERE element='" + sqlite_safe(metadata_element_name) + "' AND value='" + sqlite_safe(metadata_value) + "'";
+
+  // If we're sorting the documents by a certain metadata element, extend the SQL command to do this
+  if (sort_by_metadata_element_name != "")
+  {
+    sql_cmd = "SELECT docOID FROM (" + sql_cmd + ") LEFT JOIN (SELECT docOID,value from document_metadata WHERE element='" + sqlite_safe(sort_by_metadata_element_name) + "') USING (docOID) ORDER by value";
+  }
+
+  // Perform the SQL request
   vector<text_tmap> sql_results;
   if (!sqlgetarray(sql_cmd, sql_results) || sql_results.size() == 0)