Strange behaviour when searching for words in Text field

Support Developer
1

First, I know that having Text fields in a list view is expensive on resources. But if the users want it, I wouldn’t complain because they have to work with the system.
The field we are talking about is defined as Text field on sale.line:

description = fields.Text('Description')

In this case the user wants to search for a very specific rare spare part. This part is not added as a regular product in Tryton, because it’s very rare so a general product is used for this. When adding to a sale, the description on the sale line is modified as well as the price etc.
When using the GTK client (5.0.11) the user opens a list with sale lines and opens the search / filter window. Then in the description field he adds two words. After clicking on Find, in the search bar the two words appear surrounded by quotes:

Description: "accu DDF"

Tryton does not find any record. However removing the quotes:

Description: accu DDF

Tryton finds all the records with the words “accu” and “DDF” in the description. It doesn’t matter where these words are in the description. This is the expected behavior.

Also, the latter does not work for Char fields. So what should be the default behavior? IMO when you want to search in Char or Text fields:

  1. Just enter some words for a field and Tryton will search for records where those words appear in
  2. Enter some wildcards to search for portions of some words

What can we do about this?

2

This request to find the exact string “accu DDF” inside the context of Description

This request to find “accu” in Description and “DDF” in record name.

You can use ‘%’ as wildcard with the ilike operator see: Client Usage — Tryton desktop client

3

I don’t see a “name” on the sale.line and that’s the table I’m searching. The word “DDF” only appears in the description field on the record. It also doesn’t appear anywhere in the linked records.

4

The record name is a default field that exists on every ModelStorage.

For the sale line, the search_rec_name method is defined to search on the sale record name and the product record name. So one of them probably contains the “DDF” string.

5

Hmm, you’re right … indeed the “DDF” string in the end was related to a product. So, I tried using the % between the two words and searching for two words which are certainly not appeared anywhere else and indeed it worked.
But you have to take into account that the words have to appear in that particular order. Is it possible to modify the SQL-query in a way that it doesn’t matter where the words appear in the text?
So now I search for

Description: %DDF%accu%

Gives other results then searching for

Description: %accu%DDF%
6

You must use an OR-ed expression like:

Description: DDF or Description: accu

7

I don’t know if it’s possible, but what if the user enters the words in the search widget and the client converts that to

Description: DDF or Description: accu

I think that this is something everybody should agree on.

8

Then how to search for the pattern “DFF accu”?
Why would a space be managed differently than any other character?

9

I have to put more thought into this. My point is that the user only wants to enter the search words in the search widget and don’t want to think about and, or, fieldnames etc.

10

But there is a big difference between ‘and’ and ‘or’. This must be set explicitly.

11

You are right. But I’m thinking about a method to let users only enter the words with a minimal extra and let the client do the rest.
So when I open the search widget and want to search for some words in the Description I enter:

  1. accu DDF → yields to Description: “accu DDF” (normal usage)
  2. accu&DDF → yields to Description: accu AND Description: DDF
  3. accu|DDF → yields to Description: accu OR Description: DDF

The & sign gives you the AND, the |-sign gives you the OR.