[rt-users] Users Autocomplete not working 4.0.7?

[Chris O'Kelly]

Hi Guys,

Have you tried separating out the subqueries there?
If it were me that's what I would look at next. Here they are split up into individual queries (where aliases weren't used already I have given them generic names):

distinctQuery:
SELECT DISTINCT main.id
FROM Users main
JOIN Principals Principals_1  ON ( Principals_1.id = main.id )
WHERE (Principals_1.Disabled = '0')
AND (
        lower(main.RealName) LIKE '%taf2%'
        OR lower(main.EmailAddress) LIKE 'taf2%'
        OR lower(main.Name) LIKE 'taf2%'
        )
AND (lower(Principals_1.PrincipalType) = 'user')
AND (
        main.EmailAddress != ''
        AND main.EmailAddress IS NOT NULL
        )


limitQuery:
SELECT main.*
FROM distinctquery, Users main
WHERE (main.id = distinctquery.id)
ORDER BY main.Name ASC


thirdQuery:
SELECT limitquery.*,rownum limitrownum
FROM limitquery
WHERE rownum <= 10

finalQuery:
SELECT *
FROM thirdQuery
WHERE limitrownum >= 1;

so first run distinctQuery - This part should essentially get you the list of users who match what you are typing (in this case, users whose real name contains taf2 or whose email or username starts with taf2; who are also user's with valid email addresses; who are not disabled). If this returns no rows there are serious problems - either there is definitely no user matching that condition or there are serious Db issues. The next three queries are trivial really. limitQuery selects the id of the matched users and orders by username. thirdQuery (I'm pretty creative when it comes to naming) limits the results to 10.

but yeah, to reiterate, I would suggest just running the deepest level query (the one I have marked distinctQuery above) to see if you get results from that. If you do, heck the other queries in order to find the point of failure. Otherwise, all I can really suggest is to take a serious look at the Users table and confirm there is a user there who matches the conditions. Make absolute sure the user is not disabled, make absolute sure that taf2 appears somewhere in the Real Name field, NOT the nickname field (or add nickname to $UserAutocompleteFields in RT_SiteConfig.pm). If your user's are synced from LDAP/AD, then they may have these details in that system but the configuration has not yet been setup to sync these into LDAP.

I saw in an earlier post you were checking with plummer, your username, but in these queries it suggests we were looking for taf2. If taf2 is not yourself you may be running into a confusion I experienced when I installed RT - that is, I set up the External Auth plugin but not the LDAP Import plugin. Your users may not yet be in the RT database if they have not yet logged in if this is the case.

I know I've covered a bunch of fairly basic things here, so I am really sorry if this is all stuff you have already checked out. It can be tough to guess someone's level of expertise over the internet, so while it's likely you know a whole lot more than me, I didn't want to leave anything out that might help you.

HTH


Regards

Chris O'Kelly
Web Administrator

Minecorp Australia
P: 07 3723 1000
M: 0450 586 190


Minecorp Australia
37 Murdoch Circuit
Acacia Ridge QLD 4110
www.minecorp.com.au
Sent Via a Mobile Device.


-----Original Message-----
From: rt-users-bounces at lists.bestpractical.com [mailto:rt-users-bounces at lists.bestpractical.com] On Behalf Of Shawn Plummer
Sent: Friday, 31 August 2012 9:28 AM
To: RT Users
Subject: Re: [rt-users] Users Autocomplete not working 4.0.7?

Looks like it returns no rows. So that could be a problem!

SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 30 19:24:24 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT * FROM ( SELECT limitquery.*,rownum limitrownum FROM ( SELECT main.* FROM ( SELECT DISTINCT main.id FROM Users main JOIN Principals Principals_1  ON ( Principals_1.id = main.id )  WHERE (Principals_1.Disabled = '0') AND (lower(main.RealName) LIKE '%taf2%' OR lower(main.EmailAddress) LIKE 'taf2%' OR lower(main.Name) LIKE 'taf2%') AND (lower(Principals_1.PrincipalType) = 'user') AND (main.EmailAddress != '' AND main.EmailAddress IS NOT NULL)  ) distinctquery, Users main WHERE (main.id = distinctquery.id)  ORDER BY main.Name ASC  ) limitquery WHERE rownum <= 10 ) WHERE limitrownum >= 1;

no rows selected

SQL>


On Aug 30, 2012, at 4:49 PM, Thomas Sibley <trs at bestpractical.com> wrote:

> Please keep replies on the list for the benefit of folks searching for
> answers later.
>
> On 08/30/2012 01:16 PM, Shawn Plummer wrote:
>> My DBA tells me that this is the query she sees in the database when
>> the autocomplete fires:
>>
>>> "SELECT * FROM ( SELECT limitquery.*,rownum limitrownum FROM (
>>> SELECT main.* FROM ( SELECT DISTINCT main.id FROM Users main JOIN
>>> Principals Principals_1  ON ( Principals_1.id = main.id )  WHERE
>>> (Principals_1.Disabled = '0') AND (lower(main.RealName) LIKE
>>> '%taf2%' OR lower(main.EmailAddress) LIKE 'taf2%' OR
>>> lower(main.Name) LIKE 'taf2%') AND
>>> (lower(Principals_1.PrincipalType) = 'user') AND (main.EmailAddress
>>> != '' AND main.EmailAddress IS NOT NULL)  ) distinctquery, Users
>>> main WHERE (main.id = distinctquery.id)  ORDER BY main.Name ASC  )
>>> limitquery WHERE rownum <= 10 ) WHERE limitrownum >= 1"
>
> If you run that (awful awful) query by hand, what does it get you?