Archives
Contributors
Re: Special way of searching for products
Well if you can't use trigram, you need to suffer the performance penalty of using Btree indexes for openended text searches. You could partially mitigate by sanitizing to just lowercase and using a like search rather than ilike but probably only worth it for 25k+ products by the time you massage inputs.
But odoo.sh lets you connect via psql, so you can try. pg_trgm is bundled in contrib, same as pg_unaccent so should be there. Just whether you have rights to create extensions.
On Thu, Apr 29, 2021 at 9:07 PM Radovan Skolnik <radovan@skolnik.info> wrote:
Graeme, thanx for input. This part of your email caught my attention: > Stored computed sanitized code, then sanitize search args on way in. Trigram > index that stored field Stored computed sanitized code - no problem. I can do that and had that in mind. Sanitize search args on way in - you say search for bank accounts shows how to do this? Will check that out. Trigram index that stored field - I am on odoo.sh not sure if that is possible there. However if I am able to sanitize both stored code and input that should generally be enough. I am looking specifcally to work with internal reference (default_code) so no need to handle cases like "Red Cat" or "Cat Red" although it would nice also. Best regards Radovan Skolnik On štvrtok 29. apríla 2021 1:01:59 CEST Graeme Gellatly wrote: > I've done this for years in one way or another. OCA does have trgm module > which allows similarity search but for most use cases I find mostly the > issue is one of order of search terms. Product search is particularly > sucky because it has all sorts of overrides. This is in general my simple > approach. > 1. Install pg_trgm or else you will feel the pain. 2. Override search and > split the name argument into multiple ilikes. In my case I typically split > on spaces, which means users can search "Red Car" or "Car Red" and get same > result. I do it on spaces only. I know you want to avoid but actually for > your use case you are better just copying the way bank accounts are > sanitized and searched. Stored computed sanitized code, then sanitize > search args on way in. Trigram index that stored field On Thu, Apr 29, 2021 > at 9:42 AM Pierre Verkest < pierreverkest84@gmail.com [1] > wrote: Few > ideas based on postgresql: > * not sure if it's possible with SIMILAR TO or ~ operators > * investigate extension * fuzzystrmatch: > https://www.postgresql.org/docs/13/fuzzystrmatch.html [2] * pgtrgm: > https://www.postgresql.org/docs/current/pgtrgm.html#id-1.11.7.40.6 [3] * > create your own unaccent rules: > https://www.postgresql.org/docs/current/unaccent.html [4] regards, > Le mer. 28 avr. 2021 à 22:33, Radovan Skolnik < radovan@skolnik.info [5] > a > écrit : Hello, > I have been asked few times by users if it is possible to search for > products in a way omitting special characters (like dash or space for > exmaple) from product's default_code (or even input string). Let me give an > example: Let's say we have a product with default_code like CD-12345-XYZ In > current situation if user enteres "CD12345" or "CD 12345" nothing is > retrieved. Vice versa, if the default_code is CD12345XYZ and user enters > "CD-12345" or "CD 12345" nothing is retrieved either. So the solution would > be to first remove those special characters from the string being searched > for and then search for default_code transformed with some (SQL?) function. > Is anything like that possible? One idea comes to mind using computed field > where that stripped deault_code would be stored and extending default search > to use this. However that would require stored computed field. Any way to > prevent this? > Thank you. Best regards > Radovan Skolnik > > > _______________________________________________ > Mailing-List: https://odoo-community.org/groups/contributors-15 [6] > Post to: mailto: contributors@odoo-community.org [7] > Unsubscribe: https://odoo-community.org/groups?unsubscribe [8] > > -- > Pierre > > _______________________________________________ > Mailing-List: https://odoo-community.org/groups/contributors-15 [9] > Post to: mailto: contributors@odoo-community.org [10] > Unsubscribe: https://odoo-community.org/groups?unsubscribe [11] > > > _______________________________________________ > Mailing-List: https://odoo-community.org/groups/contributors-15 [12] > Post to: mailto:contributors@odoo-community.org > Unsubscribe: https://odoo-community.org/groups?unsubscribe [13] > > > > [1] mailto:pierreverkest84@gmail.com > [2] https://www.postgresql.org/docs/13/fuzzystrmatch.html > [3] https://www.postgresql.org/docs/current/pgtrgm.html#id-1.11.7.40.6 > [4] https://www.postgresql.org/docs/current/unaccent.html > [5] mailto:radovan@skolnik.info > [6] https://odoo-community.org/groups/contributors-15 > [7] mailto:contributors@odoo-community.org > [8] https://odoo-community.org/groups?unsubscribe > [9] https://odoo-community.org/groups/contributors-15 > [10] mailto:contributors@odoo-community.org > [11] https://odoo-community.org/groups?unsubscribe > [12] https://odoo-community.org/groups/contributors-15 > [13] https://odoo-community.org/groups?unsubscribe_______________________________________________
Mailing-List: https://odoo-community.org/groups/contributors-15
Post to: mailto:contributors@odoo-community.org
Unsubscribe: https://odoo-community.org/groups?unsubscribe
by Graeme Gellatly - 11:31 - 29 Apr 2021
Reference
-
Special way of searching for products
Hello, I have been asked few times by users if it is possible to search for products in a way omitting special characters (like dash or space for exmaple) from product's default_code (or even input string). Let me give an example: Let's say we have a product with default_code like CD-12345-XYZ In current situation if user enteres "CD12345" or "CD 12345" nothing is retrieved. Vice versa, if the default_code is CD12345XYZ and user enters "CD-12345" or "CD 12345" nothing is retrieved either. So the solution would be to first remove those special characters from the string being searched for and then search for default_code transformed with some (SQL?) function. Is anything like that possible? One idea comes to mind using computed field where that stripped deault_code would be stored and extending default search to use this. However that would require stored computed field. Any way to prevent this? Thank you. Best regards Radovan Skolnik
by Radovan Skolnik - 10:14 - 28 Apr 2021