Need help with translating query - prioritising autocomplete users

RT Developers
1

Hi Everyone,

We’ve recently migrated from RT 3.8.8 to RT 4.4.3. The jQuery autocomplete feature when typing in CCs is really nice and we don’t want to have to disable it, but as we’ve run RT 3.8 for close to 10 years, as you might imagine the users table is pretty crowded with lots of emails we rarely need to use, typos and spamers.

We’ve tried cleaning this up, but with 10s of thousands of users, this is difficult. My latest solution is to prioritise email addresses in users table that we would likely refer to more often - e.g. “more popular” customer email addresses, staff addresses and the like.

I’ve come up with a solution for this, but I’m struggling to convert the SQL query to use the DBIx Perl query builder that RT uses. I’d like to leverage the query builder so we can keep using the “Simple search” function that RT’s existing code leverages for the autocomplete.

To start with I came up with the following SQL query to sort email addresses based on how many ticket transactions are related to the address like so. This works:

SELECT u.id, u.EmailAddress, u.id, COUNT(tx.id) AS txcount FROM Users AS u
LEFT JOIN Transactions AS tx ON u.id = tx.Creator
WHERE u.EmailAddress LIKE 'ray%'
GROUP BY u.id
ORDER BY txcount DESC
LIMIT 10;

But as I say, I’m struggling to convert to use the query builder. This is my latest attempt. I’ve put this in an overlay file which is a copy of the original Autocomplete helper code: /opt/rt4/local/html/Helpers/Autocomplete/Users

(This code is actually in a standalone perl file for testing):

my $users = RT::Users->new($RT::SystemUser);

my $txtable = $users->Join(
    TYPE       => 'left',
    ALIAS1     => 'main',
    FIELD1     => 'id',
    TABLE2     => 'Transactions',
    FIELD2     => 'Creator'
);

my $txcount = $users->Column(FUNCTION => 'COUNT', TABLE => $txtable, FIELD => 'id', ALIAS => $txtable);
$users->Column(FIELD => 'EmailAddress', ALIAS => 'main');

$users->GroupByCols((
        {
        FIELD => 'id',
        ALIAS => 'main'
        }));

$users->OrderBy( FIELD => 'id', FUNCTION => 'COUNT(Transactions_2.id)', ALIAS => undef, ORDER => 'DESC');

$users->Limit(FIELD => 'EmailAddress', OPERATOR => 'LIKE', VALUE => 'ray');


print($users->BuildSelectQuery());

I can’t even figure out how to print out my COUNT() field as it doesn’t belong to the users table that the query is based on. The DBIx documentation unfortunately isn’t overly helpful as there’s not really many examples I’ve found to help explain it, and I can’t find a similar query in the RT codebase to work off either.

My plan is to call these query builder functions before the existing SimpleSearch() is called, to add my sorting criteria into the existing query. This is the existing code in the Autocomplete helper and my code will fit in here.

my $users = RT::Users->new($CurrentUser);
$users->SimpleSearch( Privileged => $privileged,
                      Return     => $return,
                      Term       => $term,
                      Max        => $max,
                      Exclude    => \@exclude,
                      # If an operator is provided, check against only
                      # the returned field using that operator
                      $op ? ( Fields => { $return => $op } ) : (),
                    );

my @suggestions;
while ( my $user = $users->Next ) {
    my $suggestion = { id => $user->id, label => $user->Format, value => $user->$return };
    $m->callback( CallbackName => "ModifySuggestion", suggestion => $suggestion, user => $user );
    push @suggestions, $suggestion;
}

Any guidance would be appreciated.

Thanks!
Rhys.