Servoy Forum

Hello,

I would like to have the following query in one find statement:

SELECT table_id FROM mytable
WHERE description LIKE '%hello%' AND
description LIKE '%my%' AND
description LIKE '%world%'

The above query will retrieve all records having all the words 'hello', 'my' and 'world' in the description but not particulary in this order.

The above query also returns the description 'Hi world, say hello to my friend'

In 1 find statement you can do:

foundset.description = '%hello%my%world%'

but this will only retrieve those descriptions that have those words in this order.

Having 3 words, means 6 combinations and I don't want to use something like this:

foundset.find()
foundset.description = '%hello%my%world%'
foundset.newRecord()
foundset.description = '%hello%world%my%'
foundset.newRecord()
foundset.description = '%my%world%hello%'
foundset.newRecord()
foundset.description = '%my%hello%world%'
foundset.newRecord()
foundset.description = '%world%hello%my%'
foundset.newRecord()
foundset.description = '%world%my%hello%'
foundset.search()

When you want to use an OR, then in Servoy you can do:

foundset.description = 'hello||my||world'

But this results all records that have 1 word and not the 3 words.

So I tried the following:

foundset.description = 'hello&&my&&world'

But that is not working, and even if it worked, then it would not give me the right result.
Because this would generate the following query:


SELECT table_id FROM mytable
WHERE description ='hello' AND description ='my' AND description ='world'

which of course never returns a value. That is probably the reason that Servoy doesn't support && in finds, like they support ||

But the following can be useful:

foundset.description = '%hello%&&%my%&&%world%'

This should generate the query that I mentioned in the beginning of this topic.

Can this be supported in the next version of Servoy?
Or is there another way to handle this situation?

Shouldn't

Code: Select all

foundset.find()
foundset.description = "%hello%";
foundset.description = "%world%";
foundset.search()

work?