$select in table

Doug Easterbrook doug at artsman.com
Wed Mar 22 13:14:59 UTC 2023 

hi Andy.

you are right ..   it is a good thread.      how to find things in a database.  There are so many tools, so, I’ll introduce text search as well



I understand the ‘Andy' and ‘Hilton’ example by parsing the name and doing two like searches.     we did something similar in that we created a hidden field that had concatenated:  first name || last name || company || last name || first name.  

we did that was some 20 years ago in omnis DF1 world and discovered it was great for ilike searching in postgres.      its a case of bringing old ideas into new technology.





then we found ‘postgres text search’ which is amazing for what it can do.   (it does have some limitations)

This lets us use postgres text search on every record which does a bunch of things that are useful, like making word order independent and helping with pluralization of some words and other lexemes.  this link might help explain how it works

https://www.postgresql.org/docs/current/textsearch-intro.html#TEXTSEARCH-MATCHING


what we did was:

1) make a field in each database table called XX_SEARCH. (replace XX_ with the prefix of the table)
2) created a trigger on the table that maintans XX_SEARCH with specified fields in that table (usually all the character and primary keys in the row)

3) create a function that builds the text search (included below) that to show what it might look like 

# Creates a to_tsquery compatible search string
#
# Parameters
# pText->the text we want to turn into a 'tect search'
# pConnector-the connector for text search .  either & or |

# remove :* which are the begins with search .. along with other white space chars.   replace single quote, double quote with a space
Do tStringFields.$replace(pText,'''',' ',pText)
Do tStringFields.$replace(pText,'"',' ',pText)
Do tStringFields.$removechars(pText,con(':*''',chr(9,10,12,13)),kTMRemove,pText)

# remove double spaces
Do tStringFields.$trim(pText)
While pos('  ',pText)
Do tStringFields.$replace(pText,'  ',' ',pText)
End While

# escape any other chars - eventually might allow &  or |
Do tStringFields.$replace(pText,')','\)',pText)
Do tStringFields.$replace(pText,'(','\(',pText)
Do tStringFields.$replace(pText,'&','\&',pText)
Do tStringFields.$replace(pText,'!','\!',pText)
Do tStringFields.$replace(pText,'|','\|',pText)

# notthing provided, so return empty string
If len(pText)=0
Quit method pText
End If

# add words in the text search vector
Do tStringFields.$replace(pText,' ',con(':* ',pConnector,' '),pText)

# finish the string
Calculate pText as con("'",pText,":*'")

Quit method pText



4) the general gist of the program flow is 

a) figure out what the text search should be
— SearchBox is a string like ‘Andy Hilton’ and is what the user types in to find in your database record
— and textSearch is the result of formatting it as per the function above


Calculate textSearch as tDatabase.$sqlTextSearch(iSearchBox,'&')


b) add it to the postgres where clause for text searching as per the following abbreviated example

   Begin statement
Sta: Select * from F_CLIENT where 
Sta:to_tsquery([textSearch]) @@ C_SEARCH)
End statement

note: we may also add in  iLike searches since its important to find people using any method that that may match



bottom line, its one more tool in the ‘how do you find things in a database when you want to be a bit more google-ish on the results returned.

there are even functions in text search that give a match % indication so you could have your results like

50% confidence -> this is the record for andy hilton
25% confidence -> this other record could be andy hilton 

hopefully that idea makes sense.




what we have found in using this
1) the database size increases about 20% because each record now has the XX_SEARCH field that is indexed.    Not a bad tradeoff

2) using text search is quite fast, comparable to the performance of ilike with ‘%’ at both ends of the search term.







Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 650-1978

> On Mar 21, 2023, at 3:06 PM, TBS <andyh at totallybrilliant.com> wrote:
> 
> Yes but…..:)
> 
> I really like that suggestion, which does have positive additional answers for sure - my searches however go a step further - in that, having observed why many users searches would not find what they wanted, I saw that it was often that there was an ordering issue, i.e. my name may be stored in the table as ‘Hilton, Andy’ so your examples below would not find that at all…..
> 
> So what I do is to parse the search field entry (as you do having removed oddball characters) by space (or spaces) and make the search for ‘Andy Hilton’ into : clientname ilike ‘andy’ and clientname ilike ‘hilton’ (all created generically) - and this would find my entry (indeed as would searching for ‘an dy hi’) - which I simply found worked a ton better to enable users to find things almost no matter how they had been entered, so we got way fewer ‘duplicated’ entries being created by finding them better to start with !
> 
> Just another point of interest in this thread (which has been a good one I think !)
> 
> Andy
> On Mar 20, 2023, 12:35 PM -0400, Doug Easterbrook via omnisdev-en <omnisdev-en at lists.omnis-dev.com>, wrote:
>> hi Andy..
>> 
>> you are right…. and there is always a ‘yes but’…. :)
>> 
>> 
>> me, I replace all the punctuation like ‘, $,&, (), * “ ! etc., escape codes, and spaces in a search tag anyway with a ‘%’
>> 
>> 
>> that way searching for O’Brien or OBrien will find it, regardless of the quote. (the search is ‘%O%Brien%’)
>> 
>> or searching for ‘Andy Hilton’ will find you regardless if you have a middle name. (the search is ‘%Andy%Hilton%’
>> 
>> searhcing for Bob! will find ‘bob’ since the ! is ignored.
>> 
>> that way you can use partial words for any search and strip away stuff known to hide characters.
>> 
>> 
>> bind vars are less useful if you want that kind of searching since you have to replace text and add functions like unaccent()
>> 
>> 
>> 
>> 
>> 
>> 
>> 
>> Doug Easterbrook
>> Arts Management Systems Ltd.
>> mailto:doug at artsman.com
>> http://www.artsman.com
>> Phone (403) 650-1978
>> 
>>> On Mar 20, 2023, at 8:53 AM, TBS <andyh at totallybrilliant.com> wrote:
>>> 
>>> Worth noting that using bind variables removes this need !! (Just to be clear)
>>> 
>>> Andy Hilton
>>> Totally Brilliant Software Inc
>>> Phone (US) : (863) 409 4870
>>> Phone (UK) : 0207 193 8582
>>> Web : www.totallybrilliant.com
>>> Helpdesk : https://totallybrilliantsoftware.supportsystem.com/
>>> Email : andyh at totallybrilliant.com
>>> On Mar 20, 2023, 11:35 AM -0400, Daniel Sananes <daniel.s at kopparbergs.se>, wrote:
>>>> Thanks
>>>> I put this in my repertoire as well.
>>>> 
>>>> Daniel
>>>> 
>>>> -----Ursprungligt meddelande-----
>>>> Från: omnisdev-en <omnisdev-en-bounces at lists.omnis-dev.com> För Rudolf Bargholz
>>>> Skickat: den 20 mars 2023 15:52
>>>> Till: OmnisDev List - English <omnisdev-en at lists.omnis-dev.com>
>>>> Ämne: AW: $select in table
>>>> 
>>>> Hi Daniel,
>>>> 
>>>> If you are using LIKE with a character value, e.g.
>>>> 
>>>> Sta: select * from TABLE where FIELD LIKE '[MyVar]%'
>>>> 
>>>> just remember to replace special chars appropriately, e.g.
>>>> 
>>>> Calculate MyVar as replaceall(MyVar,"'","''")
>>>> Sta: select * from TABLE where FIELD LIKE '[MyVar]%'
>>>> 
>>>> Replace all single quotes with two single quotes.
>>>> 
>>>> If you do not do this, at some stage you search for <O'Brian> and your SQL will fail. Searching for <O''Brian> (two single quotes) will work fine.
>>>> 
>>>> Regards
>>>> 
>>>> Rudolf Bargholz
>>>> 
>>>> -----Ursprüngliche Nachricht-----
>>>> Von: omnisdev-en <omnisdev-en-bounces at lists.omnis-dev.com> Im Auftrag von Daniel Sananes
>>>> Gesendet: Montag, 20. März 2023 12:17
>>>> An: OmnisDev List - English <omnisdev-en at lists.omnis-dev.com>
>>>> Betreff: Sv: $select in table
>>>> 
>>>> Hi Mike
>>>> 
>>>> I am now using ILIKE.
>>>> Works like a brilliant diamond.
>>>> 
>>>> Daniel
>>>> 
>>>> -----Ursprungligt meddelande-----
>>>> Från: omnisdev-en <omnisdev-en-bounces at lists.omnis-dev.com> För Mike Rowan
>>>> Skickat: den 19 mars 2023 01:57
>>>> Till: OmnisDev List - English <omnisdev-en at lists.omnis-dev.com>
>>>> Ämne: Re: $select in table
>>>> 
>>>> Hi Daniel
>>>> Also it can sometimes be useful to use ILIKE rather than LIKE to make the match ignore the case.
>>>> 
>>>> Mike
>>>> 
>>>> On Sun, Mar 19, 2023 at 10:47 AM Scotte Meredith <spomacguy at gmail.com>
>>>> wrote:
>>>> 
>>>>> Look for “bind variables” in the docs to see how they work. A little
>>>>> tough to explain simply but very powerful.
>>>>> 
>>>>> Sent from my iPad
>>>>> 
>>>>>> On Mar 18, 2023, at 3:14 PM, Daniel Sananes
>>>>>> <daniel.s at kopparbergs.se>
>>>>> wrote:
>>>>>> 
>>>>>> Thanks Scotte
>>>>>> 
>>>>>> This works: where client_name like '%Amaz%'.
>>>>>> But the @ is confusing and does not work: where client_name like
>>>>> '@%Amaz%'.
>>>>>> 
>>>>>> Daniel
>>>>>> 
>>>>>> -----Ursprungligt meddelande-----
>>>>>> Från: omnisdev-en <omnisdev-en-bounces at lists.omnis-dev.com> För
>>>>>> Scotte
>>>>> Meredith
>>>>>> Skickat: den 18 mars 2023 20:18
>>>>>> Till: OmnisDev List - English <omnisdev-en at lists.omnis-dev.com>
>>>>>> Ämne: Re: $select in table
>>>>>> 
>>>>>> Always start with how the full SQL would look.
>>>>>> In your case:
>>>>>> SELECT * FROM client WHERE client_name LIKE '%somethinghere%'
>>>>>> The $select is the WHERE clause
>>>>>> So put this first:
>>>>>> Calculate clientname = con('%',clientname,'%')
>>>>>> Then:
>>>>>> where client_name like @[clientname]
>>>>>> 
>>>>>> Using the @ sign makes it a bind variable so the SQL DAM will put in
>>>>>> the
>>>>> quotes or whatever is needed for the particular SQL field type. Also
>>>>> can make it more efficient and other things, but that's another story.
>>>>>> Also remember that the $select just sets the data set to use. Then
>>>>>> you
>>>>> need to $fetch to get 1 at a time, or all at once or 100 at once or
>>>>> whatever you decide.
>>>>>> 
>>>>>> Scotte Meredith
>>>>>> spomacguy at gmail.com
>>>>>> 509/998-0991
>>>>>> 
>>>>>>> On Mar 18, 2023, at 11:52 AM, Daniel Sananes
>>>>>>> <daniel.s at kopparbergs.se>
>>>>> wrote:
>>>>>>> 
>>>>>>> Hi all
>>>>>>> I am having problems understanding the $select.
>>>>>>> As I am using tables now I would like to override the table $select.
>>>>>>> I am constructing an order-window now and want to let user enter a
>>>>>>> part
>>>>> of a clients name to find the client.
>>>>>>> Begin text block
>>>>>>> Text: where client_name LIKE [clientname] End text block Get text
>>>>>>> block lQuery Do iclientrow.$select(lquery)
>>>>>>> 
>>>>>>> The only thing that works is if I explicitly send "where client_id=1".
>>>>>>> 
>>>>>>> Are there a kind soul out there who could give me an example how an
>>>>> overridden $select would look?
>>>>>>> As I don't know how the inbuilt $select looks I feel lost somehow.
>>>>>>> 
>>>>>>> I guess there are joins and other useful stuff you could have in a
>>>>>>> $select-method
>>>>>> _____________________________________________________________
>>>>>> Manage your list subscriptions at https://lists.omnis-dev.com Start
>>>>>> a
>>>>> new message -> mailto:omnisdev-en at lists.omnis-dev.com
>>>>>> VARNING: Det här e-postmeddelandet kommer från en extern avsändare.
>>>>> KLICKA INTE på länkar eller bilagor om du inte känner igen avsändaren
>>>>> och vet att innehållet är säkert.
>>>>>> _____________________________________________________________
>>>>>> Manage your list subscriptions at https://lists.omnis-dev.com Start
>>>>>> a new message -> mailto:omnisdev-en at lists.omnis-dev.com
>>>>> _____________________________________________________________
>>>>> Manage your list subscriptions at https://lists.omnis-dev.com Start a
>>>>> new message -> mailto:omnisdev-en at lists.omnis-dev.com
>>>>> 
>>>> _____________________________________________________________
>>>> Manage your list subscriptions at https://lists.omnis-dev.com Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
>>>> VARNING: Det här e-postmeddelandet kommer från en extern avsändare. KLICKA INTE på länkar eller bilagor om du inte känner igen avsändaren och vet att innehållet är säkert.
>>>> _____________________________________________________________
>>>> Manage your list subscriptions at https://lists.omnis-dev.com Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
>>>> _____________________________________________________________
>>>> Manage your list subscriptions at https://lists.omnis-dev.com Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
>>>> VARNING: Det här e-postmeddelandet kommer från en extern avsändare. KLICKA INTE på länkar eller bilagor om du inte känner igen avsändaren och vet att innehållet är säkert.
>>>> _____________________________________________________________
>>>> Manage your list subscriptions at https://lists.omnis-dev.com
>>>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
>>> _____________________________________________________________
>>> Manage your list subscriptions at https://lists.omnis-dev.com
>>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
>> 
>> _____________________________________________________________
>> Manage your list subscriptions at https://lists.omnis-dev.com
>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com

More information about the omnisdev-en mailing list