DBIx::Searchbuilder woes

RT Developers
1

Hi,

I’m trying to use DBIx::SearchBuilder, but failing miserably.

I’m trying to change the requestor-mode interface so that the open and
closed ticket pages contain a list of everything where the logged-in
user is either the requestor or the Cc (permissions have been set
appropriately).

In WebRT/html/SelfService/Elements/MyRequests, there is:

$MyTickets->LimitRequestor(VALUE =>
$session{‘CurrentUser’}->EmailAddress);

so I added
$MyTickets->LimitCc(VALUE => $session{‘CurrentUser’}->EmailAddress);

This resulted in no records. Presumably it’s doing an AND instead of an
OR.

Searching the archives and googling randomly didn’t seem to help;
DBIx::SearchBuilder->Limit isn’t really documented properly anywhere;
the manpage doesn’t really explain it, and seems to be really outdated.

I dug around in the code, and tried an ENTRYAGGREGATOR = ‘OR’, but what
I don’t understand is that it appears that this is supposed to be the
default anyway.

How does ENTRYAGGREGATOR work? I don’t understand if it’s supposed to
be added to the first Limit call, the second or both. Is it prefix or
postfix or something else?

If someone would explain it I’ll happily document what I learnt.

Cheers,
Robie.

2

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@principle.co.uk’)OR(lower(Users_2.EmailAddress) =
‘robie@principle.co.uk’)OR(lower(Watchers_3.Email) =
‘robie@principle.co.uk’)OR(lower(Users_4.EmailAddress) =
‘robie@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
:slight_smile:

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 :slight_smile:

Cheers,
Robie.

3

You would add the ENTRYAGGREGATOR to the second call of a Limit* routine.

The way it works, is the limit calls set up a where clause for the select
query represented by the DBIx::SearchBuilder class.
With each call to Limit it adds a constraint that is by default ANDed with
the previous constraints. If you want a constraint to be ORed instead of
ANDed then you would add the ENTRYAGGREGATOR=‘OR’ to the limit call that
would appear after the OR in the SQL.
So in your example the query would look something like this:

SELECT * FROM Tickets WHERE Requestor = $session{‘CurrentUser’}->EmailAddress
AND CC = $session{‘CurrentUser’}->EmailAddress;

(With the variable references expanded of course, and the Table name and
selected fields are actually different than what is sent to the database
backend because DBIx::SearchBuilder does some magical massaging of the names
of tables and fields (or at least tables) by way of aliases.

So if you wanted to replace the AND above with OR you would change your
LimitCC call to
$MyTickets->LimitCc(VALUE => $session{‘CurrentUser’}->EmailAddress,
ENTRYAGGREGATOR => ‘OR’);

Just remember that Limit method calls really just append ‘AND|OR Field =
VALUE’ to the WHERE clause of a query. and of course you can do things such
as Like by setting the OPERATOR key in the parameter hash to ‘LIKE’

You are very right about the documentation of DBIx::SearchBuilder though.
I built an application unrelated to RT on top of it. I think it is a great
library, but it is a bear to learn. The way I figured it out was by
examining nearly all of the RT modules and examing the DBIx::SearchBuilder
code indepth, however this is not so bad because Jesse codes better than most
perl programmers.On Thursday 22 August 2002 05:51 am, Robie Basak wrote:

Hi,

I’m trying to use DBIx::SearchBuilder, but failing miserably.

I’m trying to change the requestor-mode interface so that the open and
closed ticket pages contain a list of everything where the logged-in
user is either the requestor or the Cc (permissions have been set
appropriately).

In WebRT/html/SelfService/Elements/MyRequests, there is:

$MyTickets->LimitRequestor(VALUE =>
$session{‘CurrentUser’}->EmailAddress);

so I added
$MyTickets->LimitCc(VALUE => $session{‘CurrentUser’}->EmailAddress);

This resulted in no records. Presumably it’s doing an AND instead of an
OR.

Searching the archives and googling randomly didn’t seem to help;
DBIx::SearchBuilder->Limit isn’t really documented properly anywhere;
the manpage doesn’t really explain it, and seems to be really outdated.

I dug around in the code, and tried an ENTRYAGGREGATOR = ‘OR’, but what
I don’t understand is that it appears that this is supposed to be the
default anyway.

How does ENTRYAGGREGATOR work? I don’t understand if it’s supposed to
be added to the first Limit call, the second or both. Is it prefix or
postfix or something else?

If someone would explain it I’ll happily document what I learnt.

Cheers,
Robie.

rt-devel mailing list
rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel

4

Oh come now, DBIx::SearchBuilder::Record has some decent documentation. It
even includes schmancy examples.

-Matt

5

Oh come now, DBIx::SearchBuilder::Record has some decent documentation. It
even includes schmancy examples.

looks

So it does :slight_smile:

Actually, the docs for ::Record seem great. Unfortunately, I haven’t had
the need to use it yet :slight_smile:

I’m busy just trying to use DBIx::SearchBuilder->Limit to get search
results, and unfortunately the docs for /those/ aren’t really
comprehensive.

But thanks for writing the ::Record docs, when I need to do something to
do with SQL and Perl and searches, I /will/ be using DBIx::SearchBuilder
in general :slight_smile:

Robie.

6

[…]

Just remember that Limit method calls really just append ‘AND|OR Field =
VALUE’ to the WHERE clause of a query. and of course you can do things such
as Like by setting the OPERATOR key in the parameter hash to ‘LIKE’

One question here though; what about brackets? As in if I wanted to
do x OR (y AND z) then does it get interpreted as (x OR y) AND z? Or am
I just expected to know the SQL well enough to write the query in the
correct order?

The “correct” way of doing this would be to define the ENTRYAGGREGATOR
as (say) postfix, so that to do the former you’d call x, y, z, AND, OR
and the latter x, y, OR z, AND (reverse-polish) or something similarly
well-defined. But the current API doesn’t give that flexibility.

So I’m suggesting something like:

$MyTickets->LimitRequestor(VALUE =>
$session{‘CurrentUser’}->EmailAddress);
$MyTickets->LimitCc(VALUE => $session{‘CurrentUser’}->EmailAddress);
$MyTickets->Limit(ENTRYAGGREGATOR => ‘OR’);

Or, for my x, y and z examples above, for (x or (y and z)):

$MyTickets->LimitX(…);
$MyTickets->LimitY(…);
$MyTickets->LimitZ(…);
$MyTickets->Limit(ENTRYAGGREGATOR => ‘AND’);
$MyTickets->Limit(ENTRYAGGREGATOR => ‘OR’);

or for ((x or y) and z):
$MyTickets->LimitX(…);
$MyTickets->LimitY(…);
$MyTickets->Limit(ENTRYAGGREGATOR => ‘OR’);
$MyTickets->LimitZ(…);
$MyTickets->Limit(ENTRYAGGREGATOR => ‘AND’);

This is a non-ambiguous, fairly standard and widespread way of doing it,
and the way I think it “should” work. I’d say that this should be a new
method of doing it in terms of the API (which will require changes to
the code, obviously) and the old method should be deprecated.

What do people think of this?

You are very right about the documentation of DBIx::SearchBuilder though.
I built an application unrelated to RT on top of it. I think it is a great
library, but it is a bear to learn. The way I figured it out was by
examining nearly all of the RT modules and examing the DBIx::SearchBuilder
code indepth, however this is not so bad because Jesse codes better than most
perl programmers.

I agree with you completely; Jesse’s code is great :slight_smile:

I’ve made a huge number of modifications to RT, all of which I’ve only
been able to do because I can grok the code very easily and very
quickly.

Unfortunately most of them have at least some element of hack and so I’m
reluctant to contribute them, but I am busy cleaning things up so
hopefully things which I think might be useful to others will filter
through :slight_smile:

Robie.

7

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Well, RPN sounds like a good idea, but here is the issues.

  1. It would take quite a bit of code rewriting to support it,
    because internally DBIx::SearchBuilder builds the query as you call
    methods. So to keep track of everything till the end would require at
    least an array. And then you have the issue that when you call limit
    in this manner you may be left with an incomplete or invalid query,
    until you make a call to limit with ENTRYAGGREGATOR.

  2. It IMO is not the most intuitive way of looking at things, forgive me
    if I admit my ignorance here, but I’ve always had somewhat of a mental
    block with RPN, it just doesn’t come naturually to me.

After briefly looking through the code, there is a SUBCLAUSE option
to Limit, I’ve never used this option so I’m not entirely sure as to it’s
purpose, and initially looking at the code it is not to me entirely obvious
as to what it is doing.

But that’s just my opinion…I could be wrong.

  • -MichaelOn Friday 23 August 2002 05:14 am, Robie Basak wrote:

On Thu, 2002-08-22 at 19:28, Michael Grubb wrote:

[…]

Just remember that Limit method calls really just append ‘AND|OR Field =
VALUE’ to the WHERE clause of a query. and of course you can do things
such as Like by setting the OPERATOR key in the parameter hash to ‘LIKE’

One question here though; what about brackets? As in if I wanted to
do x OR (y AND z) then does it get interpreted as (x OR y) AND z? Or am
I just expected to know the SQL well enough to write the query in the
correct order?

The “correct” way of doing this would be to define the ENTRYAGGREGATOR
as (say) postfix, so that to do the former you’d call x, y, z, AND, OR
and the latter x, y, OR z, AND (reverse-polish) or something similarly
well-defined. But the current API doesn’t give that flexibility.

So I’m suggesting something like:

$MyTickets->LimitRequestor(VALUE =>
$session{‘CurrentUser’}->EmailAddress);
$MyTickets->LimitCc(VALUE => $session{‘CurrentUser’}->EmailAddress);
$MyTickets->Limit(ENTRYAGGREGATOR => ‘OR’);

Or, for my x, y and z examples above, for (x or (y and z)):

$MyTickets->LimitX(…);
$MyTickets->LimitY(…);
$MyTickets->LimitZ(…);
$MyTickets->Limit(ENTRYAGGREGATOR => ‘AND’);
$MyTickets->Limit(ENTRYAGGREGATOR => ‘OR’);

or for ((x or y) and z):
$MyTickets->LimitX(…);
$MyTickets->LimitY(…);
$MyTickets->Limit(ENTRYAGGREGATOR => ‘OR’);
$MyTickets->LimitZ(…);
$MyTickets->Limit(ENTRYAGGREGATOR => ‘AND’);

This is a non-ambiguous, fairly standard and widespread way of doing it,
and the way I think it “should” work. I’d say that this should be a new
method of doing it in terms of the API (which will require changes to
the code, obviously) and the old method should be deprecated.

What do people think of this?

You are very right about the documentation of DBIx::SearchBuilder though.
I built an application unrelated to RT on top of it. I think it is a
great library, but it is a bear to learn. The way I figured it out was
by examining nearly all of the RT modules and examing the
DBIx::SearchBuilder code indepth, however this is not so bad because
Jesse codes better than most perl programmers.

I agree with you completely; Jesse’s code is great :slight_smile:

I’ve made a huge number of modifications to RT, all of which I’ve only
been able to do because I can grok the code very easily and very
quickly.

Unfortunately most of them have at least some element of hack and so I’m
reluctant to contribute them, but I am busy cleaning things up so
hopefully things which I think might be useful to others will filter
through :slight_smile:

Robie.

rt-devel mailing list
rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE9ZlsBAUDeVla85B8RAszcAKCTpYP9vhX1XgmOJPVq7Qe/yCyEgwCfekf1
YiCuPYubzG/76KqPbF2egKM=
=lv/A
-----END PGP SIGNATURE-----

8

Well, RPN sounds like a good idea, but here is the issues.

  1. It would take quite a bit of code rewriting to support it,
    because internally DBIx::SearchBuilder builds the query as you call
    methods. So to keep track of everything till the end would require at
    least an array. And then you have the issue that when you call limit
    in this manner you may be left with an incomplete or invalid query,
    until you make a call to limit with ENTRYAGGREGATOR.

As for rewriting; yes, it would :slight_smile:

But at the moment all I can see is how limited the search functionality
is, and I can’t see any other way of improving it.

At the moment ENTRYAGGREGATOR defaults to ‘OR’ (am I right?). So
wouldn’t it be possible to make a query valid just by adding a number of
ORs to the end? I’m not sure that there will be a backwards
compatibility issue; inclusion of an ENTRYAGGREGATOR in the same call as
a query could just be a shortcut of adding it to the end, so the calls:
LimitA
LimitB(ENTRYAGGREGATOR => OR)
would be expanded to:
LimitA
LimitB
Limit(ENTRYAGGREGATOR => OR)
which is the same thing (the same for AND).

Or have I missed something? As far as I can see adding the necessary ORs
to the end would result in exactly the same query as it does now,
regardless of the existing queries already being used (as in, complete
backwards compatiblity). But it is late, so I’ll happily accept an
example of where it isn’t the case :slight_smile:

  1. It IMO is not the most intuitive way of looking at things, forgive me
    if I admit my ignorance here, but I’ve always had somewhat of a mental
    block with RPN, it just doesn’t come naturually to me.

Fair enough; but what is the alternative? At the moment, unless there’s
something in the API I don’t understand, the Limit function is very
limited, since you can only do ANDs and ORs without any brackets.

If the interface were exactly the same as before, except that you
could do RPN as well if you wanted to (I think this is the case with my
proposal), then is that an issue?

After briefly looking through the code, there is a SUBCLAUSE option
to Limit, I’ve never used this option so I’m not entirely sure as to it’s
purpose, and initially looking at the code it is not to me entirely obvious
as to what it is doing.

looks

It looks to me like it lets you add an arbitrary chunk of SQL to go
after the WHERE which is ANDed with everything the function puts in
already. So it does let you do what you want, but reverts you to writing
SQL, which is the whole point of the thing.

But that’s just my opinion…I could be wrong.

Me, too :slight_smile:

Robie.

9

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’}";