Page 1 of 1

foundset.getQuery() when in Find mode

PostPosted: Fri Sep 14, 2012 1:26 am
by sbutler
When calling foundset.getQuery() and databaseManager.getSQL(foundset) when in Find mode, I would expect to get back the query/SQL that is going to be used to generate the find requests. Intead, it returns back to me the prevoius query/sql that was used to generate the previous sceen (before the form entered find mode).
I have the need to customize the search and add in complex logic with manual SQL, however it would be nice to start with the query the Servoy builds from the find request. The only way to get that is to complete the search with foundset.search, however then I have to issue the query twice.

So, without writing my own functions to traverse the form and generate all of my own SQL, it would be nice to intersept the search and build on top of Servoy's find request.

Re: foundset.getQuery() when in Find mode

PostPosted: Fri Sep 14, 2012 6:55 pm
by ljames
It may also work to have a new function for foundset.testGetQuery (or similar) that would return the execute string you ultimately intend to execute.

Re: foundset.getQuery() when in Find mode

PostPosted: Mon Sep 17, 2012 10:47 am
by rgansevles
Scott,

Have a look at the new QueryBuilder api in 6.1
There you can build your own sql, you don't need find() at all.

Rob

Re: foundset.getQuery() when in Find mode

PostPosted: Mon Sep 17, 2012 7:11 pm
by sbutler
Hi Rob,

I know about the QueryBuilder API in 6.1, and took at look at that for this request, however it doesn't do what I'm asking for, or work as it should.

For example, when in find mode, if I do foundset.getQuery() it returns back the QBSelect object that was used to generate the previous data, before the form went into find mode. Instead, when the form is in find mode, and I do foundset.getQuery() I would expect it to return the QBSelect object that is being built by the find request (ie, what will be executed if the user does foundset.search()).

With this, the developer could intercept the search request, and using the QueryBuild API, could easily alter, or add to the find request, which is exactly what I need to do. I my specific example, a very complicated parameterized function has been built in Oracle, and I need to be able to search it (along with other find criteria). Servoy doesn't directly support that, but if I could intercept the search request, then get the QBSelect object before the search is done, I could add it to the criteria.

Currently, I am limited to only 1 of these 2 options, neither of which is very good.

Option #1: I could execute the search, then get the QBSelect object back, then modify it, and then issue the search again with the modified criteria. Problem is I'm doing the search twice here, and on a big DB, this is bad for performance

Option #2: I could intercept the search, and manually loop through the elementes and attempt to use the QueryBuilder API to generate ALL of my own SQL. This can be done, but would take some work to deal with all the related fields, etc, and it takes away from the built in productivity and power of Servoy. Why would I want to build all of this when Servoy does it all already? At that point, I've lost all the productivity of Servoy's data-binding and SQL generation.

Basically, I just need to get the query Servoy is about to issue when in Find mode, rather than it returning the previous query that was used before find mode. Seems like a bug since the form is in find mode, so getQuery shouldn't return the previous query.

Re: foundset.getQuery() when in Find mode

PostPosted: Mon Sep 17, 2012 7:56 pm
by sbutler
I've also created a case for this for those of you that would like to vote/follow this issue: https://support.servoy.com/browse/SVY-3081

Re: foundset.getQuery() when in Find mode

PostPosted: Fri Sep 28, 2012 12:07 pm
by rgansevles
Scott,

Currently, foundset.getQuery() when in find mode, just returns the query from before the find() call.
We only start analyzing the find-records data in the search() method, and that also depends on the search arguments (clearLastResults, reduceSearch).

How about if we change foundset.getQuery() to behave as foundset.search() with default args (clearLastResults=true, reduceSearch=true) without running the query and without taking the foundset out of find mode?
So return the query as defined by the search with default args?

The foundset can be taken out of find mode with a separate call foundset.loadRecords() without arguments, it will revert to before the find() call.

Rob

Re: foundset.getQuery() when in Find mode

PostPosted: Wed Oct 03, 2012 3:51 pm
by sbutler
That would be perfect!