Joop wrote:
OK. RT-3.6.3 has the possibility to log sql statements. Please turn this
on and try to find the query which is not producing the correct results.
We might be talking about different things.
I'll do the same in the clients RT with patch and without, hoping they
don't mind the slight interruption.
The problem that we were facing is that you start with entering CF's and
ordering is fine until either you delete some CF's or start moving them
around and then suddenly the ordering isn't correct any more, the reason
is that Oracle gets its rows, probably, in entered order until you
disturb it by deleted records. This makes it hard to reproduce in a test
environment by entering CF's and looking whether they come out in order
or not.
The patch that I send to Ruslan is meant to fix the ordering on, for
example, the customfields which are displayed when looking at a ticket.
In my case we discovered this problem because we use AssetTracker alot
with lots of customfields and suddenly they started being displayed in a
different order. I'm talking about this page:
http://localhost/rt3/AssetTracker/Asset/ModifyFields.html?id=163 and the
query from this is:
SELECT main.*
FROM (SELECT main.ID
FROM customfields main, objectcustomfields objectcustomfields_1
WHERE ( (objectcustomfields_1.objectid = '2')
OR (objectcustomfields_1.objectid = '0')
)
AND ((main.disabled = '0'))
AND ((main.lookuptype =
'RTx::AssetTracker::Type-RTx::AssetTracker::Asset'
)
)
AND ((main.ID = objectcustomfields_1.customfield))
ORDER BY objectcustomfields_1.objectid ASC,
objectcustomfields_1.sortorder ASC) distinctquery,
customfields main
WHERE (main.ID = distinctquery.ID)
And when using the original Oracle.pm its:
SELECT main.*
FROM (SELECT main.ID
FROM customfields main, objectcustomfields objectcustomfields_1
WHERE ( (objectcustomfields_1.objectid = '2')
OR (objectcustomfields_1.objectid = '0')
)
AND ((main.disabled = '0'))
AND ((main.lookuptype =
'RTx::AssetTracker::Type-RTx::AssetTracker::Asset'
)
)
AND ((main.ID = objectcustomfields_1.customfield))
GROUP BY main.ID
ORDER BY MIN (objectcustomfields_1.objectid) ASC,
MIN (objectcustomfields_1.sortorder) ASC) distinctquery,
customfields main
WHERE (main.ID = distinctquery.ID)
Notice the extra GROUP BY main.ID and ORDER BY MIN(..) statements.
When I perform a search like this, ticket SQL:
Queue = 'Purmerend' AND 'CF.{Soort Hulp}' LIKE 'Eerste lijn'
I get an Oracle SQL statement about the same as you but not quite so I'm
not sure this is the correct search that I'm doing.
This is what I get:
SELECT *
FROM (SELECT limitquery.*, ROWNUM limitrownum
FROM (SELECT main.*
FROM (SELECT DISTINCT main.ID
FROM (((tickets main LEFT JOIN
objectcustomfields objectcustomfields_1
ON
((objectcustomfields_1.objectid =
'0'
)
)
OR
(objectcustomfields_1.objectid =
main.queue
))
LEFT JOIN
customfields customfields_2
ON (customfields_2.ID =
objectcustomfields_1.customfield
))
LEFT JOIN
objectcustomfieldvalues
objectcustomfieldvalues_3
ON
((objectcustomfieldvalues_3.objectid =
main.ID
)
)
AND
(objectcustomfieldvalues_3.customfield =
customfields_2.ID
)
AND
((objectcustomfieldvalues_3.disabled =
'0'
)
)
AND
((objectcustomfieldvalues_3.objecttype =
'RT::Ticket'
)
))
WHERE ((customfields_2.NAME =
'Soort Hulp'
)
)
AND ((main.effectiveid = main.ID))
AND ((main.status != 'deleted'))
AND ((main.TYPE = 'ticket'))
AND ( (main.queue = '22')
AND
((objectcustomfieldvalues_3.content LIKE
'Eerste lijn'
)
)
)) distinctquery,
tickets main
WHERE (main.ID = distinctquery.ID)
ORDER BY main.ID DESC) limitquery
WHERE ROWNUM <= 50)
WHERE limitrownum >= 1
And this is your query:
SELECT *
FROM (SELECT limitquery.*, ROWNUM limitrownum
FROM (SELECT main.*
FROM (SELECT main.ID
FROM ((((tickets main LEFT JOIN
objectcustomfields objectcustomfields_1
ON ((objectcustomfields_1.objectid = '0'))
OR (objectcustomfields_1.objectid =
main.queue
))
LEFT JOIN
customfields customfields_2
ON (customfields_2.ID =
objectcustomfields_1.customfield
))
LEFT JOIN
objectcustomfieldvalues
objectcustomfieldvalues_3
ON ((objectcustomfieldvalues_3.objectid =
main.ID
)
)
AND (objectcustomfieldvalues_3.customfield =
customfields_2.ID
)
AND ((objectcustomfieldvalues_3.disabled
= '0'
)
)
AND ((objectcustomfieldvalues_3.objecttype =
'RT::Ticket'
)
))
LEFT JOIN
customfieldvalues customfieldvalues_4
ON ((customfieldvalues_4.NAME =
objectcustomfieldvalues_3.content
)
)
AND (customfieldvalues_4.customfield =
objectcustomfieldvalues_3.customfield
))
WHERE ((main.effectiveid = main.ID))
AND ((main.status != 'deleted'))
AND ((main.TYPE = 'ticket'))
AND (((main.owner = '86')))
GROUP BY main.ID
ORDER BY MIN (customfieldvalues_4.sortorder) ASC,
MIN
(objectcustomfieldvalues_3.content) ASC) distinctquery,
tickets main
WHERE (main.ID = distinctquery.ID)) limitquery
WHERE ROWNUM <= 50)
WHERE limitrownum >= 1;
Could you post the TicketSQL for your search?
You can get this from the QueryBuilder page and than 'Advanced'
Thanks in advance,
Joop
_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
