Virtual layer accessing virtual field

Question

I have a layer my_layer containing a virtual field virtual_1. Next step I want to create a virtual layer, which list only unique values (as a bonus also dissolves the geometries by unique values).

The following query for the virtual layer doesn't work with virtual fields:

SELECT DISTINCT virtual_1 from my_layer

How could the desired outcome be achieved?

Some notes/ideas:

• I need an dynamic solution, as the data in my_layer gets updated reguarly. So unfortunately the Vector>Analysis>Unique Values isn't an option. Also Dissolving by virtual field is therefore not an option.
• Would it work to generate the virtual field inside the query itself? How could an expression of the field editor evaluated in the virtual-field-query?

Answer 1

If you created a virtual field (Virtual_1) using a CASE statement you may use the case statement in your SQL query

In the below exemple I create a virtual field (Virtual_1) using the following :

CASE
    WHEN "continent" IN ( 'North America' , 'South America' )
        THEN 'On the Left'
    WHEN "continent" IN ( 'Africa' , 'Asia' , 'Europe' )
        THEN 'On the Right'
    WHEN "continent" =  'Antarctica' 
        THEN 'A the Bottom'
    ELSE 'Other'
END

To use this Virtual field in a Virtual layer I could just use the following SQL statement where the virtual field is replaced by the CASE expression:

SELECT 
DISTINCT (CASE
            WHEN "continent" IN ( 'North America' , 'South America' )
                THEN 'On the Left'
            WHEN "continent" IN ( 'Africa' , 'Asia' , 'Europe' )
                THEN 'On the Right'
            WHEN "continent" =  'Antarctica' 
                THEN 'A the Bottom'
            ELSE 'Other'
        END) AS Virtual_1 
FROM Admin_0_polygons

In the below picture you can see the original table with the virtual field and the Virtual layer next to it.

The same principle will work with any fonction that SQLite/SpatiaLite engine could evaluate including on the geometry if needed