[QGIS-Developer] Creating a non trivial join/view via PyQGIS in a QgsProcessingAlgorithm

[Gault Drakkor]

I posted this to gis stack exchange. no answers as yet. Below is
essentially the same.

https://gis.stackexchange.com/questions/382141/create-non-trivial-join-view-via-pyqgis-in-a-qgsprocessingalgorithm

In the context of QgsProcessingAlgorithm, what is the best way to take
information from several layers and output based upon that combination of
layers? or: What is wrong with my layer creation?

The overall task/procedure I am trying to accomplish is to take several
QGIS layers combine them with a query, then output some files based upon
that view/join. I have chosen a processing algorithm above a plugin because
it seemed to be better fitting than a plugin.

What I have tried.

Given:

A non-trivial SQL select and join (three tables, one geometry, two
attributes) named *sql_query*. I am mentioning that it is non-trivial to
say that QgsVectorLayerJoinInfo is not something that can be used easily.

A geo package with all the tables used in the query at location:
/path/to/test.gpkg The tables of the above geo package placed into a QGIS
project.

A joined layer can be created via: The DB Manager plugin/tool's
"SQLWindow". The sql_query can be entered into the Query tab, executed,
then "Load as new layer"

Or in the python console:

spec = f'/path/to/test.gpkg|layerid=0|subset={sql_query}'
layer = QgsVectorLayer(spec, 'joined_layer', providerLib='ogr')
QgsProject.instance().addMapLayer(layer)

Or via ogrinfo: ogrinfo -q test.gpkg -sql <sql_query>

In the context of QgsProcessingAlgorithm using same code as what was used
in the python console I get:

ERROR 1: failed to prepare SQL: <echo of sql_query>

In the meantime, I used a workaround. I am guessing I am missing some minor
thing.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20210111/3092be1a/attachment.html>