Using input parameter as part of SQL query in QGIS model

Question

Sample data for testing available here (the model described below is embedded in the QGIS project file)

In the context of a QGIS model, I am struggling to pass input parameters as part of a SQL query to be used in the Execute SQL tool.

I have this model:

And I use a pre-calculated expression as follows:

select 
    row_number() over() as id, 
    min('||@Fieldcontainingthecentralityscores||') as min, 
    ('||@FieldcontainingtheHierarchylevel||') as hierarchy
from '||@MatrixofFunctions||'
group by hierarchy'

The screenshot:

As you can see, what I do is to try to grab model-specific variables denoted by the @

However when I run the model I get this message:

Prepare algorithm: gdal:executesql_1
Running Execute SQL [1/1]
Input Parameters:
{ DIALECT: 0, INPUT: 'C:/Users/DaSilvaA/Desktop/oromia_MoF/Oromia_(incl_Addis)_MoF_final.gpkg|layername=Oromia_(incl_Addis)_MoF_final', OPTIONS: '', OUTPUT: 'TEMPORARY_OUTPUT', SQL: 'select \r\n\trow_number() over() as id, \r\n\tmin(centrality_score) as min, \r\n\t(Oromia_MoF_oct_2022_c_Hierarchy Level) as hierarchy\r\nfrom \r\ngroup by hierarchy' }
GDAL command:
ogr2ogr C:/Users/DaSilvaA/AppData/Local/Temp/processing_YDUAJo/3d541238b96f4b6cb5d51a547ba2f4ac/OUTPUT.gpkg "C:/Users/DaSilvaA/Desktop/oromia_MoF/Oromia_(incl_Addis)_MoF_final.gpkg" -sql "select 
row_number() over() as id, 
min(centrality_score) as min, 
(Oromia_MoF_oct_2022_c_Hierarchy Level) as hierarchy 
from 
group by hierarchy" -f "GPKG"
GDAL command output:
ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(select ):

incomplete input

I have tried many variations to the expression syntax formulation but in the end the model parameters do not pass into the query.

Does anyone know how to have input parameters interpreted in a SQL query inside a QGIS model?

Answer 1

This seems to be some bug with how the GDAL execute SQL algorithm interprets the layer input.
You could replace it with the QGIS native Execute SQL tool, which has a slightly different way of understanding input names (you can just write 'input1', 'input2' and so on, basically just 'inputN'.

For example, I replaced the tool with your sample data and the query runs fine as long as I define the geometry type as no geometry:

This is the expression:
concat('select row_number() over() as id, min(','"',@Fieldcontainingthecentralityscores,'"',') as min, (','"',@FieldcontainingtheHierarchylevel,'"',') as hierarchy from input1 group by hierarchy')

side note: if you are already wrapping your text in concat, you don't really need all the || signs, you can just use commas. It makes the expression easier to read.

The definition of the model stays pretty much the same.

The output is a point layer, but without any geometry.

Answer 2

I found some kind of solution. I've checked plenty of places with different work around to get the layer name as parameter. Something is wrong with the capture of the layer name. Can't even addes as project parameter.

Here you have two of the tools that others came out with:

• example 1
• example 2

I hope this solution can be also useful for them. Let's go:

1. Don't use input "vectorial layer", use "vectorial objects" instead (hope you can find it easily in the english layout of QGIS).
2. Add the name of the input using the function "layer_property" like here:

Here is the SQL code:

concat('select row_number() over() as id, min(',
'"',@Fieldcontainingthecentralityscores,'"',') as min, (',
'"',@FieldcontainingtheHierarchylevel,'"',') as hierarchy from ', 
layer_property(@input, 'name') ,' group by hierarchy')

This is what worked for me. But there is always a but. The filename must be the same as the name of the layer in the QGIS canvas. If you prefer to rely on the name of the file instead, you have other functions to get the name of the file, like file_name.

Here you have the project with the updated model

Answer 3

Wherever MatrixofFunctions is coming from is apparently undefined, as its substituting a blank for the FROM table name, thus the error. Check the definition and make sure it outputs the desired table name.