def joinLayer(self, targetlayer, pkfield, sourcelayer, fkfield):
"""
Join the results of the SQL Server query to the pg layer
"""
joinInfo = QgsVectorLayerJoinInfo()
# Backward compatbility QGIS3=>2
if qversion == 3:
joinInfo.setTargetFieldName(pkfield)
joinInfo.setJoinLayer(sourcelayer)
joinInfo.setJoinFieldName(fkfield)
joinInfo.setUsingMemoryCache(True)
else: # QGIS 2
joinInfo.targetFieldName = pkfield
joinInfo.joinLayerId = sourcelayer.id()
joinInfo.joinFieldName = fkfield
joinInfo.memoryCache = True
targetlayer.addJoin(joinInfo)
targetlayer.updateFields()
return targetlayer
def execute_layers_join(layer, layer_field, column_header, fk_layer,
fk_field):
"""
Joins two layers with specified field.
:param layer: The destination layer of the merge.
:type layer: QgsVectorLayer
:param layer_field: The source layer of the merge.
:type layer_field: String
:param fk_layer: The foreign key layer object.
:type fk_layer: QgsVectorLayer
:param fk_field: The foreign key layer field name.
:type fk_field: String
:return:
:rtype:
"""
join = QgsVectorLayerJoinInfo()
join.joinLayerId = fk_layer.id()
join.joinFieldName = 'id'
join.setJoinFieldNamesSubset([fk_field])
join.targetFieldName = layer_field
join.memoryCache = True
join.prefix = '{} '.format(column_header)
layer.addJoin(join)
def join_field(input_table,
join_table,
field_to_calc,
field_to_copy,
joinfield_input_table,
joinfield_join_table,
inner_join=False):
"""Veld overnemen uit andere tabel o.b.v. tablejoin.
Het veld wat gevuld moet worden (field_to_calc) moet al wel bestaan en wordt in deze functie alleen gevuld.
Vul "pk" in bij joinfield_join_table om de primary key te laten bepalen of kies een ander veld"""
# voorbeeld: join_field(input_table="", join_table="", field_to_calc="", field_to_copy="", joinfield_input_table="", joinfield_join_table="")
if 1 == 1:
print_log(
"joining field {} from {}...".format(
field_to_calc, os.path.basename(join_table.name())), "d")
input_table.selectByIds([])
# add join old way qgis 2
## joinObject = QgsVectorJoinInfo()
## joinObject.joinLayerId = join_table.id()
## joinObject.joinFieldName = joinfield_join_table
## joinObject.joinFieldName = joinfield_join_table
## joinObject.targetFieldName = joinfield_input_table
# add join qgis 3
joinObject = QgsVectorLayerJoinInfo() # old: QgsVectorJoinInfo()
joinObject.setJoinLayer(join_table)
joinObject.setJoinFieldName(joinfield_join_table)
joinObject.setTargetFieldName(joinfield_input_table)
if not input_table.addJoin(joinObject):
print_log("join failed!", "w")
# calculate field
context = QgsExpressionContext()
scope = QgsExpressionContextScope()
context.appendScope(scope)
e = QgsExpression('"{}_{}"'.format(join_table.name(), field_to_copy))
##e.prepare(input_table.fields()) # qgis 2
print_log(
"expression = {}".format('"{}_{}"'.format(join_table.name(),
field_to_copy)), "d")
idx_field_to_copy = input_table.fields().indexFromName('{}_{}'.format(
join_table.name(), field_to_copy))
if idx_field_to_copy == -1:
print_log(
"[{}] is leeg omdat [{}] ontbreekt in kaartlaag '{}'.".format(
field_to_calc, field_to_copy, join_table.name()), "w")
idx_joinfield_join_table = join_table.fields().indexFromName(
joinfield_join_table)
if idx_joinfield_join_table == -1:
print_log(
"join_field [{}] ontbreekt in {}. table join mislukt.".format(
joinfield_join_table, join_table.name()), "w")
input_table.startEditing()
idx = input_table.fields().indexFromName(field_to_calc)
if idx == -1:
print_log(
"field_to_calculate [{}] niet gevonden in kaartlaag '{}'.".
format(field_to_calc, input_table.name()), "w")
if inner_join:
print_log("inner_join = True", 'd')
s_expr = '"{}_{}" IS NOT NULL'.format(join_table.name(),
field_to_copy)
print_log(s_expr, 'd')
expr = QgsExpression(s_expr)
it = input_table.getFeatures(
QgsFeatureRequest(expr)) # iterator object
input_table.selectByIds([i.id() for i in it])
features = input_table.selectedFeatures()
else:
features = input_table.getFeatures()
for f in features:
scope.setFeature(f)
f[idx] = e.evaluate(context) # qgis 2: e.evaluate(f)
input_table.updateFeature(f)
input_table.commitChanges()
input_table.removeJoin(joinObject.joinLayerId())