1

I'm trying to do something like this:

select *
from table_a
inner join table_b
  on table_b.fid = table_a.id
  and table_b.code =  @atlas_featureid

I have tried this using QGIS 2.18.16 and QGIS 3.0 but it doesn't seem to work. Am I missing something? Is there another approach to achieve this?

The alternative I could figure is to add a virtual layer without the last condition and symbolize just the ones that match the @atlas_featureid but this layer has about 100 times more features (≈ 300k) and it takes ages to work with it, it's not a viable solution.

  • I don't think this is possible. – HeikkiVesanto Mar 13 '18 at 11:15
  • What would happen if you don't have the composer open? You might have more luck with a python code updating the virtual layer query (which ends up like and table_b.code = 123 ) – JGH Mar 13 '18 at 11:18
  • @JGH The thing is that I want to generate several maps for every iteration and I have more layers that depend on these iterations. What you suggest would be to create my own atlas application right? – Albert Mar 13 '18 at 11:30
  • not an entire app, but small customizations done by code when the atlas page change – JGH Mar 13 '18 at 11:32
  • @JGH Thank you, I will investigate in this direction. The github link in this question: gis.stackexchange.com/questions/227203/… says: "QgsExpression functions are not supported in an SQL query. Addition of such a feature would be possible (since SQLite virtual table mechanism allows to add/overload functions)." So I guess it is not possible at the moment, it would make a nice feature though – Albert Mar 13 '18 at 12:05
3

You cannot use Expression variables in your virtual Layers directly. Use the Expression function var() instead (i.e. SELECT var('project_crs')).

Unfortunately this won't help in your case, because virtual Layers only allow global and Project scoped Expression variables by now (I hope Hugo Mercier will catch up my feature request https://github.com/qgis/QGIS/issues/35867 to allow more Expression functions in future).

But to solve your problem at the moment, there is a possible workaround using a custom Python Expression function:

@qgsfunction(args='auto', group='Custom')
def getAtlasFeatureId(layout,atlasLayerName,feature,parent):
    features = QgsProject.instance().mapLayersByName(atlasLayerName)[0].getFeatures()
    id = QgsProject.instance().layoutManager().layoutByName(layout).atlas().currentFeatureNumber()
    idx = 0
    for feat in features:
        if idx==id:
            return(feat["id"])
        idx+=1
select *
from table_a
inner join table_b
  on table_b.fid = table_a.id
  and table_b.code =  getAtlasFeatureId('MyLayout','table_b')
| improve this answer | |
2

Another solution, a little bit shorter:

@qgsfunction(args='auto', group='Custom')
def getAtlasFeatureId(layout,feature,parent): 
    return QgsProject.instance().layoutManager().layoutByName(layout).referenceMap().createExpressionContext().variable('atlas_featureid')
select *
from table_a
inner join table_b
  on table_b.fid = table_a.id
  and table_b.code =  getAtlasFeatureId('MyLayout')
| improve this answer | |
  • Thank you Cristoph for your answers, they look like they could be a workaround. I posted this question a while back so I'd have to recover my project and try it out. – Albert Apr 24 at 13:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.