Search doesn't work on custom fields

[pavel4u@…]

We used to add a reference to another bug tracking tool by adding the other number in the Keywords field. In this case the search (the search field on the top right) worked as expected and the ticket was found. Now we added a custom field to store the reference and the search doesn't work anymore → no ticket is found.

[jwin]

this workes for me: excerpt from …/trac/ticket/api.py

...
        sql2, args2 = query_to_sql(db, query,
'summary||keywords||description||reporter||ltc||cc')
        cursor = db.cursor()
        cursor.execute("SELECT DISTINCT
a.summary,a.description,a.reporter, "
                       "a.keywords,a.id,a.time,c.value as ltc FROM ticket
a "
                       "LEFT JOIN ticket_change b ON a.id = b.ticket "
                       "LEFT OUTER JOIN ticket_custom c ON (a.id =
c.ticket AND c.name = 'ltc') "
                       "WHERE (b.field='comment' AND %s ) OR %s" % (sql,
sql2),
                       args + args2)
        for summary,desc,author,keywords,tid,date,ltc in cursor:
...

notice the occurrence of "ltc", which is my custom ticket field that should be searched for the query string

[anonymous]

But how does this help? You don't want to edit api.py every time you add a custom field?

Surely seach should search in custom fields!

(We have the same problem. I end up putting the same text in a comment as well as the custom field … in the comment so search will find it and in the custom field so that it can be included in repots :S :S :S

[anonymous]

In fact, an edit to api.py is obviously a temporary workaround since it will be lost next time you upgrade!

[anonymous]

I realised that the *fantastic* new custom query page can be used to work around this limitation completely.

While the trac seach box doesn't work for custom fields, the custom query page does :)

Just dial up the custom field you're interested in and away you go!

[anonymous]

We ran into the same problem on our instance. Unfortunately, it's a shared install with two instances running. The following code allows them both to search all custom fields for each particular instance without having to re-modify it in the future.

excerpt from …/trac/ticket/api.py

...
        sql, args = query_to_sql(db, query, 'b.newvalue')
        sql2, args2 = query_to_sql(db, query, 
'summary||keywords||description||reporter||cc')
        sql3, args3 = query_to_sql(db, query, 'c.value')
        cursor = db.cursor()
        cursor.execute("SELECT DISTINCT a.summary,a.description,a.reporter, "
                       "a.keywords,a.id,a.time FROM ticket a "
                       "LEFT JOIN ticket_change b ON a.id = b.ticket "
                       "LEFT OUTER JOIN ticket_custom c ON (a.id = c.ticket)"
                       "WHERE (b.field='comment' AND %s ) OR %s OR %s" % (sql, 
sql2, sql3),
                       args + args2 + args3)
        for summary,desc,author,keywords,tid,date,ltc in cursor:
...

This works only when you want to search across all custom fields.

[scott@…]

Patch for 0.10.3

located in: trac_install_folder/trac/ticket/api.py

        db = self.env.get_db_cnx()
        sql, args = search_to_sql(db, ['b.newvalue'], terms)
        sql2, args2 = search_to_sql(db, ['summary', 'keywords', 'description',
                                         'reporter', 'cc'], terms)
        sql3, args3 = search_to_sql(db, ['c.value'], terms)
        cursor = db.cursor()
        cursor.execute("SELECT DISTINCT a.summary,a.description,a.reporter, "
                       "a.keywords,a.id,a.time,a.status FROM ticket a "
                       "LEFT JOIN ticket_change b ON a.id = b.ticket "
                       "LEFT OUTER JOIN ticket_custom c ON (a.id = c.ticket) "
                       "WHERE (b.field='comment' AND %s ) OR %s OR %s" % (sql,sql2, sql3),
                       args + args2 + args3)

[Christian Boos]

Search in custom text fields should be possible, especially when custom fields will be possible for other resources than just the tickets.

[sorberd@…]

Is there really any reason that the simple patch posted above can not be incorporated into 0.11?

[Christian Boos]

There need to be a way to specify that a custom field can be searchable. Also, this part of the code will be subject to some big changes (FieldRefactoring, SearchRefactoring), so I don't want to endorse the above change in the meantime.

[schandler@…]

Short of a better solution, I could "get by" if the Custom Query page included filtering on the content of custom fields that are defined as type "textarea". The Add Filter drop list lists custom fields that are of type "text"; just not "textarea". Being able to select tickets in which a multiline text area called "Excellent Presidential Candidate List" contains "ron paul", for example, would be nice.

[ThurnerRupert <rupert.thurner@…>]

this should look now

...
        sql, args = search_to_sql(db, ['b.newvalue'], terms)
        sql2, args2 = search_to_sql(db, ['summary', 'keywords', 'description',
                                         'reporter', 'cc'], terms)
        sql3, args3 = search_to_sql(db, ['c.value'], terms)
        cursor = db.cursor()
        cursor.execute("SELECT DISTINCT a.summary,a.description,a.reporter, "
                       "a.keywords,a.id,a.time,a.status FROM ticket a "
                       "LEFT JOIN ticket_change b ON a.id = b.ticket "
                       "LEFT OUTER JOIN ticket_custom c ON (a.id = c.ticket) "
                       "WHERE (b.field='comment' AND %s ) OR %s OR %s" % (sql, sql2, sql3),
                       args + args2 + args3)

...

[Christian Boos]

TracDev/SubmittingPatches … patches exist for a reason, they give you the much needed information about the expected context of the change (file, line numbers).

[ThurnerRupert]

0.10.4 patch

[Christian Boos]

Thanks! Ah, 0.10.4, well, let's see how that works with 0.11-stable. Tentatively scheduling for 0.11.1, but no promise ;-)

[Christian Boos]

Same as the other patch, but for branches/0.11-stable​ (applies on a different file)

[Christian Boos]

Well, this will search on all custom fields, regardless on their types.

Can anyone see a problem with that?

[ThurnerRupert]

are you sure this patch works and should be not like this one?

[Remy Blank]

Updated patch for current 0.11-stable (and actually working :-)

[anonymous]

Patch applied in [7569].

[Remy Blank]

(anonymous above was me)