Edgewall Software
Modify

Opened 8 years ago

Last modified 3 years ago

#10152 new defect

Custom Query - "and" becomes "or"

Reported by: bastik <bastikln@…> Owned by:
Priority: normal Milestone: next-major-releases
Component: query system Version: 0.12.2
Severity: normal Keywords: query
Cc: erik.m.bray@…, Jun Omae, th@… Branch:
Release Notes:
API Changes:

Description

In custom query when you have a field

Description contains ...

click "and" > "Description" (combobox to the left) it changes to

Description contains ...
                  or ...

It is not intuitive if you have the choice between "and" and "or" and decide for "and", you get just another variant of "or".

Expected:

Description contains ...                 (and)
Description contains/doesn't contain ...

Attachments (0)

Change History (23)

comment:1 by Remy Blank, 8 years ago

The "and" makes sense when you select a field that isn't already in the query. When it is, it can be considered "strange".

What do you suggest?

comment:2 by Christian Boos, 8 years ago

Keywords: query added
Milestone: next-major-0.1X

Yes, I also often find that suboptimal. It's always been like that, but now that we also have an explicit "Or" button for adding an alternative query, it's even more strange.

I'm not sure repeating the field would be an improvement. Instead, I think we should have a (+) button after the value for adding the alternative match:

(-) Description contains ... (+)
(-) Description contains ...
(-)                   or ... (+)

The "And" field will be used only to add a new field in the current query alternative.

[OT] regarding #7145, I'll try to review the latest patch this evening ;-)

comment:3 by Christian Boos, 8 years ago

Component: search systemquery system

comment:4 by bastik <bastikln@…>, 8 years ago

So how do you enter multiple search terms, then? Putting them all in one text field in Google style does not seem to work. I often have the situation that I remember 2 or 3 keywords from the topic, but not a single phrase that would give a direct hit.

(-) Description contains ...
(-)                   or ... (+)

What if the "or" in this case would be a combobox that can be changed to "and"?

in reply to:  4 comment:5 by Christian Boos, 8 years ago

Replying to bastik <bastikln@…>:

So how do you enter multiple search terms, then? Putting them all in one text field in Google style does not seem to work

It works like that for the keywords field (#4775), so we might do the same for other text like fields, and use explicit quotes (e.g. "word sequence") for searching full sentences.

comment:6 by ebray, 8 years ago

Cc: erik.m.bray@… added

Hey, it's been a while since I've commented on a Trac issue…

Just had one of my coworkers bring up this same issue and was a bit dismayed by it. It really is a silly limitation. I like cboos's idea of adding the (+) button to existing fields in a clause.

There are two rationales for the current behavior:

  • It's closer to what the old behavior was before the multi-clause query system was added.
  • It enables use cases like "Type=defect AND (Keyword=ticket OR Keyword=query)" without having to make multiple "Or" clauses each having to contain "Type=defect")

But the current system makes it impossible to do something like "Description contains 'foo' AND Description contains 'bar".

It might(?) be an easy fix to change those 'or's to dropdowns containing 'and' and 'or'. Ands and Ors would just be evaluated in the order given, like in Python, and it would be up to the user to make sure their logic makes sense.

comment:7 by Christian Boos, 8 years ago

#10617 was closed as duplicate (/"there is no possibility to search for two words in ticket description").

comment:8 by tod <tcourtney@…>, 6 years ago

we have been using trac for 5+ years now. I like almost every aspect of it except the ability to search the bugs. I waste lot of time trying to find tickets because I can not search for descriptions that contain two separate strings ("A" and "B"). The lack of this search capability is extremely limiting. I hope someone will consider fixing it soon.

comment:9 by luis.robledano@…, 6 years ago

I think is related: I want to do: [[TicketQuery(version~=Meeting+2013)]]

So that it takes all the tickets which have in version "Meeting" and "2013". [[TicketQuery(version~=Meeting,version~=2013)]] Gives the "OR", which is not what I want.

comment:10 by Jun Omae, 5 years ago

Cc: Jun Omae added

comment:11 by th@…, 5 years ago

Cc: th@… added

comment:12 by anonymous, 4 years ago

This is very strange. Open since four years. :-(

Please fix it.

comment:13 by ebray, 4 years ago

Trac is open source software developed by volunteers. You can fix it, anonymous.

comment:14 by figaro, 4 years ago

The search functionality in Trac core is under review at present. In the meantime, you should be able to find a search plugin on HackIndex that will work around this bug.

comment:15 by theo.nolte@…, 4 years ago

Here is a patch for 1.0.1 which implements AND-Queries. Whitespace is interpreted as AND, so a search for "word1 word2" is translated into "contains word1 AND contains word2"

--- Trac-1.0.1-orig/trac/ticket/query.py	2016-03-11 17:28:10.019412729 +0100
+++ Trac-1.0.1/trac/ticket/query.py	2016-03-11 18:03:57.183385739 +0100
@@ -538,16 +538,24 @@
 
             if not value:
                 return None
-            value = db.like_escape(value)
-            if mode == '~':
-                value = '%' + value + '%'
-            elif mode == '^':
-                value = value + '%'
-            elif mode == '$':
-                value = '%' + value
-            return ("COALESCE(%s,'') %s%s" % (col, 'NOT ' if neg else '',
-                                              db.like()),
-                    (value, ))
+
+            sqlConds = []
+            sqlArgs = []
+            values = value.split()
+            for value in values:
+                value = db.like_escape(value)
+                if mode == '~':
+                    value = '%' + value + '%'
+                elif mode == '^':
+                    value = value + '%'
+                elif mode == '$':
+                    value = '%' + value
+                sqlCond = "COALESCE(%s,'') %s%s" % (col, 'NOT ' if neg else '',
+                                                  db.like())
+                sqlConds.append(sqlCond)
+                sqlArgs.append(value)
+            totCond = '(' + (' AND '.join(sqlConds)) + ')'
+            return (totCond, sqlArgs)
 
         def get_clause_sql(constraints):
             db = self.env.get_read_db()

in reply to:  15 ; comment:16 by anonymous2, 3 years ago

Replying to theo.nolte@…:

Here is a patch for 1.0.1 which implements AND-Queries. Whitespace is interpreted as AND, so a search for "word1 word2" is translated into "contains word1 AND contains word2"

patch looks good to me.

Thanks! It was very helpful.

I Hope "contains word1 AND contains word2 AND doesn't contain word3 ".

in reply to:  16 ; comment:17 by Christian Boos, 3 years ago

Replying to anonymous2:

Replying to theo.nolte@…:

Here is a patch for 1.0.1 which implements AND-Queries. Whitespace is interpreted as AND, so a search for "word1 word2" is translated into "contains word1 AND contains word2"

patch looks good to me.

See however PatchWelcome.

I Hope "contains word1 AND contains word2 AND doesn't contain word3 ".

Keywords: [word1 word2 -word3 "word4 word5"]

⇒ a matching field must contain word1 and word2 but not word3 and also word4 word5.

Also, if we have that convention for negative matches (which we already use for the author filter in the timeline), we should also use it in the regular search view.

in reply to:  17 comment:18 by anonymous2, 3 years ago

Replying to Christian Boos:

Keywords: [word1 word2 -word3 "word4 word5"]

Thanks! I didn't know that a negative match can be used.

If possible,I'd like to use that for a Custom field and Description.

But the occasion with a lot of words of the negative match would be a problem.
I want to see only the "word".
e.g.

Keywords: [word -ms-word -wordpad -wordpress -wordviewer]

in reply to:  17 ; comment:19 by Ryan J Ollos, 3 years ago

Replying to Christian Boos:

I Hope "contains word1 AND contains word2 AND doesn't contain word3 ".

Keywords: [word1 word2 -word3 "word4 word5"]

⇒ a matching field must contain word1 and word2 but not word3 and also word4 word5.

Also, if we have that convention for negative matches (which we already use for the author filter in the timeline), we should also use it in the regular search view.

Documented in TracQuery@50.

in reply to:  19 ; comment:20 by Christian Boos, 3 years ago

Replying to Ryan J Ollos:

Replying to Christian Boos:

I Hope "contains word1 AND contains word2 AND doesn't contain word3 ".

Keywords: [word1 word2 -word3 "word4 word5"]

⇒ a matching field must contain word1 and word2 but not word3 and also word4 word5.

Also, if we have that convention for negative matches (which we already use for the author filter in the timeline), we should also use it in the regular search view.

Documented in TracQuery@50.

Sorry, I wasn't that clear in my wording, but I wanted to suggest that it should work that way for the "word4 word5" part. We don't do this yet.

We would need a regexp split here.

>>> value = 'word1 word2 -word3 "word4 word5"'
>>> [w.strip() for wl in ([x[1:-1]] if x and x[0] == x[-1] == '"' else x.split() for x in re.split('("[^"]+")', value)) for w in wl]
['word1', 'word2', '-word3', 'word4 word5']

in reply to:  20 comment:21 by Ryan J Ollos, 3 years ago

Replying to Christian Boos:

Sorry, I wasn't that clear in my wording, but I wanted to suggest that it should work that way for the "word4 word5" part. We don't do this yet.

I guess that I must have also misled myself when testing that feature. If it's easy enough to implement, maybe we should just take it as a sign to make the change ;)

comment:22 by Ryan J Ollos, 3 years ago

I'll add a test for log:rjollos.git:t10152_query_phrase_match before committing. Does anyone care about allowing " to be included in the phrase by escaping the character? For example: "word4 \" word5". It seems unlikely to be needed, at least for a field like Keywords.

comment:23 by Ryan J Ollos, 3 years ago

comment:22 changes committed to 1.2-stable in r15622, merged to trunk in r15623. Edited TracQuery@53.

Modify Ticket

Change Properties
Set your email in Preferences
Action
as new The ticket will remain with no owner.
The ticket will be disowned. Next status will be 'new'.
as The resolution will be set. Next status will be 'closed'.
The owner will be changed from (none) to anonymous. Next status will be 'assigned'.

Add Comment


E-mail address and name can be saved in the Preferences .
 
Note: See TracTickets for help on using tickets.