Skip to content

Commit

Permalink
Merge pull request #7724 from agiudiceandrea/patch-fix-19636
Browse files Browse the repository at this point in the history 
Fix "Filter on joined fields": add geometry for spatial layer and handle special field/layer names
  • Loading branch information
Hugo Mercier committed Sep 3, 2018
2 parents 7721694 + 1971f8a commit a80117b
Show file tree
Hide file tree
Showing 2 changed files with 32 additions and 14 deletions.
14 changes: 9 additions & 5 deletions src/core/qgsvirtuallayerdefinitionutils.cpp
View file
Expand Up @@ -28,6 +28,10 @@ QgsVirtualLayerDefinition QgsVirtualLayerDefinitionUtils::fromJoinedLayer( QgsVe
QStringList leftJoins;
QStringList columns;

// add the geometry column if the layer is spatial
if ( layer->isSpatial() )
columns << "t.geometry";

// look for the uid
QgsFields fields = layer->dataProvider()->fields();
{
Expand All @@ -51,7 +55,7 @@ QgsVirtualLayerDefinition QgsVirtualLayerDefinitionUtils::fromJoinedLayer( QgsVe
const QgsFields providerFields = layer->dataProvider()->fields();
for ( const auto &f : providerFields )
{
columns << "t." + f.name();
columns << "t.\"" + f.name() + "\"";
}

int joinIdx = 0;
Expand All @@ -63,12 +67,12 @@ QgsVirtualLayerDefinition QgsVirtualLayerDefinitionUtils::fromJoinedLayer( QgsVe
continue;
QString prefix = join.prefix().isEmpty() ? joinedLayer->name() + "_" : join.prefix();

leftJoins << QStringLiteral( "LEFT JOIN %1 AS %2 ON t.\"%5\"=%2.\"%3\"" ).arg( joinedLayer->id(), joinName, join.joinFieldName(), join.targetFieldName() );
leftJoins << QStringLiteral( "LEFT JOIN \"%1\" AS %2 ON t.\"%5\"=%2.\"%3\"" ).arg( joinedLayer->id(), joinName, join.joinFieldName(), join.targetFieldName() );
if ( join.joinFieldNamesSubset() )
{
Q_FOREACH ( const QString &f, *join.joinFieldNamesSubset() )
{
columns << joinName + "." + f + " AS " + prefix + f;
columns << joinName + ".\"" + f + "\" AS \"" + prefix + f + "\"";
}
}
else
Expand All @@ -78,12 +82,12 @@ QgsVirtualLayerDefinition QgsVirtualLayerDefinitionUtils::fromJoinedLayer( QgsVe
{
if ( f.name() == join.joinFieldName() )
continue;
columns << joinName + "." + f.name() + " AS " + prefix + f.name();
columns << joinName + ".\"" + f.name() + "\" AS \"" + prefix + f.name() + "\"";
}
}
}

QString query = "SELECT " + columns.join( QStringLiteral( ", " ) ) + " FROM " + layer->id() + " AS t " + leftJoins.join( QStringLiteral( " " ) );
QString query = "SELECT " + columns.join( QStringLiteral( ", " ) ) + " FROM \"" + layer->id() + "\" AS t " + leftJoins.join( QStringLiteral( " " ) );
def.setQuery( query );

return def;
Expand Down
32 changes: 23 additions & 9 deletions tests/src/python/test_provider_virtual.py
View file
Expand Up @@ -822,7 +822,11 @@ def test_joined_layers_conversion(self):
self.assertEqual(v2.isValid(), True)
v3 = QgsVectorLayer("Point?field=id:integer&field=cname:string", "C", "memory")
self.assertEqual(v3.isValid(), True)
QgsProject.instance().addMapLayers([v1, v2, v3])
tl1 = QgsVectorLayer("NoGeometry?field=id:integer&field=e_id:integer&field=0name:string", "D", "memory")
self.assertEqual(tl1.isValid(), True)
tl2 = QgsVectorLayer("NoGeometry?field=id:integer&field=ena me:string", "E", "memory")
self.assertEqual(tl2.isValid(), True)
QgsProject.instance().addMapLayers([v1, v2, v3, tl1, tl2])
joinInfo = QgsVectorLayerJoinInfo()
joinInfo.setTargetFieldName("b_id")
joinInfo.setJoinLayer(v2)
Expand All @@ -832,15 +836,15 @@ def test_joined_layers_conversion(self):
self.assertEqual(len(v1.fields()), 6)

df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(df.query(), 'SELECT t.rowid AS uid, t.id, t.b_id, t.c_id, t.name, j1.bname AS B_bname, j1.bfield AS B_bfield FROM {} AS t LEFT JOIN {} AS j1 ON t."b_id"=j1."id"'.format(v1.id(), v2.id()))
self.assertEqual(df.query(), 'SELECT t.geometry, t.rowid AS uid, t."id", t."b_id", t."c_id", t."name", j1."bname" AS "B_bname", j1."bfield" AS "B_bfield" FROM "{}" AS t LEFT JOIN "{}" AS j1 ON t."b_id"=j1."id"'.format(v1.id(), v2.id()))

# with a field subset
v1.removeJoin(v2.id())
joinInfo.setJoinFieldNamesSubset(["bname"])
v1.addJoin(joinInfo)
self.assertEqual(len(v1.fields()), 5)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(df.query(), 'SELECT t.rowid AS uid, t.id, t.b_id, t.c_id, t.name, j1.bname AS B_bname FROM {} AS t LEFT JOIN {} AS j1 ON t."b_id"=j1."id"'.format(v1.id(), v2.id()))
self.assertEqual(df.query(), 'SELECT t.geometry, t.rowid AS uid, t."id", t."b_id", t."c_id", t."name", j1."bname" AS "B_bname" FROM "{}" AS t LEFT JOIN "{}" AS j1 ON t."b_id"=j1."id"'.format(v1.id(), v2.id()))
joinInfo.setJoinFieldNamesSubset(None)

# add a table prefix to the join
Expand All @@ -849,7 +853,7 @@ def test_joined_layers_conversion(self):
v1.addJoin(joinInfo)
self.assertEqual(len(v1.fields()), 6)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(df.query(), 'SELECT t.rowid AS uid, t.id, t.b_id, t.c_id, t.name, j1.bname AS BB_bname, j1.bfield AS BB_bfield FROM {} AS t LEFT JOIN {} AS j1 ON t."b_id"=j1."id"'.format(v1.id(), v2.id()))
self.assertEqual(df.query(), 'SELECT t.geometry, t.rowid AS uid, t."id", t."b_id", t."c_id", t."name", j1."bname" AS "BB_bname", j1."bfield" AS "BB_bfield" FROM "{}" AS t LEFT JOIN "{}" AS j1 ON t."b_id"=j1."id"'.format(v1.id(), v2.id()))
joinInfo.setPrefix("")
v1.removeJoin(v2.id())
v1.addJoin(joinInfo)
Expand All @@ -862,11 +866,21 @@ def test_joined_layers_conversion(self):
v1.addJoin(joinInfo2)
self.assertEqual(len(v1.fields()), 7)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(v1)
self.assertEqual(df.query(), ('SELECT t.rowid AS uid, t.id, t.b_id, t.c_id, t.name, j1.bname AS B_bname, j1.bfield AS B_bfield, j2.cname AS C_cname FROM {} AS t ' +
'LEFT JOIN {} AS j1 ON t."b_id"=j1."id" ' +
'LEFT JOIN {} AS j2 ON t."c_id"=j2."id"').format(v1.id(), v2.id(), v3.id()))

QgsProject.instance().removeMapLayers([v1.id(), v2.id(), v3.id()])
self.assertEqual(df.query(), ('SELECT t.geometry, t.rowid AS uid, t."id", t."b_id", t."c_id", t."name", j1."bname" AS "B_bname", j1."bfield" AS "B_bfield", j2."cname" AS "C_cname" FROM "{}" AS t ' +
'LEFT JOIN "{}" AS j1 ON t."b_id"=j1."id" ' +
'LEFT JOIN "{}" AS j2 ON t."c_id"=j2."id"').format(v1.id(), v2.id(), v3.id()))

# test NoGeometry joined layers with field names starting with a digit or containing white spaces
joinInfo3 = QgsVectorLayerJoinInfo()
joinInfo3.setTargetFieldName("e_id")
joinInfo3.setJoinLayer(tl2)
joinInfo3.setJoinFieldName("id")
tl1.addJoin(joinInfo3)
self.assertEqual(len(tl1.fields()), 4)
df = QgsVirtualLayerDefinitionUtils.fromJoinedLayer(tl1)
self.assertEqual(df.query(), 'SELECT t.rowid AS uid, t."id", t."e_id", t."0name", j1."ena me" AS "E_ena me" FROM "{}" AS t LEFT JOIN "{}" AS j1 ON t."e_id"=j1."id"'.format(tl1.id(), tl2.id()))

QgsProject.instance().removeMapLayers([v1.id(), v2.id(), v3.id(), tl1.id(), tl2.id()])

def testFieldsWithSpecialCharacters(self):
ml = QgsVectorLayer("Point?srid=EPSG:4326&field=123:int", "mem_with_nontext_fieldnames", "memory")
Expand Down

0 comments on commit a80117b

Please sign in to comment.