@Override public List<Port> getUsedPortsForHostExcludingInstance(long hostId, long instanceId) { return create() .select(PORT.fields()) .from(PORT) .join(INSTANCE_HOST_MAP) .on(PORT.INSTANCE_ID.eq(INSTANCE_HOST_MAP.INSTANCE_ID)) .join(INSTANCE) .on(INSTANCE_HOST_MAP.INSTANCE_ID.eq(INSTANCE.ID)) .leftOuterJoin(SERVICE_EXPOSE_MAP) .on(SERVICE_EXPOSE_MAP.INSTANCE_ID.eq(INSTANCE.ID)) .where( INSTANCE_HOST_MAP .HOST_ID .eq(hostId) .and(INSTANCE.REMOVED.isNull()) .and(INSTANCE.ID.ne(instanceId)) .and( INSTANCE.STATE.in( InstanceConstants.STATE_STARTING, InstanceConstants.STATE_RESTARTING, InstanceConstants.STATE_RUNNING)) .and(INSTANCE_HOST_MAP.REMOVED.isNull()) .and(PORT.REMOVED.isNull()) .and(SERVICE_EXPOSE_MAP.UPGRADE.eq(false).or(SERVICE_EXPOSE_MAP.UPGRADE.isNull()))) .fetchInto(Port.class); }