[rt-devel] [patch] DBIx::Searchbuilder woes

[Robie Basak]

On Thu, 2002-08-22 at 18:10, Robie Basak wrote:
> I've looked a bit further into this, and I reckon there's a bug with
> SearchBuilder (0.61) when there is more than one LEFT JOIN with (at
> least) postgres.

[...]

> My explanation for this is that the first LEFT JOIN is in the wrong
> place; it should be straight after "Watchers Watchers_1" rather than
> after all the aliases...

[...]

Patch below; this puts the LEFT JOIN bits of the query in the right
places. Tested with Postgres, queries which were failing with a syntax
error before are now working. It seems to be generally continuing to
work as well, manual examination of the query makes sense.

But, this is untested with MySQL; the query looks fine to me though (as
far as I know it's standard SQL now and wasn't before), whether MySQL
accepts it or not needs to be tested.

Lines I've added are Copyright 2002 Northern Principle Limited and is
free software; you can redistribute it and/or modify it under the same
terms as Perl itself (hope that's OK, please ask me if it's an issue).

Robie.


--- SearchBuilder.pm.orig	2002-08-23 16:25:32.000000000 +0100
+++ SearchBuilder.pm	2002-08-27 10:19:47.000000000 +0100
@@ -78,10 +78,11 @@
     $self->{'order'} = "";
     $self->{'alias_count'} = 0;
     $self->{'first_row'} = 0;
-    @{$self->{'aliases'}} = ();
+    $self->{'aliases'} = {};
 
     delete $self->{'items'} if (defined $self->{'items'});
     delete $self->{'left_joins'} if (defined $self->{'left_joins'});
+    delete $self->{'left_join_aliases'} if(defined $self->{'left_join_aliases'});
     delete $self->{'raw_rows'} if (defined $self->{'raw_rows'});
     delete $self->{'subclauses'} if (defined $self->{'subclauses'});
     delete $self->{'restrictions'} if (defined $self->{'restrictions'});
@@ -108,8 +109,6 @@
     
     $QueryString = "SELECT DISTINCT main.* FROM " . $self->_TableAliases . " " ;
     
-    $QueryString .= $self->_LeftJoins . " ";
-
     $QueryString .= $self->_WhereClause . " ".  $self->{'table_links'}. " " 
       if ($self->_isLimited > 0);
    
@@ -197,8 +196,6 @@
     
     $QueryString = "SELECT count(main.id) FROM " . $self->_TableAliases . " ";
 
-    $QueryString .= $self->_LeftJoins . " ";
-   
     $QueryString .= $self->_WhereClause . " ".  $self->{'table_links'}. " " 
       if ($self->_isLimited > 0);
 
@@ -730,7 +727,7 @@
     # go through all the other aliases we set up and build the compiled
     # aliases string
     my $compiled_aliases = join (", ", $self->{'table'} . " main", 
-				       @{$self->{'aliases'}});
+    	map { $self->{'aliases'}{$_} . " $_" . $self->_LeftJoin($_) } keys %{$self->{'aliases'}});
         
     return ($compiled_aliases);
 }
@@ -898,10 +895,8 @@
     
     my $alias = $self->_GetAlias($table);
     
-    my $subclause = "$table $alias";
+    $self->{'aliases'}{$alias} = $table;
 
-    push (@{$self->{'aliases'}}, $subclause);
-    
     return $alias;
 }
 # }}}
@@ -927,21 +922,28 @@
 
 # }}}
 
-# {{{ sub _LeftJoins
+# {{{ sub _LeftJoin
 
-# Return the left joins clause
+# Return the left join clause for a given table alias, if it exists,
+# otherwise return an empty string
 
-sub _LeftJoins {
+sub _LeftJoin {
     my $self = shift;
+    my ($alias) = @_;
+
     my $join_clause = '';
-    foreach my $join (keys %{ $self->{'left_joins'}}) {
+    if ($self->{'left_join_aliases'}{$alias}) {
+    	my $join = $self->{'left_join_aliases'}{$alias};
 	$join_clause .=  $self->{'left_joins'}{$join}{'alias_string'}." ON ";
 	$join_clause .= join(' AND ', values %{$self->{'left_joins'}{$join}{'criteria'}});
+	return ($join_clause);
+    } else {
+    	return ("");
     }
-    
-    return ($join_clause);
 }
 
+# }}}
+
 
 # {{{ sub Join
 
@@ -979,7 +981,8 @@
 	
 	$self->{'left_joins'}{"$alias"}{'alias_string'} =   
 	  " LEFT JOIN $args{'TABLE2'} as $alias ";
-
+	
+	$self->{'left_join_aliases'}{$args{'ALIAS1'}} = $alias;
 
 	$self->{'left_joins'}{"$alias"}{'criteria'}{'base_criterion'} = 
 	  " $args{'ALIAS1'}.$args{'FIELD1'} = $alias.$args{'FIELD2'}";