join.p6

use Red;
my $*RED-DB = database "SQLite";

model Bla { ... }
model Ble { ... }
model Bli { ... }

model Bla {
   has UInt $.id is serial;
   has UInt $!ble-id is referencing( *.id, :model<Ble>);
   has      $.ble    is relationship( *.ble-id, :model<Ble> );
}

model Ble {
   has UInt $.id    is serial;
   has UInt $!bli-id is referencing( *.id, :model<Bli> );
   has      $.bli    is relationship( *.bli-id, :model<Bli> );
}

model Bli {
   has UInt $.id    is serial;
   has Int  $.value is column;
}

Bla.^create-table; Ble.^create-table; Bli.^create-table;
my $*RED-DEBUG = True;

say Bla.^all.grep: { .ble.bli.value == 42 }

select.sql

SELECT
   bla.id , bla.ble_id as "ble-id"
FROM
   bla
   JOIN ble as bla_ble ON bla.ble_id = bla_ble.id	
   JOIN bli as bla_ble_bli ON bla_ble.bli_id = bla_ble_bli.id	
WHERE
   bla_ble_bli.value = 42