Correct selection filters

[sjib]

• Control center:

wastewater/plugin/teksi_wastewater/interlis/interlis_model_mapping/interlis_exporter_to_intermediate_schema.py

Line 1210 in 7149100

query.join(self.model_classes_tww_od.throttle_shut_off_unit)

as in qgep:
QGEP/qgepqwat2ili@67bdfcf

        query = query.join(qgep_model.throttle_shut_off_unit).join(
            qgep_model.wastewater_node,
            qgep_model.wastewater_node.obj_id
            == qgep_model.throttle_shut_off_unit.fk_wastewater_node,
        ).filter(
            qgep_model.wastewater_networkelement.obj_id.in_(subset_ids)
        )

[sjib]

• profile_geometry
  Change to no filtering

    query = qgep_session.query(qgep_model.profile_geometry)
    # always export all profile_geometry

[sjib]

• hydr_geom_relation
  Change to no filtering

query = qgep_session.query(qgep_model.hydr_geom_relation) # always export all hydr_geom_relation

[sjib]

• filter mechanical_pretreatment
  query = qgep_session.query(qgep_model.mechanical_pretreatment)

  specify relation key - only directly to wastewater_structure

  if filtered:
  query = (
  query.join(qgep_model.wastewater_structure, qgep_model.mechanical_pretreatment.fk_wastewater_structure == qgep_model.wastewater_structure.obj_id)
  .join(qgep_model.wastewater_networkelement)
  .filter(qgep_model.wastewater_networkelement.obj_id.in_(subset_ids))
  )

[sjib]

• filter retention_body

    # specify relation key - only directly to wastewater_structure
    if filtered:
        query = (
            query.join(
                qgep_model.infiltration_installation,
                qgep_model.retention_body.fk_infiltration_installation
                == qgep_model.infiltration_installation.obj_id,
            )
            .join(qgep_model.wastewater_networkelement)
            .filter(qgep_model.wastewater_networkelement.obj_id.in_(subset_ids))
        )

[sjib]

• electric_equipment

    if filtered:
        query = (
            query.join(
                QGEP.wastewater_structure,
                QGEP.structure_part.fk_wastewater_structure == QGEP.wastewater_structure.obj_id,
            )
            .join(QGEP.wastewater_networkelement)
            .filter(QGEP.wastewater_networkelement.obj_id.in_(subset_ids))
        )
        # add sql statement to logger
        statement = query.statement
        logger.debug(f" selection query = {statement}")

[sjib]

electromechanical_equipment

    if filtered:

        # query = query.join(QGEP.wastewater_structure, QGEP.wastewater_networkelement).filter(
        # QGEP.wastewater_networkelement.obj_id.in_(subset_ids)
        # )
        query = (
            query.join(
                QGEP.wastewater_structure,
                QGEP.structure_part.fk_wastewater_structure == QGEP.wastewater_structure.obj_id,
            )
            .join(QGEP.wastewater_networkelement)
            .filter(QGEP.wastewater_networkelement.obj_id.in_(subset_ids))
        )
        # add sql statement to logger
        statement = query.statement
        logger.debug(f" selection query = {statement}")

[sjib]

• measuring point

if filtered:
        query1 = query.join(
            QGEP.wastewater_structure,
            QGEP.measuring_point.fk_wastewater_structure == QGEP.wastewater_structure.obj_id,
        ).join(QGEP.wastewater_networkelement)

        # needs to add QGEP.wastewater_structure as waste_water_treatment_plant is a subclass of organisation that has a relation to wastewater_structure and then wastewater_networkelement
        # variant1 for query2
        # query2=query.join(
        #   QGEP.waste_water_treatment_plant,
        #   (QGEP.wastewater_structure, QGEP.waste_water_treatment_plant.obj_id == QGEP.wastewater_structure.fk_owner),
        #   (QGEP.wastewater_structure, QGEP.waste_water_treatment_plant.obj_id == QGEP.wastewater_structure.fk_provider),
        #   QGEP.wastewater_networkelement,
        # )
        # variant2 for query2
        # try with extra or_
        # or_(
        # QGEP.waste_water_treatment_plant.obj_id == QGEP.wastewater_structure.fk_owner,
        # QGEP.waste_water_treatment_plant.obj_id == QGEP.wastewater_structure.fk_provider,
        # ),
        # QGEP.wastewater_networkelement,

        # )

        # query2 via waste_water_treatment_plant Release 2015 where waste_water_treatment_plant is subclass of organisation
        query2 = (
            query.join(
                QGEP.waste_water_treatment_plant,
                QGEP.measuring_point.fk_waste_water_treatment_plant
                == QGEP.waste_water_treatment_plant.obj_id,
            )
            .join(
                QGEP.wastewater_structure,
                QGEP.wastewater_structure.fk_owner == QGEP.waste_water_treatment_plant.obj_id,
            )
            .join(QGEP.wastewater_networkelement)
        )
        # only until VSA-DSS Release 2015
        query3 = (
            query.join(
                QGEP.water_course_segment,
                QGEP.measuring_point.fk_water_course_segment == QGEP.water_course_segment.obj_id,
            )
            .join(
                QGEP.river,
                # Fehler im Datenmodell fk_watercourse should be name fk_surface_water_bodies (resp. fk_surface_water_body - class should be renamed to single)
                QGEP.water_course_segment.fk_watercourse == QGEP.river.obj_id,
            )
            .join(
                QGEP.sector_water_body,
                QGEP.sector_water_body.fk_surface_water_bodies == QGEP.sector_water_body.obj_id,
            )
            .join(
                QGEP.discharge_point,
                QGEP.discharge_point.fk_sector_water_body == QGEP.sector_water_body.obj_id,
            )
            .join(QGEP.wastewater_networkelement)
        )
        query = query.union(query1, query2, query3)
        # query = query.union(query1, query3)
        query = query.filter(QGEP.wastewater_networkelement.obj_id.in_(subset_ids))
        # add sql statement to logger
        statement = query.statement
        logger.debug(f" selection query = {statement}")

[sjib]

• measuring_device

if filtered:

        # query = query.join(
        # QGEP.measuring_point, QGEP.wastewater_structure, QGEP.wastewater_networkelement
        # ).filter(QGEP.wastewater_networkelement.obj_id.in_(subset_ids))

        query1 = (
            query.join(
                QGEP.measuring_point,
                QGEP.measuring_device.fk_measuring_point == QGEP.measuring_point.obj_id,
            )
            .join(
                QGEP.wastewater_structure,
                QGEP.measuring_point.fk_wastewater_structure == QGEP.wastewater_structure.obj_id,
            )
            .join(QGEP.wastewater_networkelement)
        )
        # query2 via waste_water_treatment_plant Release 2015 where waste_water_treatment_plant is subclass of organisation
        query2 = (
            query.join(
                QGEP.measuring_point,
                QGEP.measuring_device.fk_measuring_point == QGEP.measuring_point.obj_id,
            )
            .join(
                QGEP.waste_water_treatment_plant,
                QGEP.measuring_point.fk_waste_water_treatment_plant
                == QGEP.waste_water_treatment_plant.obj_id,
            )
            .join(
                QGEP.wastewater_structure,
                QGEP.wastewater_structure.fk_owner == QGEP.waste_water_treatment_plant.obj_id,
            )
            .join(QGEP.wastewater_networkelement)
        )
        # only until VSA-DSS Release 2015
        query3 = (
            query.join(
                QGEP.measuring_point,
                QGEP.measuring_device.fk_measuring_point == QGEP.measuring_point.obj_id,
            )
            .join(
                QGEP.water_course_segment,
                QGEP.measuring_point.fk_water_course_segment == QGEP.water_course_segment.obj_id,
            )
            .join(
                QGEP.river,
                # Fehler im Datenmodell fk_watercourse should be name fk_surface_water_bodies (resp. fk_surface_water_body - class should be renamed to single)
                QGEP.water_course_segment.fk_watercourse == QGEP.river.obj_id,
            )
            .join(
                QGEP.sector_water_body,
                QGEP.sector_water_body.fk_surface_water_bodies == QGEP.sector_water_body.obj_id,
            )
            .join(
                QGEP.discharge_point,
                QGEP.discharge_point.fk_sector_water_body == QGEP.sector_water_body.obj_id,
            )
            .join(QGEP.wastewater_networkelement)
        )
        query = query.union(query1, query2, query3)
        # query = query.union(query1, query3)
        query = query.filter(QGEP.wastewater_networkelement.obj_id.in_(subset_ids))
        # add sql statement to logger
        statement = query.statement
        logger.debug(f" selection query = {statement}")

[sjib]

• measurment_series

if filtered:

        # query = query.join(
        # QGEP.measuring_point, QGEP.wastewater_structure, QGEP.wastewater_networkelement
        # ).filter(QGEP.wastewater_networkelement.obj_id.in_(subset_ids))
        query1 = (
            query.join(
                QGEP.measuring_point,
                QGEP.measurement_series.fk_measuring_point == QGEP.measuring_point.obj_id,
            )
            .join(
                QGEP.wastewater_structure,
                QGEP.measuring_point.fk_wastewater_structure == QGEP.wastewater_structure.obj_id,
            )
            .join(QGEP.wastewater_networkelement)
        )
        # query2 via waste_water_treatment_plant Release 2015 where waste_water_treatment_plant is subclass of organisation
        query2 = (
            query.join(
                QGEP.measuring_point,
                QGEP.measurement_series.fk_measuring_point == QGEP.measuring_point.obj_id,
            )
            .join(
                QGEP.waste_water_treatment_plant,
                QGEP.measuring_point.fk_waste_water_treatment_plant
                == QGEP.waste_water_treatment_plant.obj_id,
            )
            .join(
                QGEP.wastewater_structure,
                QGEP.wastewater_structure.fk_owner == QGEP.waste_water_treatment_plant.obj_id,
            )
            .join(QGEP.wastewater_networkelement)
        )
        # only until VSA-DSS Release 2015
        query3 = (
            query.join(
                QGEP.measuring_point,
                QGEP.measurement_series.fk_measuring_point == QGEP.measuring_point.obj_id,
            )
            .join(
                QGEP.water_course_segment,
                QGEP.measuring_point.fk_water_course_segment == QGEP.water_course_segment.obj_id,
            )
            .join(
                QGEP.river,
                # Fehler im Datenmodell fk_watercourse should be name fk_surface_water_bodies (resp. fk_surface_water_body - class should be renamed to single)
                QGEP.water_course_segment.fk_watercourse == QGEP.river.obj_id,
            )
            .join(
                QGEP.sector_water_body,
                QGEP.sector_water_body.fk_surface_water_bodies == QGEP.sector_water_body.obj_id,
            )
            .join(
                QGEP.discharge_point,
                QGEP.discharge_point.fk_sector_water_body == QGEP.sector_water_body.obj_id,
            )
            .join(QGEP.wastewater_networkelement)
        )
        query = query.union(query1, query2, query3)
        # query = query.union(query1, query3)
        query = query.filter(QGEP.wastewater_networkelement.obj_id.in_(subset_ids))
        # add sql statement to logger
        statement = query.statement
        logger.debug(f" selection query = {statement}")

[sjib]

• measurement_result

if filtered:

        # query = query.join(
        # QGEP.measurement_series,
        # QGEP.measuring_point,
        # QGEP.wastewater_structure,
        # QGEP.wastewater_networkelement,
        # ).filter(QGEP.wastewater_networkelement.obj_id.in_(subset_ids))
        query1 = (
            query.join(
                QGEP.measurement_series,
                QGEP.measurement_result.fk_measurement_series == QGEP.measurement_series.obj_id,
            )
            .join(
                QGEP.measuring_point,
                QGEP.measurement_series.fk_measuring_point == QGEP.measuring_point.obj_id,
            )
            .join(
                QGEP.wastewater_structure,
                QGEP.measuring_point.fk_wastewater_structure == QGEP.wastewater_structure.obj_id,
            )
            .join(QGEP.wastewater_networkelement)
        )
        # query2 via waste_water_treatment_plant Release 2015 where waste_water_treatment_plant is subclass of organisation
        query2 = (
            query.join(
                QGEP.measurement_series,
                QGEP.measurement_result.fk_measurement_series == QGEP.measurement_series.obj_id,
            )
            .join(
                QGEP.measuring_point,
                QGEP.measurement_series.fk_measuring_point == QGEP.measuring_point.obj_id,
            )
            .join(
                QGEP.waste_water_treatment_plant,
                QGEP.measuring_point.fk_waste_water_treatment_plant
                == QGEP.waste_water_treatment_plant.obj_id,
            )
            .join(
                QGEP.wastewater_structure,
                QGEP.wastewater_structure.fk_owner == QGEP.waste_water_treatment_plant.obj_id,
            )
            .join(QGEP.wastewater_networkelement)
        )
        # only until VSA-DSS Release 2015
        query3 = (
            query.join(
                QGEP.measurement_series,
                QGEP.measurement_result.fk_measurement_series == QGEP.measurement_series.obj_id,
            )
            .join(
                QGEP.measuring_point,
                QGEP.measurement_series.fk_measuring_point == QGEP.measuring_point.obj_id,
            )
            .join(
                QGEP.water_course_segment,
                QGEP.measuring_point.fk_water_course_segment == QGEP.water_course_segment.obj_id,
            )
            .join(
                QGEP.river,
                # Fehler im Datenmodell fk_watercourse should be name fk_surface_water_bodies (resp. fk_surface_water_body - class should be renamed to single)
                QGEP.water_course_segment.fk_watercourse == QGEP.river.obj_id,
            )
            .join(
                QGEP.sector_water_body,
                QGEP.sector_water_body.fk_surface_water_bodies == QGEP.sector_water_body.obj_id,
            )
            .join(
                QGEP.discharge_point,
                QGEP.discharge_point.fk_sector_water_body == QGEP.sector_water_body.obj_id,
            )
            .join(QGEP.wastewater_networkelement)
        )
        # query4 not implemented via measuring_device
        query = query.union(query1, query2, query3)
        # query = query.union(query1, query3)
        query = query.filter(QGEP.wastewater_networkelement.obj_id.in_(subset_ids))
        # add sql statement to logger
        statement = query.statement
        logger.debug(f" selection query = {statement}")

[sjib]

• backflow_prevention

    # side fk_throttle_shut_off_unit and fk_overflow not considered in filter query - they are usually added only for log_cards and then the corresponding nodes exist anyway thru the direct relation.
    if filtered:
        query = (
            query.join(
                QGEP.wastewater_structure,
                QGEP.structure_part.fk_wastewater_structure == QGEP.wastewater_structure.obj_id,
            )
            .join(QGEP.wastewater_networkelement)
            .filter(QGEP.wastewater_networkelement.obj_id.in_(subset_ids))
        )
        # add sql statement to logger
        statement = query.statement
        logger.debug(f" selection query = {statement}")

[sjib]

• solids_retention

    if filtered:

        # query = query.join(QGEP.wastewater_structure, QGEP.wastewater_networkelement).filter(
        # QGEP.wastewater_networkelement.obj_id.in_(subset_ids)
        # )
        query = (
            query.join(
                QGEP.wastewater_structure,
                QGEP.structure_part.fk_wastewater_structure == QGEP.wastewater_structure.obj_id,
            )
            .join(QGEP.wastewater_networkelement)
            .filter(QGEP.wastewater_networkelement.obj_id.in_(subset_ids))
        )
        # add sql statement to logger
        statement = query.statement
        logger.debug(f" selection query = {statement}")

[sjib]

• tank_cleaning

    if filtered:

        # query = query.join(QGEP.wastewater_structure, QGEP.wastewater_networkelement).filter(
        # QGEP.wastewater_networkelement.obj_id.in_(subset_ids)
        # )
        query = (
            query.join(
                QGEP.wastewater_structure,
                QGEP.structure_part.fk_wastewater_structure == QGEP.wastewater_structure.obj_id,
            )
            .join(QGEP.wastewater_networkelement)
            .filter(QGEP.wastewater_networkelement.obj_id.in_(subset_ids))
        )
        # add sql statement to logger
        statement = query.statement
        logger.debug(f" selection query = {statement}")

[sjib]

• tank_emptying

 # side fk_throttle_shut_off_unit and fk_overflow not considered in filter query - they are usually added only for log_cards and then the corresponding nodes exist anyway thru the direct relation.
    if filtered:

        # query = query.join(QGEP.wastewater_structure, QGEP.wastewater_networkelement).filter(
        # QGEP.wastewater_networkelement.obj_id.in_(subset_ids)
        # )
        query = (
            query.join(
                QGEP.wastewater_structure,
                QGEP.structure_part.fk_wastewater_structure == QGEP.wastewater_structure.obj_id,
            )
            .join(QGEP.wastewater_networkelement)
            .filter(QGEP.wastewater_networkelement.obj_id.in_(subset_ids))
        )
        # add sql statement to logger
        statement = query.statement
        logger.debug(f" selection query = {statement}")

[sjib]

• maintenance_event

 # explicit join for n:m re_maintenance_event_wastewater_structure
    if filtered:
        query = (
            query.join(
                QGEP.re_maintenance_event_wastewater_structure,
                QGEP.re_maintenance_event_wastewater_structure.fk_maintenance_event
                == QGEP.maintenance_event.obj_id,
            )
            .join(
                QGEP.wastewater_structure,
                QGEP.re_maintenance_event_wastewater_structure.fk_wastewater_structure
                == QGEP.wastewater_structure.obj_id,
            )
            .join(QGEP.wastewater_networkelement)
            .filter(QGEP.wastewater_networkelement.obj_id.in_(subset_ids))
        )
        # add sql statement to logger
        statement = query.statement
        logger.debug(f" selection query = {statement}")

[sjib]

• hydr_geom_relation

    # always export all hydr_geom_relation - does not work -> fk_errors
    if filtered:
        query = query.join(qgep_model.hydr_geometry).join(qgep_model.wastewater_node).filter(
            qgep_model.wastewater_networkelement.obj_id.in_(subset_ids)
        )

[sjib]

• re_maintenance_event_wastewater_structure

  explicit join for n:m re_maintenance_event_wastewater_structure

    if filtered:
        query = (
            query.join(
                qgep_model.re_maintenance_event_wastewater_structure,
                qgep_model.re_maintenance_event_wastewater_structure.fk_maintenance_event
                == qgep_model.maintenance_event.obj_id,
            )
            .join(
                qgep_model.wastewater_structure,
                qgep_model.re_maintenance_event_wastewater_structure.fk_wastewater_structure
                == qgep_model.wastewater_structure.obj_id,
            )
            .join(qgep_model.wastewater_networkelement)
            .filter(qgep_model.wastewater_networkelement.obj_id.in_(subset_ids))
        )
        # add sql statement to logger
        statement = query.statement
        logger.debug(f" selection query = {statement}")