What's wrong with this search builder code?

RT Users
1

All,

I’m building a somewhat complicated query to find all
tickets that are linked to a ticket, including tickets
indirectly linked. The problem seems to be that the
Limit call doesn’t make it into the SQL (see below.)

What am I doing wrong?

Thanks!

perl link.pl

SELECT DISTINCT main.* FROM Tickets main , Links Links_1, Links Links_2, Tickets Tickets_3 WHERE ((Links_1.LocalTarget = Tickets_3.id)) AND ((Links_2.LocalTarget = Links_1.LocalBase)) AND ((main.id = Links_2.LocalTarget))

CODE:

#!/usr/bin/perl

use warnings;
use strict;

use lib qw( /opt/rt3/lib );

use RT;
use RT::Tickets;
use RT::Ticket;

RT::LoadConfig();
RT::Init();

my $Tickets1 = RT::Tickets->new( $RT::SystemUser );
$Tickets1->UnLimit;
my $Links1 = $Tickets1->NewAlias(‘Links’);
my $Links2 = $Tickets1->NewAlias(‘Links’);
my $Tickets2 = $Tickets1->NewAlias(‘Tickets’);

$Tickets1->Join( ALIAS1 => ‘main’,
FIELD1 => ‘id’,
ALIAS2 => $Links2,
FIELD2 => ‘LocalTarget’ );

$Tickets1->Join( ALIAS1 => $Links2,
FIELD1 => ‘LocalTarget’,
ALIAS2 => $Links1,
FIELD2 => ‘LocalBase’ );

$Tickets1->Join( ALIAS1 => $Links1,
FIELD1 => ‘LocalTarget’,
ALIAS2 => $Tickets2,
FIELD2 => ‘id’ );

$Tickets1->Limit( ALIAS => $Tickets2,
FIELD => ‘id’,
VALUE => 3377 );

print $Tickets1->BuildSelectQuery, “\n”;

2

Thanks. I did switch to _SQLLimit and _SQLJoin and things
started working. Still haven’t figured out the significance
of one method over another. I don’t think I will be able
to get the SQL to do what I want though; find all tickets
linked to a ticket, and all tickets linked to those
tickets, etc…

Transitive closures in SQL are…hard and engine-specific Were they
easy, we’d have no CachedGroupMembers table