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.

Example

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)

Model example

Setup of Execute SQL

Example_setup

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 %].

• gisnside: For info, I haven't managed to reproduce it for now, maybe i did something wrong. I'll check this ASAP and mark as answered if I get it right or ask you more info :) thanks to you I understood the reason of the Expression area (syntax seems like in the layout part)