# query wrapped and sort is gone

**URL:** <https://forum.servoy.com/t/query-wrapped-and-sort-is-gone/17186>\
**Category:** Classic Servoy\
**Created:** [April 17, 2013, 7:26am UTC](https://forum.servoy.com/t/query-wrapped-and-sort-is-gone/17186 "2013-04-17T07:26:06Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Hans\_Nieuwenhuis](https://avatars.discourse-cdn.com/v4/letter/h/87869e/32.png) [@Hans\_Nieuwenhuis](https://forum.servoy.com/u/Hans_Nieuwenhuis)\
**Post date:** [April 17, 2013, 7:26am UTC](https://forum.servoy.com/t/query-wrapped-and-sort-is-gone/17186/1 "2013-04-17T07:26:06Z")

</div>

Hi,

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

```auto
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.

```auto
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 ??

---

<div class="post-metadata">

**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\
**Post date:** [April 17, 2013, 8:45am UTC](https://forum.servoy.com/t/query-wrapped-and-sort-is-gone/17186/2 "2013-04-17T08:45:24Z")

</div>

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:

```auto
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? ![:shock:]( "Shocked") ![;)]( "Wink")

---

<div class="post-metadata">

**Author:** ![Hans\_Nieuwenhuis](https://avatars.discourse-cdn.com/v4/letter/h/87869e/32.png) [@Hans\_Nieuwenhuis](https://forum.servoy.com/u/Hans_Nieuwenhuis)\
**Post date:** [April 17, 2013, 8:55am UTC](https://forum.servoy.com/t/query-wrapped-and-sort-is-gone/17186/3 "2013-04-17T08:55:51Z")

</div>

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.

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

Regards,

---

<div class="post-metadata">

**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\
**Post date:** [April 17, 2013, 10:05am UTC](https://forum.servoy.com/t/query-wrapped-and-sort-is-gone/17186/4 "2013-04-17T10:05:10Z")

</div>

Hi Hans,

Since we are not starting a discussion here ![;)]( "Wink")

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

Good for Oracle. ![:)]( "Smile")  
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.

---

<div class="post-metadata">

**Author:** ![rgansevles](https://avatars.discourse-cdn.com/v4/letter/r/3d9bf3/32.png) [@rgansevles](https://forum.servoy.com/u/rgansevles)\
**Post date:** [April 23, 2013, 8:58am UTC](https://forum.servoy.com/t/query-wrapped-and-sort-is-gone/17186/5 "2013-04-23T08:58:00Z")

</div>

> Hans Nieuwenhuis:  
> 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