getSites doesn't work when searching sites with params

GENERAL

TESTING

GENERAL

TESTING

Description

In getSitesFields method the parameters should be before the userId field.

For example, we want looking for sites with sitetype type and parameter: param1 with the value1 value. Doing this call:

HashMap<String,String> searchprop = new HashMap();
searchprop.put("param1", "value1");
List openSites = SiteService.getSites(org.sakaiproject.site.api.SiteService.SelectionType.JOINABLE,
"sitetype", null, searchprop, org.sakaiproject.site.api.SiteService.SortType.TITLE_ASC, null);

getSitesFields returns the values:

[sitetype, param1, %value1%, userId]

And the select is:

select SAKAI_SITE.SITE_ID from SAKAI_SITE where SAKAI_SITE.IS_USER = '0' and SAKAI_SITE.IS_SPECIAL = '0' and SAKAI_SITE.PUBLISHED = 1
and SAKAI_SITE.TYPE = ?
and SAKAI_SITE.IS_SOFTLY_DELETED = '0' and SAKAI_SITE.JOINABLE = '1'
and SITE_ID not in (select SITE_ID from SAKAI_SITE_USER where USER_ID = ? and PERMISSION <= PUBLISHED)
and SAKAI_SITE.SITE_ID in (select SITE_ID from SAKAI_SITE_PROPERTY where NAME = ? and UPPER(VALUE) like UPPER( ? ))
order by SAKAI_SITE.TITLE ASC;

clearly, the correct values returned by getSitesFields must be:

[sitetype, userId, param1, %value1%]

Attachments

Linked issues

is related to

SAK-38999

User Membership search not working for joinable sites

Activity

Show:

Alexandre Ballesté Crevillén November 26, 2014 at 1:21 AM

Tested on local trunk. Rev 315688. Worked.

Hudson CI Server November 23, 2014 at 1:20 PM

SUCCESS: Integrated in sakai-trunk-java-1.7 #522 (See http://builds.sakaiproject.org:8080/job/sakai-trunk-java-1.7/522/)
Fix getSites() when used passed joinable and properties.

If you attempted to search for joinable sites and also supplied properties to search for the parameters would be subsituted in the wrong place in the SQL resulting in no results returned (or the wrong results). This fixes the order that the values array is built up in so it matches the SQL string.

Original patch from Angel Nudea Lozano. (matthew.buckett@it.ox.ac.uk: rev 315629)

Matthew Buckett November 21, 2014 at 6:58 AM

Thanks for the patch. Applied to trunk.

I also wrote a testcase so it should get tested every time the build runs.
If someone can verify it's working on a night server it should get merged back.

Matthew Buckett November 21, 2014 at 5:33 AM

The building of SQL in the SiteService isn't very readable at all, but it looks like this is the right fix:

org.sakaiproject.site.impl.DbSiteService.DbStorage#getSitesWhere() This is the method that builds the where query and the order for the where queries is "type, joinable, properties".

org.sakaiproject.site.impl.DbSiteService.DbStorage#getSitesFields() This is where the fields are built and it looks to assume a where order of "type, properties, joinable"

The fix changes this so that getSitesFields() uses the same order as getSitesWhere().

NB: The getSitesFields() is also used by getSoftlyDeletedSites() however this doesn't look to work at all at the moment as the SQL WHERE query is badly formed and doesn't look to parse.