Create virtual line layer based on points and connectivity list?

Question

I have two layers:

• myPoints: a spatial point layer with an attribute field "ID" (entries are unique).
• connectivityList: a non-spatial attribute table with two fields (idFrom, idTo). Here, the entries are not unique (i.e, a single point from myPoints can appear multiple times in the list, both as start and end point).

My goal is to draw one line for each row in the connectivity list that goes from one point (with ID == idFrom) to another point (with ID == idTo). How can I achieve this?

I would love a solution that just links the layers instead of baking them together, as the point coordinates will change in the future and I would like the lines to automatically adapt. I guess I am looking for a virtual layer, but I could not figure out how to set it up. This question seems very related, but I don't know how to select both of my points and connect them by a line.

Answer 1

General query to relate your layers in a Virtual Layer:

SELECT
  c.*,
  MakeLine(f.geometry, t.geometry) AS geometry
FROM
  "connectivityList" AS c
  JOIN
  "myPoints" AS f
    ON c."idFrom" = f."ID"
  JOIN
  "myPoints" AS t
    ON c."idTo" = t."ID"
;

Answer 2

You can also use an expression using geometry generator.

1. Make an new style layer for the point layer.
2. Choose "Geometry Generator", "LineString/MultiLineString" for "Symbol layer type" and "Geometry type" respectively.
3. Paste the following expression.

   make_line(
     $geometry,
     geometry(
       get_feature(
         @layer,
         'ID',
         attribute(
           get_feature(
             'connectivityList',
             'idFrom',
             ID
           ),
           'idTo'))))
   

Then the lines automatically adapt.