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 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 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"); }