Multi LIKEs in 1 find

Discuss all feature requests you have for a new Servoy versions here. Make sure to be clear about what you want, provide an example and indicate how important the feature is for you

Multi LIKEs in 1 find

Postby martinh » Tue Sep 20, 2011 9:33 am

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?
Martin
------------------------------------------------
Servoy Developer
Version 5.2.10/5.2.13
Java version 1.6 update 31
Database SQL Server 2008 R2
martinh
 
Posts: 857
Joined: Wed May 09, 2007 5:34 pm
Location: Belgium

Re: Multi LIKEs in 1 find

Postby patrick » Tue Sep 20, 2011 10:45 am

Shouldn't

Code: Select all
foundset.find()
foundset.description = "%hello%";
foundset.description = "%world%";
foundset.search()


work?
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3703
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany


Return to Discuss Feature Requests

Who is online

Users browsing this forum: No registered users and 12 guests