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

The following examples show how to use org.jooq.impl.DSL#selectFrom() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: FlowSummaryWithTypesAndPhysicalsExport.java    From waltz with Apache License 2.0 6 votes vote down vote up 
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());

}
Example 2
Source File: ChangeInitiativeIdSelectorFactory.java    From waltz with Apache License 2.0 6 votes vote down vote up 
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());
}
Example 3
Source File: ChangeInitiativeDao.java    From waltz with Apache License 2.0 6 votes vote down vote up 
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);
}
Example 4
Source File: MeasurableIdSelectorFactory.java    From waltz with Apache License 2.0 6 votes vote down vote up 
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());
}
Example 5
Source File: InvolvementDao.java    From waltz with Apache License 2.0 6 votes vote down vote up 
@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);
}
Example 6
Source File: ApplicationIdSelectorFactory.java    From waltz with Apache License 2.0 5 votes vote down vote up 
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());
}
Example 7
Source File: ApplicationIdSelectorFactory.java    From waltz with Apache License 2.0 5 votes vote down vote up 
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);
}
Example 8
Source File: AppGroupDao.java    From waltz with Apache License 2.0 5 votes vote down vote up 
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);
    }
Example 9
Source File: ApplicationIdSelectorFactory.java    From waltz with Apache License 2.0 4 votes vote down vote up 
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());
}