How to prefetch @. relationships?

[FCO]

[FCO]

I think the biggest problem is that the query will return multiple lines for each object... out or order...

[FCO]

Maybe:

model Book {
   has UInt $!id        is serial;
   has Str  $.name      is column;
   has UInt $!author-id is referencing(*.id, :model<Person>);
   has      $.author    is relationship(*.author-id, :model<Person>);
}

model Person {
   has UInt $!id    is serial;
   has Str  $.name  is column;
   has      @.books is relationship(*.author-id, :model<Book>, :prefetch);
}

for Person.^all.grep: *.books.elems > 0 -> $author {
   say “{ $author.name }: { $author.books.join: “, “ }”
}

Should it run:

SELECT person.id, person.name, book.id as ‘book__id’, book.name as ‘book__name’, book.author_id as ‘book__author_id’ FROM person JOIN book ON person.id = book.author_id ORDER BY person.id

And on Seq, loop generating and accumulating Book objects while Person.id is the same, and when it changes, create the Person obj using the accumulated objects?

[FCO]

I'm working on it now, and for now I have this:

➜  Red git:(master) ✗ raku -I. -MRed -e '

model Bla { has $.id is serial; has $.value is column; has @.bles is relationship(*.bla-id, :model<Ble>, :optional, :prefetch) }
model Ble { has $.id is serial; has $.value is column; has $.bla-id is referencing(*.id, :model(Bla)) }
my $*RED-DB = database "SQLite";
Bla.^create-table; Ble.^create-table; Bla.^create(:value(<test1 test2>.pick)) xx 10; Ble.^create(:value(<t1 t2>.pick), :bla-id((1 .. 10).pick)) xx 20;
my $*RED-DEBUG = True;

.say for Bla.^all

'
SQL : SELECT
   "bla".id , "bla".value , "bla_bles".id as "bla_bles.id", "bla_bles".value as "bla_bles.value", "bla_bles".bla_id as "bla_bles.bla-id"
FROM
   "bla"
    LEFT JOIN "ble" as bla_bles ON "bla_bles".bla_id = "bla".id
BIND: []
Bla.new(id => 1, value => "test1")
Bla.new(id => 1, value => "test1")
Bla.new(id => 1, value => "test1")
Bla.new(id => 2, value => "test1")
Bla.new(id => 2, value => "test1")
Bla.new(id => 3, value => "test2")
Bla.new(id => 3, value => "test2")
Bla.new(id => 3, value => "test2")
Bla.new(id => 3, value => "test2")
Bla.new(id => 3, value => "test2")
Bla.new(id => 4, value => "test2")
Bla.new(id => 5, value => "test1")
Bla.new(id => 6, value => "test2")
Bla.new(id => 6, value => "test2")
Bla.new(id => 7, value => "test2")
Bla.new(id => 7, value => "test2")
Bla.new(id => 7, value => "test2")
Bla.new(id => 8, value => "test2")
Bla.new(id => 9, value => "test2")
Bla.new(id => 9, value => "test2")
Bla.new(id => 10, value => "test1")
Bla.new(id => 10, value => "test1")
Bla.new(id => 10, value => "test1")

it's not grouping the prefetches inside the object and returning an object a single time yet. But I was thinking... we have 2 possibilities here, the first one is doing the query as it is above and then iterate over all results to to create each object with all preaches inside of it. I was thinking on iterating until we find a different object, but we have no control over ORDER BY, so it may be on the wrong order. Then I thought on a different possibility, the query, instead, could be something like this:

➜  Red git:(master) ✗ sqlite3 test.db
SQLite version 3.37.0 2021-12-09 01:34:53
Enter ".help" for usage hints.
sqlite> SELECT
   ...>    "bla".id , "bla".value , "bla_bles".json as "bla_bles"
   ...> FROM
   ...>    "bla"
   ...>     LEFT JOIN (SELECT bla_id, json_group_array(json_object('id', id, 'value', value, 'bla_id', bla_id)) as json FROM ble GROUP BY bla_id) as bla_bles ON "bla_bles".bla_id = "bla".id
   ...> ;
1|test1|[{"id":20,"value":"t1","bla_id":"1"}]
2|test1|[{"id":2,"value":"t1","bla_id":"2"},{"id":11,"value":"t1","bla_id":"2"}]
3|test1|[{"id":7,"value":"t2","bla_id":"3"},{"id":9,"value":"t1","bla_id":"3"},{"id":12,"value":"t1","bla_id":"3"},{"id":13,"value":"t1","bla_id":"3"},{"id":19,"value":"t2","bla_id":"3"}]
4|test1|
5|test2|[{"id":5,"value":"t2","bla_id":"5"},{"id":14,"value":"t2","bla_id":"5"}]
6|test1|[{"id":1,"value":"t1","bla_id":"6"}]
7|test1|[{"id":3,"value":"t2","bla_id":"7"}]
8|test1|[{"id":6,"value":"t2","bla_id":"8"},{"id":8,"value":"t1","bla_id":"8"},{"id":15,"value":"t2","bla_id":"8"}]
9|test1|[{"id":4,"value":"t1","bla_id":"9"},{"id":10,"value":"t2","bla_id":"9"},{"id":16,"value":"t1","bla_id":"9"},{"id":18,"value":"t2","bla_id":"9"}]
10|test1|[{"id":17,"value":"t1","bla_id":"10"}]
sqlite>

And it would be much easier to create each object and its prefetches. But much harder to only select the needed prefetch rows.

Any idea/suggestion here?

[FCO]

I just committed a new branch (to-many-prefetch) (https://github.com/FCO/Red/pull/563) with a PoC for SQLite doing prefetch the 2nd way. I'm testing it with:

➜  Red git:(to-many-prefetch) ✗ raku -I. -MRed -e '

model Bla { has $.id is serial; has $.value is column; has @.bles is relationship(*.bla-id, :model<Ble>, :prefetch) }
model Ble { has $.id is serial; has $.value is column; has $.bla-id is referencing(*.id, :model(Bla)) }
my $*RED-DB = database "SQLite";
Bla.^create-table; Ble.^create-table; Bla.^create(:value(<test1 test2>.pick)) xx 5; Ble.^create(:value(<t1 t2>.pick), :bla-id((1 .. 10).pick)) xx 20;
my $*RED-DEBUG = True; my $*RED-DEBUG-RESULT = True;

for Bla.^all { .say for .bles }

'
SQL : SELECT
   "bla".id , "bla".value , "bla_bles".json as "bla_bles"
FROM
   "bla"
    LEFT JOIN (
       SELECT
           bla_id,
           json_group_array(json_object('id', "ble".id, 'value', "ble".value, 'bla-id', "ble".bla_id)) as json
       FROM
           "ble"
       GROUP BY
           bla_id
   )
    as bla_bles ON "bla_bles".bla_id = "bla".id
BIND: []
Ble.new(id => 8, value => "t2", bla-id => "1")
Ble.new(id => 11, value => "t2", bla-id => "1")
Ble.new(id => 14, value => "t1", bla-id => "1")
Ble.new(id => 15, value => "t1", bla-id => "1")
Ble.new(id => 20, value => "t1", bla-id => "1")
Ble.new(id => 1, value => "t1", bla-id => "2")
Ble.new(id => 19, value => "t1", bla-id => "2")
Ble.new(id => 3, value => "t1", bla-id => "3")
Ble.new(id => 17, value => "t2", bla-id => "3")
Ble.new(id => 2, value => "t2", bla-id => "4")
Ble.new(id => 6, value => "t2", bla-id => "5")

It's still in the beginning, but I seems very interesting... Opinions?

[FCO]

Maybe that may be an option for for #25

[FCO]

Now we can set the prefetches on a ResultSeq using the prefetch method:

➜  Red git:(to-many-prefetch) ✗ raku -I. -MRed -e '
my $*RED-FALLBACK = False;
model Bla { has $.id is serial; has $.value is column; has @.bles is relationship(*.bla-id, :model<Ble>) }
model Ble { has $.id is serial; has $.value is column; has $.bla-id is referencing(*.id, :model(Bla)) }
my $*RED-DB = database "SQLite";
Bla.^create-table; Ble.^create-table; Bla.^create(:value(<test1 test2>.pick)) xx 5; Ble.^create(:value(<t1 t2>.pick), :bla-id((1 .. 10).pick)) xx 20;
my $*RED-DEBUG = True; my $*RED-DEBUG-RESULT = True;

say .gist, "\n", .bles>>.gist.join("\n").indent: 4 for Bla.^all.prefetch: "bles"

'
SQL : SELECT
   "bla".id , "bla".value , "bla_bles".json as "bla_bles"
FROM
   "bla"
    LEFT JOIN (
       SELECT
           bla_id,
           json_group_array(json_object('id', "ble".id, 'value', "ble".value, 'bla-id', "ble".bla_id)) as json
       FROM
           "ble"
       GROUP BY
           bla_id
   )
    as bla_bles ON "bla_bles".bla_id = "bla".id
BIND: []
Bla.new(id => 1, value => "test2")
    Ble.new(id => 12, value => "t2", bla-id => "1")
    Ble.new(id => 15, value => "t1", bla-id => "1")
Bla.new(id => 2, value => "test2")
    Ble.new(id => 3, value => "t2", bla-id => "2")
    Ble.new(id => 19, value => "t2", bla-id => "2")
Bla.new(id => 3, value => "test1")
    Ble.new(id => 10, value => "t1", bla-id => "3")
Bla.new(id => 4, value => "test1")
    Ble.new(id => 2, value => "t1", bla-id => "4")
    Ble.new(id => 4, value => "t1", bla-id => "4")
    Ble.new(id => 6, value => "t1", bla-id => "4")
    Ble.new(id => 17, value => "t2", bla-id => "4")
    Ble.new(id => 20, value => "t1", bla-id => "4")
Bla.new(id => 5, value => "test2")
    Ble.new(id => 16, value => "t2", bla-id => "5")

[FCO]

(not recursive, yet...)

[FCO]

Pg:

➜  Red git:(to-many-prefetch) ✗ raku -I. -MRed -e '
my $*RED-FALLBACK = False;
model Bla { has $.id is serial; has $.value is column; has @.bles is relationship(*.bla-id, :model<Ble>) }
model Ble { has $.id is serial; has $.value is column; has UInt $.bla-id is referencing(*.id, :model(Bla)) }
my $*RED-DB = database "Pg";
schema(Bla, Ble).drop.create; Bla.^create(:value(<test1 test2>.pick)) xx 5; Ble.^create(:value(<t1 t2>.pick), :bla-id((1 .. 5).pick)) xx 20;
my $*RED-DEBUG = True; my $*RED-DEBUG-RESULT = True;

say .gist, "\n", .bles>>.gist.join("\n").indent: 4 for Bla.^all.prefetch: "bles"

'
SQL : SELECT
   "bla".id , "bla".value , "bla_bles".json as "bla_bles"
FROM
   "bla"
    LEFT JOIN (
       SELECT
           bla_id,
           json_agg(json_build_object('id', "ble".id, 'value', "ble".value, 'bla-id', "ble".bla_id)) as json

       FROM
           "ble"
       GROUP BY
           bla_id
   )
    as bla_bles ON "bla_bles".bla_id = "bla".id
BIND: []
Bla.new(id => 1, value => "test1")
    Ble.new(id => 19, value => "t1", bla-id => 1)
Bla.new(id => 2, value => "test2")
    Ble.new(id => 10, value => "t2", bla-id => 2)
    Ble.new(id => 20, value => "t2", bla-id => 2)
Bla.new(id => 3, value => "test1")
    Ble.new(id => 8, value => "t1", bla-id => 3)
    Ble.new(id => 12, value => "t2", bla-id => 3)
    Ble.new(id => 14, value => "t1", bla-id => 3)
    Ble.new(id => 16, value => "t2", bla-id => 3)
    Ble.new(id => 17, value => "t1", bla-id => 3)
    Ble.new(id => 18, value => "t1", bla-id => 3)
Bla.new(id => 4, value => "test1")
    Ble.new(id => 1, value => "t1", bla-id => 4)
    Ble.new(id => 2, value => "t1", bla-id => 4)
    Ble.new(id => 4, value => "t2", bla-id => 4)
    Ble.new(id => 9, value => "t1", bla-id => 4)
Bla.new(id => 5, value => "test2")
    Ble.new(id => 3, value => "t2", bla-id => 5)
    Ble.new(id => 5, value => "t2", bla-id => 5)
    Ble.new(id => 6, value => "t2", bla-id => 5)
    Ble.new(id => 7, value => "t1", bla-id => 5)
    Ble.new(id => 11, value => "t1", bla-id => 5)
    Ble.new(id => 13, value => "t2", bla-id => 5)
    Ble.new(id => 15, value => "t1", bla-id => 5)

still a bit hardcoded, but I'm going to merge it.