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

[Robie Basak]

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.

0.62 breaks for me with "Can't locate object method "new" via package
"RT::Handle" at /usr/local/rt2/lib/RT.pm line 26.", so I'm still on
0.61; the Changes file doesn't seem to say anything about this issue
though.

I tried the following:

my $tickets = new RT::Tickets(RT::SystemUser) or die;
$tickets->LimitRequestor(VALUE => "robie\@principle.co.uk",
ENTRYAGGREGATOR => "OR");
$tickets->LimitCc(VALUE => "robie\@principle.co.uk", ENTRYAGGREGATOR =>
"AND");

while(my $ticket = $tickets->Next()) {
  print $ticket->id . "\n";
}

The SQL query this produced was:

SELECT DISTINCT main.* FROM Tickets main, Watchers Watchers_1, Watchers
Watchers_3  LEFT JOIN Users as Users_2  ON  Watchers_1.Owner =
Users_2.id LEFT JOIN Users as Users_4  ON  Watchers_3.Owner =
Users_4.id  WHERE ((Watchers_3.Type = 'Cc')) AND ((Watchers_3.Scope =
'Ticket')) AND ((main.EffectiveId = main.id)) AND ((Watchers_1.Scope =
'Ticket')) AND ((Watchers_1.Type = 'Requestor')) AND
((lower(Watchers_1.Email) =
'robie at principle.co.uk')OR(lower(Users_2.EmailAddress) =
'robie at principle.co.uk')OR(lower(Watchers_3.Email) =
'robie at principle.co.uk')OR(lower(Users_4.EmailAddress) =
'robie at principle.co.uk'))  AND main.id = Watchers_1.Value  AND main.id =
Watchers_3.Value

The error produced from postgres: 
    ERROR: Relation "watchers_1" does not exist

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. Making this change and executing the query
manually works, IMHO the query as it is produced makes no sense, which
postgres correctly complains about.

I did some digging around in the code for DBIx::SearchBuilder;
specifically the functions _DoSearch and _DoCount, which both call the
two functions _TableAliases and _LeftJoins to build the query.

This is where I think the problem is; the TableAliases and the LeftJoins
need to be produced together, since the correct syntax for a join is
done together with the alias at once (according to the postgres manual;
I don't know about mysql).

I tried removing _LeftJoins, making instead a function called _LeftJoin
which is called inside _TableAliases to produce the LEFT JOIN clause if
it exists for each alias as the aliases get written out. This doesn't
work, however, because there is no way to pull the correct LEFT JOIN out
given the ALIAS clause being written, without actually trying to parse
the SQL fragments already inside the $self->{'aliases'} and the
$self->{'left_joins'} hashes.

This seems like a design flaw in the internal data structures the
package holds; it is converting to SQL without any more data available
to build the query later.

I'll happily go and modify it, because I need the functionality now. I
do want to see my changes integrated though, because I don't want to
have to deal with the patch breaking every time a new version comes out
:)

So what will be accepted? If I make changes to the internal data
structure, is that OK? I'm basically wanting to store a list of aliases
and joins made in a proper data structure as opposed to SQL, so that
when we need to make a query this can actually be pulled out again.

Just to reiterate; I'll happily make and contribute the changes myself
if someone would just agree on the fact that there's something wrong and
that my fix will be accepted :)

Cheers,
Robie.