Loading PostGIS layer from SQL query using PyQGIS [duplicate]

Question

With PyQGIS I can load a vector layer from a PostgreSQL/PostGIS database :

uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "myBase", "myUser", "myPassword")
uri.setDataSource("mySchema", "myTable", "geom")
vlayer = iface.addVectorLayer(uri.uri(False), "myLayer", "postgres")

I want to apply a SQL query to load just a part of my features. With the documentation i've read i can add a simple WHERE clause :

uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "myBase", "myUser", "myPassword")
uri.setDataSource("mySchema", "myTable", "geom", "name like 'Example'")
vlayer = iface.addVectorLayer(uri.uri(False), "myLayer", "postgres")

But I'm not able to build a more complex syntax like st_intersects. For example :

uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "myBase", "myUser", "myPassword")
uri.setWkbType(QgsWkbTypes.MultiPolygon)
uri.setKeyColumn(u"idA")
myQuery = """SELECT a.name FROM public.tableA a INNER JOIN public.tableB b ON st_intersects(a.geom, b.geom) WHERE a.name LIKE 'Example' AND b.name LIKE 'Something';"""
uri.setDataSource("mySchema", myQuery, "geom")
vlayer = iface.addVectorLayer(uri.uri(False), "myLayer", "postgres")

How can I use a full SQL query with a QgsDataSourceUri class (maybe with setSql method) ?

Answer 1

A meticulous syntax must be respected, especially escaped quotes, and an id (integer) and a geom must appear in the query. Here is a full example with st_intersects :

uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "myBase", "myUser", "myPassword")
sql = 'SELECT a.idA, a.geom, a.name FROM public.tableA a INNER JOIN public.tableB b ON st_intersects(a.geom, b.geom) WHERE a.name LIKE \'Example\' AND b.name LIKE \'Something\''
uri.setDataSource('', f'({sql})', 'geom', '', 'idA')
vlayer = iface.addVectorLayer(uri.uri(), 'myLayer','postgres')
# Optional    
print (sql)