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 attribute) 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 will be using a workaround. I am guessing I am missing some minor thing. At the very least this should help others not take so long to get SQL layers working using PyQGIS.