Java Code Examples for org.jooq.impl.DSL#selectFrom()

private static Select<Record1<Long>> mkAppIdSelector(ApplicationIdSelectorFactory appIdSelectorFactory) {
    EntityReference infraRef = mkRef(EntityKind.ORG_UNIT, 6811);
    EntityReference entRiskRef = mkRef(EntityKind.ORG_UNIT, 3125);
    EntityReference regCtrlRef = mkRef(EntityKind.ORG_UNIT, 2761);

    Function<EntityReference, Select<Record1<Long>>> mkOrgUnitSelector = (ref) -> DSL
            .select(ENTITY_HIERARCHY.ID)
            .from(ENTITY_HIERARCHY)
            .where(ENTITY_HIERARCHY.ANCESTOR_ID.eq(ref.id()))
            .and(ENTITY_HIERARCHY.KIND.eq(ref.kind().name()));

    Select<Record1<Long>> ouSelector = DSL.selectFrom(
            mkOrgUnitSelector.apply(infraRef)
                .unionAll(mkOrgUnitSelector.apply(entRiskRef))
                .unionAll(mkOrgUnitSelector.apply(regCtrlRef)).asTable());

    return DSL
            .select(APPLICATION.ID)
            .from(APPLICATION)
            .where(APPLICATION.ORGANISATIONAL_UNIT_ID.in(ouSelector))
            .and(APPLICATION.LIFECYCLE_PHASE.notEqual(EntityLifecycleStatus.REMOVED.name()))
            .and(APPLICATION.IS_REMOVED.isFalse());

}
 

private Select<Record1<Long>> mkForRef(IdSelectionOptions options) {
    EntityReference ref = options.entityReference();

    Select<Record1<Long>> aToB = DSL
            .selectDistinct(ENTITY_RELATIONSHIP.ID_A.as("id"))
            .from(ENTITY_RELATIONSHIP)
            .where(ENTITY_RELATIONSHIP.KIND_A.eq(EntityKind.CHANGE_INITIATIVE.name()))
            .and(ENTITY_RELATIONSHIP.KIND_B.eq(ref.kind().name()))
            .and(ENTITY_RELATIONSHIP.ID_B.eq(ref.id()));

    Select<Record1<Long>> bToA = DSL
            .selectDistinct(ENTITY_RELATIONSHIP.ID_B.as("id"))
            .from(ENTITY_RELATIONSHIP)
            .where(ENTITY_RELATIONSHIP.KIND_B.eq(EntityKind.CHANGE_INITIATIVE.name()))
            .and(ENTITY_RELATIONSHIP.KIND_A.eq(ref.kind().name()))
            .and(ENTITY_RELATIONSHIP.ID_A.eq(ref.id()));

    return DSL.selectFrom(aToB.union(bToA).asTable());
}
 

public Collection<ChangeInitiative> findHierarchyForSelector(Select<Record1<Long>> selector) {
    SelectConditionStep<Record1<Long>> ancestors = dsl
            .select(ENTITY_HIERARCHY.ANCESTOR_ID)
            .from(ENTITY_HIERARCHY)
            .where(ENTITY_HIERARCHY.ID.in(selector)
                    .and(ENTITY_HIERARCHY.KIND.eq(EntityKind.CHANGE_INITIATIVE.name())));

    SelectConditionStep<Record1<Long>> descendants = DSL
            .select(ENTITY_HIERARCHY.ID)
            .from(ENTITY_HIERARCHY)
            .where(ENTITY_HIERARCHY.ANCESTOR_ID.in(selector)
                    .and(ENTITY_HIERARCHY.KIND.eq(EntityKind.CHANGE_INITIATIVE.name())));

    SelectOrderByStep<Record1<Long>> hierarchySelector = DSL.selectFrom(descendants.union(ancestors).asTable());

    return findForSelector(hierarchySelector);
}
 

private Select<Record1<Long>> mkForFlowDiagram(IdSelectionOptions options) {
    checkTrue(options.scope() == HierarchyQueryScope.EXACT, "Can only calculate flow diagram based selectors with exact scopes");
    long diagramId = options.entityReference().id();
    Select<Record1<Long>> viaAppRatings = mkBaseRatingBasedSelector()
            .innerJoin(FLOW_DIAGRAM_ENTITY)
            .on(FLOW_DIAGRAM_ENTITY.ENTITY_ID.eq(MEASURABLE_RATING.ENTITY_ID)
                    .and(FLOW_DIAGRAM_ENTITY.ENTITY_KIND.eq(MEASURABLE_RATING.ENTITY_KIND)))
            .where(FLOW_DIAGRAM_ENTITY.DIAGRAM_ID.eq(diagramId));

    Select<Record1<Long>> viaDirectRelationship = DSL
            .select(FLOW_DIAGRAM_ENTITY.ENTITY_ID)
            .from(FLOW_DIAGRAM_ENTITY)
            .where(FLOW_DIAGRAM_ENTITY.ENTITY_KIND.eq(EntityKind.MEASURABLE.name()))
            .and(FLOW_DIAGRAM_ENTITY.DIAGRAM_ID.eq(diagramId));

    return DSL.selectFrom(viaAppRatings.union(viaDirectRelationship).asTable());
}
 

@Deprecated
public List<Application> findAllApplicationsByEmployeeId(String employeeId) {
    SelectOrderByStep<Record1<String>> employeeIds = DSL
            .selectFrom(DSL
                .selectDistinct(PERSON_HIERARCHY.EMPLOYEE_ID)
                .from(PERSON_HIERARCHY)
                .where(PERSON_HIERARCHY.MANAGER_ID.eq(employeeId))
                .union(DSL.select(DSL.value(employeeId))
                        .from(PERSON_HIERARCHY)).asTable());

    SelectConditionStep<Record1<Long>> applicationIds = DSL
            .selectDistinct(INVOLVEMENT.ENTITY_ID)
            .from(INVOLVEMENT)
            .where(INVOLVEMENT.ENTITY_KIND
                    .eq(EntityKind.APPLICATION.name())
                    .and(INVOLVEMENT.EMPLOYEE_ID.in(employeeIds)));

    SelectConditionStep<Record> query = dsl
            .select(APPLICATION.fields())
            .from(APPLICATION)
            .where(APPLICATION.ID.in(applicationIds))
            .and(IS_ACTIVE);

    return query
            .fetch(ApplicationDao.TO_DOMAIN_MAPPER);
}
 

public static Select<Record1<Long>> mkForActor(IdSelectionOptions options) {
    ensureScopeIsExact(options);
    long actorId = options.entityReference().id();

    Condition applicationConditions = mkApplicationConditions(options);

    Select<Record1<Long>> sourceAppIds = DSL
            .select(logicalFlow.SOURCE_ENTITY_ID)
            .from(logicalFlow)
            .innerJoin(APPLICATION).on(APPLICATION.ID.eq(logicalFlow.SOURCE_ENTITY_ID))
            .where(logicalFlow.TARGET_ENTITY_ID.eq(actorId)
                    .and(logicalFlow.TARGET_ENTITY_KIND.eq(EntityKind.ACTOR.name()))
                    .and(logicalFlow.SOURCE_ENTITY_KIND.eq(EntityKind.APPLICATION.name()))
                    .and(logicalFlow.ENTITY_LIFECYCLE_STATUS.ne(REMOVED.name())))
                    .and(applicationConditions);

    Select<Record1<Long>> targetAppIds = DSL.select(logicalFlow.TARGET_ENTITY_ID)
            .from(logicalFlow)
            .innerJoin(APPLICATION).on(APPLICATION.ID.eq(logicalFlow.TARGET_ENTITY_ID))
            .where(logicalFlow.SOURCE_ENTITY_ID.eq(actorId)
                    .and(logicalFlow.SOURCE_ENTITY_KIND.eq(EntityKind.ACTOR.name()))
                    .and(logicalFlow.TARGET_ENTITY_KIND.eq(EntityKind.APPLICATION.name()))
                    .and(logicalFlow.ENTITY_LIFECYCLE_STATUS.ne(REMOVED.name())))
                    .and(applicationConditions);

    return DSL.selectFrom(sourceAppIds
            .union(targetAppIds).asTable());
}
 

public static SelectOrderByStep<Record1<Long>> mkForAppGroup(IdSelectionOptions options) {
    if (options.scope() != EXACT) {
        throw new UnsupportedOperationException(
                "App Groups are not hierarchical therefore ignoring requested scope of: " + options.scope());
    }

    Condition applicationConditions = mkApplicationConditions(options);

    SelectConditionStep<Record1<Long>> associatedOrgUnits = DSL
            .selectDistinct(ENTITY_HIERARCHY.ID)
            .from(APPLICATION_GROUP_OU_ENTRY)
            .innerJoin(ENTITY_HIERARCHY)
            .on(ENTITY_HIERARCHY.ANCESTOR_ID.eq(APPLICATION_GROUP_OU_ENTRY.ORG_UNIT_ID)
                    .and(ENTITY_HIERARCHY.KIND.eq(EntityKind.ORG_UNIT.name())))
            .where(APPLICATION_GROUP_OU_ENTRY.GROUP_ID.eq(options.entityReference().id()));

    SelectConditionStep<Record1<Long>> applicationIdsFromAssociatedOrgUnits = DSL
            .select(APPLICATION.ID)
            .from(APPLICATION)
            .innerJoin(ORGANISATIONAL_UNIT)
            .on(APPLICATION.ORGANISATIONAL_UNIT_ID.eq(ORGANISATIONAL_UNIT.ID))
            .where(ORGANISATIONAL_UNIT.ID.in(associatedOrgUnits));

    SelectConditionStep<Record1<Long>> directApps = DSL
            .select(APPLICATION_GROUP_ENTRY.APPLICATION_ID)
            .from(APPLICATION_GROUP_ENTRY)
            .where(APPLICATION_GROUP_ENTRY.GROUP_ID.eq(options.entityReference().id()));

    SelectWhereStep<Record1<Long>> appIds = DSL
            .selectFrom(directApps
                    .union(applicationIdsFromAssociatedOrgUnits)
                    .asTable());

    return DSL
            .select(APPLICATION.ID)
            .from(APPLICATION)
            .where(APPLICATION.ID.in(appIds))
            .and(applicationConditions);
}
 

public List<AppGroup> findRelatedByApplicationId(long appId, String username) {

        SelectConditionStep<Record1<Long>> groupsFromAppGroupEntry = dsl
                .select(APPLICATION_GROUP_ENTRY.GROUP_ID)
                .from(APPLICATION_GROUP_ENTRY)
                .where(APPLICATION_GROUP_ENTRY.APPLICATION_ID.eq(appId));

        SelectConditionStep<Record1<Long>> orgUnitIds = dsl
                .select(ENTITY_HIERARCHY.ANCESTOR_ID)
                .from(ENTITY_HIERARCHY)
                .innerJoin(ORGANISATIONAL_UNIT).on(ENTITY_HIERARCHY.ID.eq(ORGANISATIONAL_UNIT.ID))
                .innerJoin(APPLICATION).on(APPLICATION.ORGANISATIONAL_UNIT_ID.eq(ORGANISATIONAL_UNIT.ID))
                .where(APPLICATION.ID.eq(appId));

        SelectConditionStep<Record1<Long>> groupsFromAppGroupOUEntry = dsl
                .select(APPLICATION_GROUP_OU_ENTRY.GROUP_ID)
                .from(APPLICATION_GROUP_OU_ENTRY)
                .where(APPLICATION_GROUP_OU_ENTRY.ORG_UNIT_ID.in(orgUnitIds));

        SelectWhereStep<Record1<Long>> appGroups = DSL
                .selectFrom(groupsFromAppGroupEntry.union(groupsFromAppGroupOUEntry).asTable());

        return dsl
                .select(APPLICATION_GROUP.fields())
                .from(APPLICATION_GROUP)
                .where(APPLICATION_GROUP.ID.in(appGroups))
                .and(APPLICATION_GROUP.KIND.eq(AppGroupKind.PUBLIC.name())
                        .or(APPLICATION_GROUP.ID.in(getPrivateGroupIdByOwner(username))))
                .and(notRemoved)
                .fetch(TO_DOMAIN);
    }
 

private Select<Record1<Long>> mkForFlowDiagram(IdSelectionOptions options) {
    ensureScopeIsExact(options);

    long diagramId = options.entityReference().id();

    Condition logicalFlowInClause = LOGICAL_FLOW.ID.in(DSL
            .select(FLOW_DIAGRAM_ENTITY.ENTITY_ID)
            .from(FLOW_DIAGRAM_ENTITY)
            .where(FLOW_DIAGRAM_ENTITY.ENTITY_KIND.eq(EntityKind.LOGICAL_DATA_FLOW.name())
                    .and(FLOW_DIAGRAM_ENTITY.DIAGRAM_ID.eq(diagramId))));

    Condition applicationConditions = DSL.trueCondition(); //mkApplicationConditions(options);

    SelectConditionStep<Record1<Long>> directlyReferencedApps = DSL
            .select(flowDiagram.ENTITY_ID)
            .from(flowDiagram)
            .innerJoin(APPLICATION)
            .on(APPLICATION.ID.eq(flowDiagram.ENTITY_ID))
            .where(flowDiagram.DIAGRAM_ID.eq(diagramId))
            .and(flowDiagram.ENTITY_KIND.eq(EntityKind.APPLICATION.name()))
            .and(applicationConditions);

    SelectConditionStep<Record1<Long>> appsViaSourcesOfFlows = DSL
            .select(LOGICAL_FLOW.SOURCE_ENTITY_ID)
            .from(LOGICAL_FLOW)
            .innerJoin(APPLICATION)
            .on(APPLICATION.ID.eq(LOGICAL_FLOW.SOURCE_ENTITY_ID))
            .where(logicalFlowInClause)
            .and(LOGICAL_FLOW.SOURCE_ENTITY_KIND.eq(EntityKind.APPLICATION.name()))
            .and(applicationConditions);

    SelectConditionStep<Record1<Long>> appsViaTargetsOfFlows = DSL
            .select(LOGICAL_FLOW.TARGET_ENTITY_ID)
            .from(LOGICAL_FLOW)
            .innerJoin(APPLICATION)
            .on(APPLICATION.ID.eq(LOGICAL_FLOW.TARGET_ENTITY_ID))
            .where(logicalFlowInClause)
            .and(LOGICAL_FLOW.TARGET_ENTITY_KIND.eq(EntityKind.APPLICATION.name()))
            .and(applicationConditions);

    return DSL.selectFrom(
            directlyReferencedApps
                .unionAll(appsViaSourcesOfFlows)
                .unionAll(appsViaTargetsOfFlows).asTable());
}