Query Builder documentation

RT Users
1

Hi,

FYI. RT 3.2.2

I was wondering if anyone has written up documentation for using the
Query builder (couldn’t really find anything useful in the wiki).

I somewhat understand how it works, although it still seems to do
unexpected things at times, or just plan refuses to do them at other
times, and if I’m having issues then I’m sure our accounts people are
too :slight_smile:

It seems fine for doing a query full of “AND”'s or “OR”'s, but once you
start mixing them, you need to shuffle it around a lot to get the query
how you want it, and sometimes it seems impossible to get it how you
want it…

Even using the advanced builder doesn’t always do what I would expect,

An example, I would think I should be able to build this

Owner = ‘Nobody’

AND ( Status = ‘new’ or Status = ‘open’ )

AND ( Queue = ‘Bugs’

OR Queue = ‘Faults’ )

AND ( LastUpdated < ‘Today’

      OR ( Due > '1970-01-01'  AND LastUpdated > 'Yesterday' ) )

However, when I apply this, I end up with

Owner = ‘Nobody’

AND ( Status = ‘new’ or Status = ‘open’ )

AND ( Queue = ‘Bugs’ OR Queue = ‘Faults’ )

AND ( LastUpdated < ‘Today’ AND ( Due > ‘1970-01-01’ AND LastUpdated >
‘Yesterday’ ) )

Almost there, except the OR between LastUpdated and Due has been
converted into an AND, and nothing I do will let me change it back.
Perhaps its just that nesting like that is not allowed, but if that is
the case, an error would be nice rather than it happily changing the
query? Actually building this query using the “graphical” builder is a
major lesson in frustration, as bits of the query jump all over the
place while trying to group parts of the query and “AND”'s turn to "OR"
and visa versa for no apparent reason.

So, the question really is, I’m I just not understanding how to
correctly use the query builder, or is it flawed (and if so, is it fixed
in 3.4)? If it’s the former, does anyone have the documentation so I can
explain to all our staff how to successfully build complex queries?

Ps… sorry for the rant :slight_smile:

Regards

Matthew Watson

Netspace Online Systems

(03) 98110010

This email and any files transmitted with it are confidential and intended solely for the
use of the individual or entity to whom they are addressed. Please notify the sender
immediately by email if you have received this email by mistake and delete this email
from your system. Please note that any views or opinions presented in this email are solely
those of the author and do not necessarily represent those of the organisation.
Finally, the recipient should check this email and any attachments for the presence of
viruses. The organisation accepts no liability for any damage caused by any virus
transmitted by this email.

2

Matthew,

The QueryBuilder doesn’t like to mix AND and OR. I have had to get
several statements up with the QueryBuilder and then go to the
Advanced Menu to finish the statement. The Advanced menu will accept
mixed statements just fine. I would suggest saving some typical
queries as templates and then allow users to load them and plug in the
valuses they want.On Mon, 31 Jan 2005 17:46:13 +1100, Matthew Watson matthew.watson@staff.netspace.net.au wrote:

Hi,

FYI. RT 3.2.2

I was wondering if anyone has written up documentation for using the Query
builder (couldn’t really find anything useful in the wiki).

I somewhat understand how it works, although it still seems to do unexpected
things at times, or just plan refuses to do them at other times, and if I’m
having issues then I’m sure our accounts people are too J

It seems fine for doing a query full of “AND”'s or “OR”'s, but once you
start mixing them, you need to shuffle it around a lot to get the query how
you want it, and sometimes it seems impossible to get it how you want it…

Even using the advanced builder doesn’t always do what I would expect,

An example, I would think I should be able to build this

Owner = ‘Nobody’

AND ( Status = ‘new’ or Status = ‘open’ )

AND ( Queue = ‘Bugs’

OR Queue = ‘Faults’ )

AND ( LastUpdated < ‘Today’

      OR ( Due > '1970-01-01'  AND LastUpdated > 'Yesterday' ) )

However, when I apply this, I end up with

Owner = ‘Nobody’

AND ( Status = ‘new’ or Status = ‘open’ )

AND ( Queue = ‘Bugs’ OR Queue = ‘Faults’ )

AND ( LastUpdated < ‘Today’ AND ( Due > ‘1970-01-01’ AND LastUpdated >
‘Yesterday’ ) )

Almost there, except the OR between LastUpdated and Due has been converted
into an AND, and nothing I do will let me change it back. Perhaps its just
that nesting like that is not allowed, but if that is the case, an error
would be nice rather than it happily changing the query? Actually building
this query using the “graphical” builder is a major lesson in frustration,
as bits of the query jump all over the place while trying to group parts of
the query and “AND”'s turn to “OR” and visa versa for no apparent reason.

So, the question really is, I’m I just not understanding how to correctly
use the query builder, or is it flawed (and if so, is it fixed in 3.4)? If
it’s the former, does anyone have the documentation so I can explain to all
our staff how to successfully build complex queries?

Ps… sorry for the rant J

Regards

–

Matthew Watson

Netspace Online Systems

(03) 98110010

This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed.
Please notify the sender immediately by email if you have received this
email by mistake and delete this email from your system. Please note that
any views or opinions presented in this email are solely those of the author
and do not necessarily represent those of the organisation. Finally, the
recipient should check this email and any attachments for the presence of
viruses. The organisation accepts no liability for any damage caused by any
virus transmitted by this email.

The rt-users Archives

RT Administrator and Developer training is coming to your town soon!
(Boston, San Francisco, Austin, Sydney) Contact training@bestpractical.com
for details.

Be sure to check out the RT Wiki at http://wiki.bestpractical.com

3

Matthew,

The QueryBuilder doesn’t like to mix AND and OR. I have had to get
several statements up with the QueryBuilder and then go to the
Advanced Menu to finish the statement. The Advanced menu will accept
mixed statements just fine. I would suggest saving some typical
queries as templates and then allow users to load them and plug in the
valuses they want.

rt-3.4.1
While preparing queries, I have seen the problems with the query builder
not mixing AND and OR operators. Therefore attempting to follow the
above advice, I went to ‘Advanced’ and modified by hand.
I changed this:
( Queue = ‘gripe’ AND Status = ‘new’ ) AND ( Owner = ‘32’ AND Status =
‘open’ )

to this:
( Queue = ‘gripe’ AND Status = ‘new’ ) OR ( Owner = ‘32’ AND Status =
‘open’ )

Then I clicked apply. RT then returned me to the query builder page and
the query is changed back to the original.

Suggestions?

Duncan Hutty
System Administrator
Electrical and Computer Engineering
Carnegie Mellon University

4

You are correct. My solution only works to one level of AND/OR.
Apparently the Query Builder will not allow the two on the same level.

It will allow you to build a statement like:

que = ‘gripe’
AND
status = ‘new’
OR status = ‘open’

But not:
que = 'gripe
AND
status = ‘’
OR status = ‘’
OR
whatever…

The operators change when moving from from Advanced to Query Builder
and the toggle is inconsistent, sometimes changing all operators or
all on one level instead of only the line selected.

This must be a bug - and a serious one since there does not seem to be
a workaround with the advanced option. At least one that I can find.
It would be better if the advanced just took straight SQL queries and
did not reformat them for the query builder. It would be ugly but
would probably work.

I think the question now seems to be is there a way to pass a SQL
statement that will not be parsed to the db? and still be able to save
the query?On Thu, 24 Feb 2005 13:26:14 -0500, Duncan Hutty dhutty+rt@ece.cmu.edu wrote:

On 01/31/2005 07:27 AM, Stephen Hancock wrote:

Matthew,

The QueryBuilder doesn’t like to mix AND and OR. I have had to get
several statements up with the QueryBuilder and then go to the
Advanced Menu to finish the statement. The Advanced menu will accept
mixed statements just fine. I would suggest saving some typical
queries as templates and then allow users to load them and plug in the
valuses they want.

rt-3.4.1
While preparing queries, I have seen the problems with the query builder
not mixing AND and OR operators. Therefore attempting to follow the
above advice, I went to ‘Advanced’ and modified by hand.
I changed this:
( Queue = ‘gripe’ AND Status = ‘new’ ) AND ( Owner = ‘32’ AND Status =
‘open’ )

to this:
( Queue = ‘gripe’ AND Status = ‘new’ ) OR ( Owner = ‘32’ AND Status =
‘open’ )

Then I clicked apply. RT then returned me to the query builder page and
the query is changed back to the original.

Suggestions?

–
Duncan Hutty
System Administrator
Electrical and Computer Engineering
Carnegie Mellon University

The rt-users Archives

RT Administrator and Developer training is coming to your town soon! (Boston, San Francisco, Austin, Sydney) Contact training@bestpractical.com for details.

Be sure to check out the RT Wiki at http://wiki.bestpractical.com

5

You are correct. My solution only works to one level of AND/OR.
Apparently the Query Builder will not allow the two on the same level.

It will allow you to build a statement like:

que = ‘gripe’
AND
status = ‘new’
OR status = ‘open’

But not:
que = 'gripe
AND
status = ‘’
OR status = ‘’
OR
whatever…

The operators change when moving from from Advanced to Query Builder
and the toggle is inconsistent, sometimes changing all operators or
all on one level instead of only the line selected.

This must be a bug - and a serious one since there does not seem to be
a workaround with the advanced option. At least one that I can find.

add parens. “()”

It would be better if the advanced just took straight SQL queries and
did not reformat them for the query builder. It would be ugly but
would probably work.

That would also expose RT to all sorts of sql injection attacks.

6

OK - I can understand the need for parsing now for security sake but
the parenthesis () does not work for me. Additional parenthesis do
nothing in my case. Also even when the query is toggled correctly (In
Query Builder) , it appears that this is short lived. A return to
Advanced, A save, or a Search reformats the OR back to AND again and
we are back where we started.

Example:
In Advance:
Owner = ‘Nobody’ AND ( ( ( Status = ‘new’ ) OR ( Owner = ‘Dale’
AND Status != ‘stalled’ ) ) )

Click on applied and the Query Builder shows:

Owner = ‘Nobody’
AND

   Status = 'new'

AND
Owner = ‘Dale’
AND Status != ‘stalled’

Select the second AND and click on the And/Or toggle button and we
have (In Query Builder):

Owner = ‘Nobody’
AND

   Status = 'new'

OR
Owner = ‘Dale’
AND Status != ‘stalled’

Which is what we want and expect. However a return to Advanced or Save
or Search casues the OR to revert to AND again…

Adding additional () does not fix my problem. Am I doing something wrong here?On Fri, 25 Feb 2005 00:20:02 -0500, Stephen Hancock sh.hancock@gmail.com wrote:

You are correct. My solution only works to one level of AND/OR.
Apparently the Query Builder will not allow the two on the same level.

It will allow you to build a statement like:

que = ‘gripe’
AND
status = ‘new’
OR status = ‘open’

But not:
que = 'gripe
AND
status = ‘’
OR status = ‘’
OR
whatever…

The operators change when moving from from Advanced to Query Builder
and the toggle is inconsistent, sometimes changing all operators or
all on one level instead of only the line selected.

This must be a bug - and a serious one since there does not seem to be
a workaround with the advanced option. At least one that I can find.
It would be better if the advanced just took straight SQL queries and
did not reformat them for the query builder. It would be ugly but
would probably work.

I think the question now seems to be is there a way to pass a SQL
statement that will not be parsed to the db? and still be able to save
the query?

On Thu, 24 Feb 2005 13:26:14 -0500, Duncan Hutty dhutty+rt@ece.cmu.edu wrote:

On 01/31/2005 07:27 AM, Stephen Hancock wrote:

Matthew,

The QueryBuilder doesn’t like to mix AND and OR. I have had to get
several statements up with the QueryBuilder and then go to the
Advanced Menu to finish the statement. The Advanced menu will accept
mixed statements just fine. I would suggest saving some typical
queries as templates and then allow users to load them and plug in the
valuses they want.

rt-3.4.1
While preparing queries, I have seen the problems with the query builder
not mixing AND and OR operators. Therefore attempting to follow the
above advice, I went to ‘Advanced’ and modified by hand.
I changed this:
( Queue = ‘gripe’ AND Status = ‘new’ ) AND ( Owner = ‘32’ AND Status =
‘open’ )

to this:
( Queue = ‘gripe’ AND Status = ‘new’ ) OR ( Owner = ‘32’ AND Status =
‘open’ )

Then I clicked apply. RT then returned me to the query builder page and
the query is changed back to the original.

Suggestions?

–
Duncan Hutty
System Administrator
Electrical and Computer Engineering
Carnegie Mellon University

The rt-users Archives

RT Administrator and Developer training is coming to your town soon! (Boston, San Francisco, Austin, Sydney) Contact training@bestpractical.com for details.

Be sure to check out the RT Wiki at http://wiki.bestpractical.com

7

Parentheses are not enough:
( Queue = ‘gripe’ AND ( Status = ‘new’ OR Status = ‘open’ OR Status =
‘stalled’ ) AND ( Owner = ‘32’ OR Owner = ‘10’ )) OR ( Owner = ‘32’ )

when I hit apply, became this:

( Queue = ‘gripe’ AND ( Status = ‘new’ OR Status = ‘open’ OR Status =
‘stalled’ ) AND ( Owner = ‘32’ OR Owner = ‘10’ )) AND ( Owner = ‘32’ )

It appears that most simple queries can be entered into the advanced
box, but that you have to think carefully about the order of your criteria.

That’s not right. Patches would be appreciated.

8

Greetings,

I am new to RT (a month now) and this is my first post. I currently have it
in testing. I would like to say that RT is excellent.

I have attached a patch and hope it fixes this problem. I have only done
some testing with it.

Anthony Leong
Network Specialist
CNS - Network Operations
York University, Toronto, Ontario
416-736-2100 x22638

Quoting Jesse Vincent jesse@bestpractical.com:

Build.html.patch (2.75 KB)