private CommonTableExpression<Record7<Long, Long, Long, Long, String, Timestamp, Integer>> withRecursiveExpression(DSLContext sql, Long postId) { return name(PCS).fields("id", "root_id", "post_id", "parent_id", "review", "created_on", "score") .as(sql.select( POST_COMMENT.ID, POST_COMMENT.ID, POST_COMMENT.POST_ID, POST_COMMENT.PARENT_ID, POST_COMMENT.REVIEW, POST_COMMENT.CREATED_ON, POST_COMMENT.SCORE) .from(POST_COMMENT) .where(POST_COMMENT.POST_ID.eq(postId).and(POST_COMMENT.PARENT_ID.isNull())) .unionAll( sql.select( POST_COMMENT.ID, field(name("post_comment_score", "root_id"), Long.class), POST_COMMENT.POST_ID, POST_COMMENT.PARENT_ID, POST_COMMENT.REVIEW, POST_COMMENT.CREATED_ON, POST_COMMENT.SCORE) .from(POST_COMMENT) .innerJoin(table(name(PCS))) .on(POST_COMMENT.PARENT_ID.eq(field(name(PCS, "id"), Long.class))) .where(POST_COMMENT.PARENT_ID.eq(field(name(PCS, "id"), Long.class))) ) ); }
@Override public Overview map(Record7<Integer, Integer, String, String, DateTime, Boolean, Boolean> r) { return Overview.builder() .ocppTagPk(r.value1()) .parentOcppTagPk(r.value2()) .idTag(r.value3()) .parentIdTag(r.value4()) .expiryDateDT(r.value5()) .expiryDate(humanize(r.value5())) .inTransaction(r.value6()) .blocked(r.value7()) .build(); }
@SuppressWarnings("unchecked") private Result<Record7<Integer, Integer, String, String, String, String, String>> getOverviewInternal(UserQueryForm form) { SelectQuery selectQuery = ctx.selectQuery(); selectQuery.addFrom(USER); selectQuery.addJoin(OCPP_TAG, JoinType.LEFT_OUTER_JOIN, USER.OCPP_TAG_PK.eq(OCPP_TAG.OCPP_TAG_PK)); selectQuery.addSelect( USER.USER_PK, USER.OCPP_TAG_PK, OCPP_TAG.ID_TAG, USER.FIRST_NAME, USER.LAST_NAME, USER.PHONE, USER.E_MAIL ); if (form.isSetUserPk()) { selectQuery.addConditions(USER.USER_PK.eq(form.getUserPk())); } if (form.isSetOcppIdTag()) { selectQuery.addConditions(includes(OCPP_TAG.ID_TAG, form.getOcppIdTag())); } if (form.isSetEmail()) { selectQuery.addConditions(includes(USER.E_MAIL, form.getEmail())); } if (form.isSetName()) { // Concatenate the two columns and search within the resulting representation // for flexibility, since the user can search by first or last name, or both. Field<String> joinedField = DSL.concat(USER.FIRST_NAME, USER.LAST_NAME); // Find a matching sequence anywhere within the concatenated representation selectQuery.addConditions(includes(joinedField, form.getName())); } return selectQuery.fetch(); }
@Test public void findProgram() { ClubManager clubManager = new ClubManager(); String id = UUID.randomUUID().toString(); Curriculum curriculum = Programs.AWANA.get(); String orgName = "An Org"; String locale = "en_US"; DSLContext create = DSL.using(SQLDialect.HSQLDB); MockDataProvider provider = ctx -> { assertWhere(id, ctx); // TODO: assert the join // TODO: refactor this mess Result<Record7<byte[], String, String, byte[], byte[], byte[], String>> result = create.newResult( ORGANIZATION.ID, ORGANIZATION.ORGANIZATIONNAME, ORGANIZATION.LOCALE, ORGANIZATION.CLUB_ID, CLUB.ID, CLUB.PARENT_CLUB_ID, CLUB.CURRICULUM); Record7<byte[], String, String, byte[], byte[], byte[], String> record = create.newRecord( ORGANIZATION.ID, ORGANIZATION.ORGANIZATIONNAME, ORGANIZATION.LOCALE, ORGANIZATION.CLUB_ID, CLUB.ID, CLUB.PARENT_CLUB_ID, CLUB.CURRICULUM); result.add(record); record.setValue(ORGANIZATION.ID, id.getBytes()); record.setValue(ORGANIZATION.CLUB_ID, id.getBytes()); record.setValue(ORGANIZATION.ORGANIZATIONNAME, orgName); record.setValue(ORGANIZATION.LOCALE, locale); record.setValue(CLUB.ID, id.getBytes()); record.setValue(CLUB.PARENT_CLUB_ID, null); record.setValue(CLUB.CURRICULUM, curriculum.getId()); return new MockResult[] {new MockResult(1, result)}; }; Program program = setup(provider).find(id, clubManager).get(); assertEquals(id, program.getId()); assertFalse(program.getParentGroup().isPresent()); assertEquals(curriculum, program.getCurriculum()); assertEquals(locale, program.getLocale().toString()); assertEquals(orgName, program.getShortCode()); }