Servoy Forum

Hi,

I use the query below to load a foundset.
When the foundset is loaded, the sort is not as requested.

Code: Select all

query = 'select ONDHCFI_ID ' +                  
        'from ondh_conf_items ' +
   'where BEDR_BEDR_ID like ? ' +
   '  and ONDHCFI_COURANT = 1 ' +
   '  and (ONDHCFI_NR like ? ' +
   '   or ONDHCFI_ID in (select ONDHCFI_ONDHCFI_ID from ONDH_CONF_SL_REGELS where ONDHCNF_ONDHCNF_ID in (select ONDHCNF_ID from ondh_configuraties where ONDHCNF_NR like ?)) ' +
   '   or ONDHCFI_ID in (select ONDHCFI_ONDHCFI_ID from ONDH_CONF_SL_REGELS where ONDHCNF_ONDHCNF_ID in (select ONDHCNF_ID from ondh_configuraties where upper(ONDHCNF_NAAM) like upper(?))) ' +
   '   or upper(ONDHCFI_NAAM) like upper(?) ' +
   '   or upper(ONDHCFI_SERIENR) like upper(?) ' +
   '   or AFLERGL_AFLERGL_ID in (select AFLERGL_ID from afleverregels where VERKOR_VERKOR_ID in (select verkor_id from verkorderregels where verkok_verkok_id in (select verkok_id from verkorderkaarten where verkok_nr like ?))) ' +
        '   or ONDHCFI_ID in (select ONDHCFI_ONDHCFI_ID from ondh_cfi_locinfos where vstg_vstg_id in (select vstg_id from vestigingen where vstg_zoeknaam like upper(?))) ' +
                    '      ) ' +
                    'order by ONDHCFI_NR asc';

if I look at the admin performance page I see that my query is wrapped in another query and the sort has gone.

Code: Select all

select ONDHCFI_ID
from ONDH_CONF_ITEMS
where ONDHCFI_ID in (

select ONDHCFI_ID from ondh_conf_items where BEDR_BEDR_ID like ? and ONDHCFI_COURANT = 1 and (ONDHCFI_NR like ? or ONDHCFI_ID in (select ONDHCFI_ONDHCFI_ID from ONDH_CONF_SL_REGELS where ONDHCNF_ONDHCNF_ID in (select ONDHCNF_ID from ondh_configuraties where ONDHCNF_NR like ?)) or ONDHCFI_ID in (select ONDHCFI_ONDHCFI_ID from ONDH_CONF_SL_REGELS where ONDHCNF_ONDHCNF_ID in (select ONDHCNF_ID from ondh_configuraties where upper(ONDHCNF_NAAM) like upper(?))) or upper(ONDHCFI_NAAM) like upper(?) or upper(ONDHCFI_SERIENR) like upper(?) or AFLERGL_AFLERGL_ID in (select AFLERGL_ID from afleverregels where VERKOR_VERKOR_ID in (select verkor_id from verkorderregels where verkok_verkok_id in (select verkok_id from verkorderkaarten where verkok_nr like ?))) or ONDHCFI_ID in (select ONDHCFI_ONDHCFI_ID from ondh_cfi_locinfos where vstg_vstg_id in (select vstg_id from vestigingen where vstg_zoeknaam like upper(?)))

)



Why is this ??

Hi Hans,

Servoy has been wrapping our SQL used in foundsets since the a while now (since 3.5 I believe). If you want to use a sort you have to set it (and defer it) using controller.sort().
so in your case you use the following code:

Code: Select all

controller.sort('ONDHCFI_NR asc', true); // defer the sort until the foundset is loaded
foundset.loadRecords(mySQL, myParams);

And I can't help but notice the use of sub-selects instead of faster joins in your SQL.
What RDMS is this? Some old version of MySQL that doesn't support joins?

Hi Robert,

Thanks for the answer.
I would still like to now why Servoy does this.

On the sub-selects : I use sub-selects al lot.
Let's not start a discussion here, but joins are not by definition faster then sub-selects.
I am using Oracle 11g, which has a cost-based optimizer.

<edit> : this cost-based optimizer will also ( almost always ) result in the same execution plan regardless of sub-query or join.

Regards,

Hi Hans,

Since we are not starting a discussion here

Hans Nieuwenhuis wrote:<edit> : this cost-based optimizer will also ( almost always ) result in the same execution plan regardless of sub-query or join.

Good for Oracle.
I guess to each their own. I would use a WITH clause and JOINs to keep the SQL easier to read and therefor be less error prone. But that is me.
Although I am not entirely sure Servoy's foundset.loadRecords() supports the WITH clause yet, I would have to check that.

Hans Nieuwenhuis wrote:Hi Robert,

Thanks for the answer.
I would still like to now why Servoy does this.

Hans,

Servoy does not parse the custom sql, we leave that to the db.
To be able to add stuff to the sql (like sorts, joins) we need something to add these to, so we wrap the custom sql in a subselect.
Unfortunately, this looses the existing sorting.

You could have a look at the query builder introduced in Servoy 6.1, it does not have this limitation.

Rob