How to create a QGIS Virtual Layer that retains the source field types?

Question

In a previous question that I posted, the answer that I accepted involved creating a Virtual Layer from a source layer. The query used to create the Virtual Layer was:

select *  
from my_layer  
order by field_a 

Although the query works as intended, some of the source field types are converted to different types in the Virtual Layer. Specifically:

• Integer (32 bit) are converted to Integer (64 bit)
• Date and Date&Time are converted to Text (string)
• Boolean are converted to Integer (64 bit)

I use the Refactor Fields to get the field types back to their original, but this involves another step that I would prefer to eliminate.

Question: How to create a Virtual Layer that retains the source field types?

Answer 1

Virtual layers lack field type support for Date, Time, and Boolean. See what Virtual layer context help says:

where type can be any of int, real or text

See also here: https://docs.qgis.org/latest/en/docs/user_manual/managing_data_source/create_layers.html#special-comments

Answer 2

I don't know a possibility to define the resulting field-type of a virtual layer, but as workaround You could create the virtual layer with a query that contains only the absolute necessary fields and add all other fields via join from the original layers.

That has multiple advantages:

1. simple queries for the virtual layer
2. attribute-tables and dialogs, which show clear, from where the fields come
3. correct (and all) field-types from original layers

So create virtual_lyr with something like

select 
id_field
from my_layer  
order by field_a

And then join all other data with layer-properties-dialogue or via Python:

join_info = QgsVectorLayerJoinInfo()
join_info.setJoinLayer(my_layer)
join_info.setJoinFieldName(id_field)
join_info.setTargetFieldName(id_field)
join_info.setUsingMemoryCache(True)
virtual_lyr.addJoin(join_info)

One possible disadvantage:

The resulting layers with simple query and join could be less performant than the with complex queries without join, I haven't tested this so far.

hth Ludwig