How do I use "execute SQL" from QGIS graphical modeler?

Question

I have a points layer representing road gullies and a non-spatial table of gully inspection data, both stored in a postGIS database. I would like to create a QGIS model to perform some repetitive analysis tasks on the tables, once they are joined on a common id field.

I have tried to use the "join attributes table" tool in the model, but there seems to be limited control over how the join is performed. For one of the analysis tasks I need just the most recent inspection, where there may be 3 or 4 inspections linked to the same gully.

I then found the "execute SQL" tool which can be called by the model. However, I've tried multiple versions of syntax and I just can't seem to get this tool to work for me. Even when executing the tool from outside the model using just

SELECT * FROM gullies

I get an error saying "Unable to open datasource ... with the following drivers..."

Can someone please let me know how I could perform this type of join in the graphical modeler?

Answer 1

I found a work around which give me satisfactory results. It seems a bit long winded though, so if anyone can help me to improve this workflow, I'd appreciate it.

1. Give the non-spatial table of inspections an arbitrary geometry using the 'points layer from table' tool and the id field

2. Since the output from 1 has a geometry and is stored as a temporary shp I can now use the 'execute sql' tool on the output using

   SELECT * FROM "output" ORDER BY date DESC

3. Now use the 'join attributes' table tool to join the ordered inspections to the gullies. Since this tool picks up the first matching record from the inspections table the most recent will be joined.

Hope this helps someone