qgis - QgsDataSourceuRI and left outer join

I'm trying to load data from different tables using left outer joins into a QgsVectorLayer. The approach I'm following so far is the one from the developers cookbook here. The underlying database is postgres. The query returns correct data, including a geometry column, however I can't figure out how to get the join inside the query to work with QgsDataSourceUri.setDataSource(). As the table the data come from needs to be specified, is it even possible to do so?

Would approaching the problem using psycopg2 be more suited, and if so, is there a way to create a layer from the result set? The only way I can think of is creating an empty layer and adding each feature individually by looping over the result set. But possibly there is an easier way?

Sadly, using the builtin DB Manager is not an option. The layer has to be added programmatically.

Answer

You have to set up the QgsDataSourceURI like in this post:

uri = QgsDataSourceURI()
uri.setConnection('host', 'port', 'database', 'username', 'password')
sql = 'SELECT * FROM myschema.mytable JOIN othertable USING(id)'
uri.setDataSource('', '(' + sql + ')', 'geom', '', 'id')

vlayer = QgsVectorLayer(uri.uri(), 'LayerName','postgres')
QgsMapLayerRegistry.instance().addMapLayer(vlayer)


print('Is layer valid?', vlayer.isValid())

whereby, it's crucial that the SQL query has to be surrounded by brackets and at least geom and id columns are selected! Another important thing is, to leave the schema string in setDataSource empty and instead use it in the query.

Update for PyQGIS 3:

uri = QgsDataSourceUri()
uri.setConnection('host', 'port', 'database', 'username', 'password')
sql = 'SELECT * FROM myschema.mytable JOIN othertable USING(id)'
uri.setDataSource('', '(' + sql + ')', 'geom', '', 'id')


vlayer = QgsVectorLayer(uri.uri(), 'LayerName','postgres')
QgsProject.instance().addMapLayer(vlayer)

print('Is layer valid?', vlayer.isValid())