A simple nested loop

In a previous post, I wrote about a suspected LIMIT problem. It turns out that suspicion proved correct. The solution was to move to a nested query which limits the underlying data and then allows the outer query to grab all the associated fluff that surrounds it.

Here is the fix to the original code:

$ cvs di -ub commits_by_committer.php
Index: commits_by_committer.php
===================================================================
RCS file: /home/repositories/freshports-1/classes/commits_by_committer.php,v
retrieving revision 1.3
diff -u -b -r1.3 commits_by_committer.php
--- commits_by_committer.php    10 Feb 2008 19:17:22 -0000      1.3
+++ commits_by_committer.php    11 Jul 2010 17:36:53 -0000
@@ -89,11 +89,12 @@
                }

                $sql .= "
-         WHERE commit_log.committer = '" . AddSlashes($this->Committer) . "'
-           AND commit_log_elements.commit_log_id = commit_log.id
-           AND commit_log_elements.element_id    = element.id
-   ORDER BY 1 desc,
-                       commit_log_id";
+         WHERE commit_log.id IN (SELECT tmp.id FROM (SELECT DISTINCT CL.id, CL.commit_date
+  FROM element_pathname EP, commit_log_elements CLE, commit_log CL
+ WHERE CL.committer  = '" . AddSlashes($this->Committer) . "'
+   AND EP.element_id = CLE.element_ID
+   AND CL.id         = CLE.commit_log_id
+ORDER BY CL.commit_date DESC ";

                if ($this->Limit) {
                        $sql .= "\nLIMIT " . $this->Limit;
@@ -105,6 +106,13 @@



+
+               $sql .= ")as tmp)
+           AND commit_log_elements.commit_log_id = commit_log.id
+           AND commit_log_elements.element_id    = element.id
+   ORDER BY 1 desc,
+                       commit_log_id";
+
                if ($this->Debug) echo '</pre><pre>' . $sql . '</pre>';

                $this->LocalResult = pg_exec($this->dbh, $sql);
$

The query (greatly simplified), before, the fix was:

SELECT DISTINCT
	commit_log.commit_date - SystemTimeAdjust() AS commit_date_raw,
	commit_log.id                       AS commit_log_id,
	commit_log.encoding_losses          AS encoding_losses,
	commit_log.message_id               AS message_id,
	commit_log.committer                AS committer,
	commit_log.description              AS commit_description
   FROM commit_log, commit_log_elements, element 
  WHERE commit_log.committer = 'jb'
    AND commit_log_elements.commit_log_id = commit_log.id
    AND commit_log_elements.element_id    = element.id
ORDER BY 1 desc, commit_log_id
LIMIT 100

With the fix, the query becomes:

SELECT DISTINCT
	commit_log.commit_date - SystemTimeAdjust()        AS commit_date_raw,
	commit_log.id                                      AS commit_log_id,
	commit_log.encoding_losses                         AS encoding_losses,
	commit_log.message_id                              AS message_id,
	commit_log.committer                               AS committer,
	commit_log.description                             AS commit_description,
   FROM commit_log, commit_log_elements, element 
	  WHERE commit_log.id IN (
   SELECT tmp.id FROM (SELECT DISTINCT CL.id, CL.commit_date
     FROM commit_log CL
    WHERE CL.committer  = 'jb'
    ORDER BY CL.commit_date DESC 
    LIMIT 100
    )as tmp)
    AND commit_log_elements.commit_log_id = commit_log.id
    AND commit_log_elements.element_id    = element.id
ORDER BY 1 desc, commit_log_id