private Object[] buildSearchParameters(StoryTellersParams params) {
Object[] searchParameters =
params.getSearchText() == null
? new Object[0]
: new Object[] {
"%" + params.getSearchText() + "%",
"%" + params.getSearchText() + "%",
"%" + params.getSearchText() + "%"
};
if (params.getCollectionId() != null) {
searchParameters = ArrayUtils.addAll(searchParameters, params.getCollectionId());
}
return searchParameters;
}
private String buildSearchClause(StoryTellersParams params) {
// a profile may have multiple addresses, here we just want the one
final String limitToOne =
"(a.idx=(SELECT MIN(a2.idx) FROM address a2 WHERE a2.entity=a.entity) OR a.idx IS "
+ "NULL)"
+ (params.getCollectionId() == null
? ""
: " AND "
+
// and a profile may have multiple stories
"cs.story=(SELECT MIN(cs2.story) FROM collection_story cs2 JOIN systemEntity es2 ON es2"
+ ".id=cs2.story "
+ "WHERE cs2.collection=cs.collection AND es2.owner=p.id)");
if (params.getSearchText() == null && params.getCollectionId() == null) {
return limitToOne;
} else if (params.getCollectionId() == null) { // then search text must be null
return limitToOne
+ " AND LOWER(u.handle) LIKE LOWER(?) OR LOWER(p.givenName) LIKE LOWER(?) OR LOWER(p"
+ ".surname) LIKE LOWER(?)";
} else if (params.getSearchText() == null
|| "".equals(params.getSearchText())) { // then collection ID must be null
return limitToOne + " AND cs.collection=?";
} else { // has both search text and collection
return limitToOne
+ " AND "
+ "LOWER(u.handle) LIKE LOWER(?) OR LOWER(p.givenName) LIKE LOWER(?) OR LOWER(p.surname) LIKE LOWER"
+ "(?) "
+ " AND cs.collection=?";
}
}
private String buildFromClause(StoryTellersParams params) {
return " FROM systemEntity e "
+ // that's the profile
"JOIN profile p on p.id=e.id "
+ "LEFT JOIN user u ON u.id=p.id "
+ // not all persons are users
"LEFT JOIN address a ON p.id=a.entity "
+ // not all persons have address; see address bit in where
// clause
(params.getCollectionId() == null
? ""
: "JOIN systemEntity es ON e.id=es.owner "
+ "JOIN collection_story cs ON es.id=cs.story");
}