public List<Pizza> findOrderByIdAsc() { List<Result<Record>> values = dslContext.selectDistinct() .from(PIZZA) .innerJoin(BASE).on(BASE.ID.eq(PIZZA.BASE_ID)) .innerJoin(PIZZA_TOPPINGS).on(PIZZA_TOPPINGS.PIZZA_ID.eq(PIZZA.ID)) .innerJoin(TOPPING).on(TOPPING.ID.eq(PIZZA_TOPPINGS.TOPPINGS_ID)) .orderBy(PIZZA.ID.asc()) .fetch() .intoGroups(PIZZA.fields()) .values() .stream() .collect(Collectors.toList()); if (values.isEmpty()) { return Collections.emptyList(); } return values.stream() .map(r -> { Pizza pizza = r.into(PIZZA.ID, PIZZA.NAME, PIZZA.PRICE).get(0).into(Pizza.class); pizza.setBase(r.into(BASE.ID, BASE.NAME).get(0).into(Base.class)); pizza.setToppings(r.sortAsc(TOPPING.ID).into(Topping.class)); return pizza; }) .collect(Collectors.toList()); }
public List<TeamUserEntry> listUsers(DSLContext tx, UUID teamId) { return tx.select(USERS.USER_ID, USERS.USERNAME, USERS.USER_TYPE, USER_TEAMS.TEAM_ROLE) .from(USER_TEAMS) .innerJoin(USERS).on(USERS.USER_ID.eq(USER_TEAMS.USER_ID)) .where(USER_TEAMS.TEAM_ID.eq(teamId)) .orderBy(USERS.USERNAME) .fetch((Record4<UUID, String, String, String> r) -> new TeamUserEntry(r.value1(), r.value2(), UserType.valueOf(r.value3()), TeamRole.valueOf(r.value4()))); }
public LandingEntry get(UUID id) { LandingPage lp = LANDING_PAGE.as("lp"); Projects p = PROJECTS.as("p"); Repositories r = REPOSITORIES.as("r"); Field<String> orgNameField = select(ORGANIZATIONS.ORG_NAME).from(ORGANIZATIONS).where(ORGANIZATIONS.ORG_ID.eq(p.ORG_ID)).asField(); try (DSLContext tx = DSL.using(cfg)) { return tx .select(lp.LANDING_PAGE_ID, p.ORG_ID, orgNameField, lp.PROJECT_ID, p.PROJECT_NAME, r.REPO_NAME, lp.NAME, lp.DESCRIPTION, lp.ICON) .from(lp) .innerJoin(p).on(p.PROJECT_ID.eq(lp.PROJECT_ID)) .innerJoin(r).on(r.REPO_ID.eq(lp.REPO_ID)) .where(lp.LANDING_PAGE_ID.eq(id)) .fetchOne(LandingDao::toEntity); } }
private List<LandingEntry> list(DSLContext tx, UUID orgId, UUID currentUserId) { LandingPage lp = LANDING_PAGE.as("lp"); Projects p = PROJECTS.as("p"); Repositories r = REPOSITORIES.as("r"); Field<String> orgNameField = select(ORGANIZATIONS.ORG_NAME).from(ORGANIZATIONS).where(ORGANIZATIONS.ORG_ID.eq(p.ORG_ID)).asField(); SelectConditionStep<Record1<UUID>> teamIds = select(TEAMS.TEAM_ID) .from(TEAMS) .where(TEAMS.ORG_ID.eq(orgId)); Condition filterByTeamMember = exists(selectOne().from(USER_TEAMS) .where(USER_TEAMS.USER_ID.eq(currentUserId) .and(USER_TEAMS.TEAM_ID.in(teamIds)))); SelectJoinStep<Record9<UUID, UUID, String, UUID, String, String, String, String, byte[]>> q = tx.select(lp.LANDING_PAGE_ID, p.ORG_ID, orgNameField, lp.PROJECT_ID, p.PROJECT_NAME, r.REPO_NAME, lp.NAME, lp.DESCRIPTION, lp.ICON) .from(lp) .innerJoin(p).on(p.PROJECT_ID.eq(lp.PROJECT_ID)) .innerJoin(r).on(r.REPO_ID.eq(lp.REPO_ID)); if (currentUserId != null) { q.where(or(p.VISIBILITY.eq(ProjectVisibility.PUBLIC.toString()), filterByTeamMember)); } return q.orderBy(lp.NAME) .fetch(LandingDao::toEntity); }