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?