Complex QGIS Relations

Question

I need QGIS to display combo boxes when editing for certain fields in a table, very similar to the functionality provided by the Relations properties in the project settings. It seems this is designed to simply have a value in one table refer to a list of possible values in another table, linked by foreign key.

However, my lookup table contains data for many different feature classes and more than one field between the two tables need to match. Any given field of a feature may or may not have subtype/domain values (not sure the correct terminology), so it's not absolute that the columns in two different features from the same feature class (table) will contain combo boxes for the same columns. It must be determined by simply querying the lookup table to see if that field has entries.

For any given feature, I need to query the lookup table for the following:

1. if the 'featureclass' column of the lookup table has a value for the current featureclass (table/layer name with the vector data)
2. if the 'code' column of the lookup table matches the 'code' column of the feature
3. if the value of the given feature column exists in the lookup table's "field_name" column

I've developed a plugin that does just that. It loops through all of the fields of a selected feature and performs queries against my lookup table for each column. A query looks something like this:

SELECT * 
FROM lookup_table
WHERE code='ABC' 
AND LOWER(field_name)=LOWER('def') 
AND LOWER(featureclass)=LOWER('my_feature_class')

If the result of the query is empty, then I build a simple QLineEdit widget for editing the text directly. If the result has values, I store them in a dictionary like this:

{ 1000: True, 1001: False, -999999: No 'Information' }

So where my feature has the value "1001" in the "def" column, I now build a QComboBox with the options "True", "False", and "No Information", and the combo box stores the integer codes as the data. When a user makes a change and clicks "Save", I manually execute a SQL command to update that record with the new integer value.

Is this functionality possible natively in QGIS using relations?

Answer 1

The solution seems to be using the "Value Relation" widget as the edit widget in the fields tab of the layer properties. I set the layer to my lookup table, the key value to the column containing the encoded integer column, and the value column to the string value column.

Then I had to create a filter expression to match all the columns I require:

lower(code)  = lower(attribute($currentfeature, 'code') )
AND lower(field_name) = 'this_field'
AND lower(featureclass) = 'my_feature_class'

I found it unfortunate that I have to provide static values for the field_name and featureclass. It seems all the variables available in the expression filter reference my lookup table and not my feature table, so I have no way to get at the table name. To use the $currentfeature function, you have to know the name of the column you want to access -- there is no way to get an array of column names.

I have ~60 feature classes and ~120 columns per class. Manually editing every widget one by one isn't very reasonable. I'll have to create a script to generate the XML for layer definition files.

Not sure if anybody else would ever find this useful, but I think it would be great if I could just create one generic filter and choose which fields to apply it to:

lower(code)  = lower(attribute($currentfeature, 'code'))
AND lower(field_name) = @current_field
AND lower(featureclass) = @current_feature_layer_name