Keeping virtual layer up to date

Question

I have a virtual layer in QGIS 2 with a query on a CSV. The CSV gets updated regulary. I need the max/min attributes in the virtual layer to be updated as well. The virtual layer is used as a title in print composer (displayed as attribute table).

The query looks like SELECT "Events between " as from, min(fromdate) as fromdate, " and " as to, max(todate) as todate from "Events_Happening" WHERE invalid = 0 (Events_Happening is the .csv)

How can I achieve this without recreating the virtual layer by hand every time the csv is updated? (Update on QGIS startup would be enough)

Answer 1

I would probably create a macro so that when you open your project, your query can be run immediately. You can create one from the toolbar:

Project > Project Properties > Macros

Then use something like:

from qgis.core import QgsMapLayerRegistry
from qgis.utils import iface

def openProject():
    name = 'virtual_layer'
    query = 'SELECT "Events between " as from, min(fromdate) as fromdate, " and " as to, max(todate) as todate from "Events_Happening" WHERE invalid = 0'
    vLayer = QgsMapLayerRegistry.instance().mapLayersByName(name)[0]
    vLayer.setDataSource("?query={}".format(query), vLayer.name(), "virtual")
    vLayer.triggerRepaint()

def saveProject():
    pass

def closeProject():
    pass

Make sure to save your project. Now the next time you load your project (you don't need to restart QGIS), it should hopefully update the virtual layer.

Answer 2

This method uses a bit of python and the QGIS project openProject() method:

1. Open blank QGIS and select Project (menu)>Project Properties>Macros tab and enable macro

2. Under the openProject() method, create a csv virtual layer (see):

   uri = "/some/path/file.csv?delimiter=%s&xField=%s&yField=%s" % (";", "x", "y")

   vlayer = QgsVectorLayer(uri, "layer name you like", "delimitedtext")

3. Add vector layer to canvas:

   QgsMapLayerRegistry.instance().addMapLayer(vlayer)

4. Set sql subset expression using setSubsetString() method, see QGIS Filter layer by expression

5. Finally save project file (you may also need to enable macros within the QGIS Settings>Options section)

Each time you open this project it should re-create the csv virtual layer and apply the sql filter.