Attribute stats query with Execute SQL tool in QGIS Modeler

Question

I'm trying to get a simple temporary table with the surface of different categories of a polygon shapefile, classified by category. My final aim is to hide an SQL query in a tool with optional variables to give it back as a user-friendly model tool.

Problem : i get some error messages and cannot do the task to the end. Is there a syntax error in my parameters ?

Below details of what I do :

This is my test data where I want to get in 2 different row the sum of S and C items areas.

In the database menu, in thr SQL window with virtual layers, i do the request on my layers to check my formula - it works :

SELECT  layer.ID as CAT, SUM(area(layer.geometry)) as SURF FROM layer GROUP BY CAT;

I get back the following table :

CAT SURF
C   139327
S   185247

I would now like to use the modeler to be able to ask the user what layer he wants and what column to use for my GROUP BY. I'm trying to use the "Execute SQL" tool but with no success. Below the model I use (it's really basic)

Setup of Execute SQL

I get the following error :

Préparation de l'algorithme : qgis:executesql_1 Exécution de Exécuter SQL [1/1] Paramètres en entrée: { INPUT_DATASOURCES: ['//.../surf.shp'], INPUT_GEOMETRY_CRS: None, INPUT_GEOMETRY_FIELD: 'geometry', INPUT_GEOMETRY_TYPE: 4, INPUT_QUERY: 'SELECT layer.ID as CAT, SUM(area(layer.geometry)) as SURF FROM layer GROUP BY CAT;', INPUT_UID_FIELD: 'TYPE', OUTPUT: 'memory:' } Traceback (most recent call last): File "C:/OSGEO4~1/apps/qgis-ltr/./python/plugins\processing\algs\qgis\ExecuteSQL.py", line 169, in processAlgorithm raise QgsProcessingException(vLayer.dataProvider().error().message()) _core.QgsProcessingException:

virtual: Referenced table layer in query not found!

Answer 1

In the Execute SQL window (Exécuter SQL), you have to check the layer parameter in the Add additionnal datas (Ajouter des données additionnelles), select no geometry for the ouput geometry type and use the following query :

SELECT
  [% @id %] as CAT,
  SUM(area(input1.geometry)) as SURF
FROM
  input1
GROUP BY
  CAT

It works but I didn't manage to use the ID parameter.

The ID parameter can be called in the query by [% @id %].