/** * Gets data points for a set of tags that a user has access to (i.e. readPermission, setPermission or the datasource editPermission). * * @param restrictions * @param user * @param callback * @param sort (may be null) * @param limit (may be null) * @param offset (may be null) */ public void dataPointsForTags(Map<String, String> restrictions, User user, MappedRowCallback<DataPointVO> callback, List<SortField<Object>> sort, Integer limit, Integer offset) { if (restrictions.isEmpty()) { throw new IllegalArgumentException("restrictions should not be empty"); } Map<String, Name> tagKeyToColumn = DataPointTagsDao.getInstance().tagKeyToColumn(restrictions.keySet()); List<Condition> conditions = restrictions.entrySet().stream().map(e -> { return DSL.field(DATA_POINT_TAGS_PIVOT_ALIAS.append(tagKeyToColumn.get(e.getKey()))).eq(e.getValue()); }).collect(Collectors.toCollection(ArrayList::new)); if (!user.hasAdminPermission()) { conditions.add(this.userHasPermission(user)); } Table<Record> pivotTable = DataPointTagsDao.getInstance().createTagPivotSql(tagKeyToColumn).asTable().as(DATA_POINT_TAGS_PIVOT_ALIAS); SelectOnConditionStep<Record> select = this.create.select(this.fields).from(this.joinedTable).leftJoin(pivotTable) .on(DataPointTagsDao.PIVOT_ALIAS_DATA_POINT_ID.eq(ID)); this.customizedQuery(select, DSL.and(conditions), sort, limit, offset, callback); }
public PageResult<Comment> page(PageResult<Comment> page, Optional<Integer> state, Optional<String> articleId) { List<Condition> conditions = new ArrayList<>(); state.ifPresent(s ->{ conditions.add(comment.STATE.eq(s)); }); articleId.ifPresent(a ->{ conditions.add(comment.ARTICLE_ID.eq(a)); }); return dao.fetch(page, e -> { return e.select(Fields.all(comment.fields(), article.TITLE.as("articleTitle"))) .from(comment) .leftJoin(article).on(comment.ARTICLE_ID.eq(article.ID)) .where(conditions).orderBy(comment.CREATE_TIME.desc()); }, Comment.class); }
private PageResult<Article> searchPageByCondition(PageResult<Article> page, Stream<Condition> cond) { page = dao.fetch(page, e -> { return e.select(Fields.all(C_ARTICLE.fields(),C_CATALOG.NAME.as("catalogName"))) .from(C_ARTICLE) .leftJoin(C_CATALOG).on(C_ARTICLE.CATALOG_ID.eq(C_CATALOG.ID)) .where(cond.collect(Collectors.toList())) .orderBy(C_ARTICLE.CREATE_TIME.desc()); }, Article.class); List<Article> articles = page.getData(); if (!articles.isEmpty()) { Map<String,Article> articlesMap = articles.stream().collect(Collectors.toMap(Article::getId, a -> { a.setTags(new ArrayList<>()); return a; })); List<ArticleTag> articleTags = articleTagService.findTags(articlesMap.keySet()); articleTags.forEach(articleTag -> { articlesMap.get(articleTag.getArticleId()).getTags().add(articleTag.getTag()); }); } return page; }
@Override public <R extends Record> SelectJoinStep<R> joinTables(SelectJoinStep<R> select, ConditionSortLimit conditions) { if (conditions instanceof ConditionSortLimitWithTagKeys) { Map<String, Name> tagKeyToColumn = ((ConditionSortLimitWithTagKeys) conditions).getTagKeyToColumn(); if (!tagKeyToColumn.isEmpty()) { Table<Record> pivotTable = DataPointTagsDao.getInstance().createTagPivotSql(tagKeyToColumn).asTable().as(DATA_POINT_TAGS_PIVOT_ALIAS); return select.leftJoin(pivotTable) .on(DataPointTagsDao.PIVOT_ALIAS_DATA_POINT_ID.eq(ID)); } } return select; }
public List<Tag> findTags(String articleId) { return dao.execute(e -> { return e.select(C_TAG.fields()).from(C_TAG) .leftJoin(C_ARTICLE_TAG).on(C_ARTICLE_TAG.TAG_ID.eq(C_TAG.ID)) .where(C_ARTICLE_TAG.ARTICLE_ID.eq(articleId)) .fetchInto(Tag.class); }); } }
public List<ArticleTag> findTags(Collection<String> articleIds) { List<ArticleTag> tags = dao.execute(e -> { return e.select(Fields.all(C_TAG.fields(), C_ARTICLE_TAG.fields())).from(C_ARTICLE_TAG) .leftJoin(C_TAG).on(C_TAG.ID.eq(C_ARTICLE_TAG.TAG_ID)) .where(C_ARTICLE_TAG.ARTICLE_ID.in(articleIds)) .fetch(r -> { ArticleTag articleTag = r.into(ArticleTag.class); articleTag.setTag(r.into(Tag.class)); return articleTag; }); }); return tags; }
public List<Catalog> findAllDetail() { return dao.execute(e -> { return e.select(Fields.all(C_CATALOG.fields(), DSL.count(C_ARTICLE.ID).as("articleCount"))).from(C_ARTICLE) .leftJoin(C_CATALOG).on(C_CATALOG.ID.eq(C_ARTICLE.CATALOG_ID)) .where(C_ARTICLE.STATUS.eq(Article.STATUS_PUBLISH)) .groupBy(C_CATALOG.ID) .orderBy(C_CATALOG.SORT).fetch(r -> { return dao.mapperEntityEx(r, Catalog.class); }); }); } }
public List<Article> findAll(int status) { final Field<?> catalogName = C_CATALOG.NAME.as("catalogName"); List<Article> list = dao.execute(e -> { return e.select(Fields.all(C_ARTICLE.fields(),catalogName)) .from(C_ARTICLE) .leftJoin(C_CATALOG).on(C_ARTICLE.CATALOG_ID.eq(C_CATALOG.ID)) .where(C_ARTICLE.STATUS.eq(status)) .orderBy(C_ARTICLE.CREATE_TIME.desc()); }).fetch(r -> { Article art = r.into(Article.class); art.setCatalogName(r.get(catalogName, String.class)); return art; }); return list; }
private SelectJoinStep<Record12<UUID, UUID, String, UUID, String, UUID, String, String, String[], String, String, String>> selectTriggers(DSLContext tx) { Organizations o = ORGANIZATIONS.as("o"); Projects p = PROJECTS.as("p"); Repositories r = REPOSITORIES.as("r"); return tx.select( TRIGGERS.TRIGGER_ID, o.ORG_ID, o.ORG_NAME, TRIGGERS.PROJECT_ID, p.PROJECT_NAME, TRIGGERS.REPO_ID, r.REPO_NAME, TRIGGERS.EVENT_SOURCE, TRIGGERS.ACTIVE_PROFILES, TRIGGERS.ARGUMENTS.cast(String.class), TRIGGERS.CONDITIONS.cast(String.class), TRIGGERS.TRIGGER_CFG.cast(String.class)) .from(TRIGGERS) .leftJoin(p).on(p.PROJECT_ID.eq(TRIGGERS.PROJECT_ID)) .leftJoin(o).on(o.ORG_ID.eq(p.ORG_ID)) .leftJoin(r).on(r.REPO_ID.eq(TRIGGERS.REPO_ID)); }
public ArticleDetail getDetail(String id) { CArticle article = C_ARTICLE.as("a"); CArticle before = C_ARTICLE.as("b"); CArticle next = C_ARTICLE.as("n"); CArticle inner = C_ARTICLE.as("i"); ArticleDetail a = dao.execute(e -> { return e.select(Fields.all(article.fields(), before.ID.as("beforeId"), before.TITLE.as("beforeTitle"), next.ID.as("nextId"), next.TITLE.as("nextTitle") )).from(article) .leftJoin(before).on(before.ID.eq(e.select(inner.ID).from(inner).where(article.CREATE_TIME.ge(inner.CREATE_TIME)).and(inner.ID.ne(article.ID).and(inner.STATUS.eq(Article.STATUS_PUBLISH))).orderBy(inner.CREATE_TIME.desc()).limit(0, 1))) .leftJoin(next).on((next.ID.eq(e.select(inner.ID).from(inner).where(article.CREATE_TIME.le(inner.CREATE_TIME)).and(inner.ID.ne(article.ID).and(inner.STATUS.eq(Article.STATUS_PUBLISH))).orderBy(inner.CREATE_TIME).limit(0, 1)))) .where(article.ID.eq(id)) .fetchOne(r -> { return dao.mapperEntityEx(r, ArticleDetail.class); }); }); List<Tag> tags = articleTagService.findTags(a.getId()); a.setTags(tags); return a; }
.on(t.TYPNAMESPACE.eq(oid(nt)))) .on(a.ATTTYPID.eq(oid(t)))) .leftJoin(bt .join(nbt) .on(bt.TYPNAMESPACE.eq(oid(nbt))))