Filter expression in sao and the executed sql statement

hi

using tryton 5.4.3 i’m struggeling a bit with the filtering in the webclient (sao).

lets say, i’m in the view “product variants” and want to look up all variants containing the words “ford” and “302” (not necessarily in that order) in their “code”-field

so i type “ford 302” (without the quotes!) into the filter-inputbox and hit enter

the request url looks like

.../model/product.product;name="Variants"&search_value=[["rec_name","ilike","%ford%"],["rec_name","ilike","%302%"]]

i would now expect the execution of a sql statement that looks something like this:

SELECT * FROM product_product WHERE code ilike '%ford%' AND code ilike '%302%';

but what was actually executed is a statement like the following:

SELECT * FROM product_product WHERE code ilike 'ford%' AND code ilike '302%';

a condition that (as far as i understand regular expressions in postgresql) is never met.

what get i wrong here?

thanx


SELECT
	"a"."id" AS "id",
	"a"."active" AS "active",
	"a"."code" AS "code",
	"a"."create_date" AS "create_date",
	"a"."create_uid" AS "create_uid",
	"a"."mig_ag_nummer" AS "mig_ag_nummer",
	"a"."mig_ar_nummer" AS "mig_ar_nummer",
	"a"."mig_wg_nummer" AS "mig_wg_nummer",
	"a"."template" AS "template",
	"a"."write_date" AS "write_date",
	"a"."write_uid" AS "write_uid",
	CAST(EXTRACT('EPOCH'
FROM
	COALESCE("a"."write_date",	"a"."create_date")) AS VARCHAR) AS "_timestamp"
FROM
	"product_product" AS "a"
LEFT JOIN "product_template" AS "b" ON
	("b"."id" = "a"."template")
WHERE
	((("a"."code" ILIKE 'ford%')
	OR ("a"."id" IN (
	SELECT
		"c"."product"
	FROM
		"product_identifier" AS "c"
	WHERE
		(("c"."code" ILIKE 'ford%'))))
	OR (("b"."id" IN (
	SELECT
		"d"."id"
	FROM
		"product_template" AS "d"
	LEFT JOIN "ir_translation" AS "e" ON
		((((("e"."res_id" = "d"."id")
		AND ("e"."name" = 'product.template,name'))
		AND ("e"."lang" = 'en'))
		AND ("e"."type" = 'model'))
		AND ("e"."fuzzy" = FALSE))
	WHERE
		(COALESCE(NULLIF(COALESCE(NULLIF(NULL,
		''),
		"e"."value"),
		''),
		"d"."name") ILIKE '%ford%')))
	AND ("b"."active" IN (TRUE,
	FALSE))))
	AND (("a"."code" ILIKE '302%')
	OR ("a"."id" IN (
	SELECT
		"f"."product"
	FROM
		"product_identifier" AS "f"
	WHERE
		(("f"."code" ILIKE '302%'))))
	OR (("b"."id" IN (
	SELECT
		"g"."id"
	FROM
		"product_template" AS "g"
	LEFT JOIN "ir_translation" AS "h" ON
		((((("h"."res_id" = "g"."id")
		AND ("h"."name" = 'product.template,name'))
		AND ("h"."lang" = 'en'))
		AND ("h"."type" = 'model'))
		AND ("h"."fuzzy" = FALSE))
	WHERE
		(COALESCE(NULLIF(COALESCE(NULLIF(NULL,
		''),
		"h"."value"),
		''),
		"g"."name") ILIKE '%302%')))
	AND ("b"."active" IN (TRUE,
	FALSE))))
	AND ("a"."active" = TRUE))
ORDER BY
	"a"."id" ASC
LIMIT 1000

Maybe linked to this changeset? modules/product: 96aadf2b8e51

i deactivated the lines

if clause[1].endswith('like'):
    code_value = lstrip_wildcard(clause[2])

introduced by the mentioned changeset and the behavior now seems to be correct.

maybe the wildcard should only be stripped when searching for codes and numbers with a “simple” (no boolean operator) expression?!

Best is to ask issue author :slight_smile:
@pokoli ?

1 Like

The wildcard is only striped for when all of this conditions are meet:

  • Search on record name
  • Use a like operator (which is set by default by clients when searcihng on rec_name)
  • When the value is not escaped.

So I will suggest to write the following search critearia:

Code: %ford% or Code: %302%

Which will translate to the following domain:

['‘OR’, (‘code’, ‘like’, ‘%ford%’), (‘code’, ‘ilike’, ‘%302%’)]

This should return the expected values.

ok - thanks pokoli

but as i need both tokens (“ford” and “302”) to be found in the “code”-phrase an “and” will do the trick

Code: %ford% Code: %302%

maybe i got the concept of the “code”-field totally wrong and you can “push” me in the right direction!?

in my use-case, the codes of the product-variants consists of several alphanumeric tokens:

variant a: code => “ford 68 302”
variant b: code => “ford 65 289”
variant c: code => “chevy 88 350”

the user should be able to find products by filtering with any combination of tokens used in the variant-codes (as an example “ford” and “302”).

ideally he just has to type “ford 302” (without the qoutes) and should see “variant a” in the result.

but because the left wildcard will be stripped, any search with more than one token has an empty result-set.

so if i don’t want that the user has to type

Code: %ford% Code: %302%

instead of just

ford 302

what is the best-practice?

  • introduce an extra field, where the starting wildcard will not be removed
  • overwrite “search_rec_name” an “re-add” the stripped wildcard

i’m new to tryton and really grateful for your suggestions!

If you want to simplify things for your specific case I think the best solution is to override the rec_name searcher to adapt it for your needs. Tryton is designed to allow this practiques and ease the behaviour for implenentation specific cases.

Just to give concept, we added the feature to strip wildcard to have a more acurate results when searching on codes: Normally when the user enters a code is because it knows all the digits.

Furthermore, when using the search on a Many2One (for example product of sale lines) the client will autocomplete while the user is typing so the user can stop tiping digits when the proper result is shown.

HIH

1 Like

A post was split to a new topic: Error when accepting a solution

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.