Alabaster Snowball, the elf, was searching for a gift for the person he had drawn on the North Pole’s Secret Santa. He had the great honour to draw Santa! What to give for the one who gives everyone’s presents? So he was searching on the internet for some keywords he knew Santa would like:

automatic letter reader

resistant boots

red sleigh accessories

red cap that does not fly off in wind

red jacket

red

Wait a minute! Is Red going to :api<2> ?!! Alabaster Snowball has already read about that ORM for Raku . But it seems this new :api<2> version is taking it to the next level.

That’s it! I’ll give Santa a Red:api<2> PoC as gift! I know he has been playing with Raku, and I think it would be great to change all that collection of SQL strings on the NiceList model to a well made set of ORM classes.

Reading the documentation, Snowball learned that it would be very easy to create it’s first model:

use Red:api<2>; unit model Child; has UInt $!id is id; has Str $.name is column; has Str $.country is column;

He started using Red:api<2> and creating a new model that represents a table child with 3 columns ( id , name and country ). As easy as that.

Alabaster could now just connect into a database, create the table, and start inserting children:

use Red:api<2>; red-defaults default => database "SQLite"; Child.^create-table: :unless-exists; Child.^create: :name<Fernanda>, :country<England> ; Child.^create: :name<Sophia>, :country<England> ; Child.^create: :name<Dudu>, :country<Scotland>; Child.^create: :name<Rafinha>, :country<Scotland>; Child.^create: :name<Maricota>, :country<Brazil> ; Child.^create: :name<Lulu>, :country<Brazil> ;

And to list all children created:

.say for Child.^all.sort: *.name;

And that would run this query:

SELECT child.id, child.name, child.country FROM child ORDER BY child.name

And prints:

Child.new(name => "Dudu", country => "Scotland") Child.new(name => "Fernanda", country => "England") Child.new(name => "Lulu", country => "Brazil") Child.new(name => "Maricota", country => "Brazil") Child.new(name => "Rafinha", country => "Scotland") Child.new(name => "Sophia", country => "England")

If it’s needed, Santa can classify children by country:

my %by-country := Child.^all.classify: *.country;

And to discover what countries have children registered:

say %by-country.keys;

That would run:

SELECT DISTINCT(child.country) as "data_1" FROM child

And that would return:

(England Scotland Brazil)

If he needs to get all children from England:

.say for %by-country<England>;

That would run:

SELECT child.id, child.name, child.country FROM child WHERE child.country = ? -- BIND: ["England"]

That would return:

Child.new(name => "Fernanda", country => "England") Child.new(name => "Sophia", country => "England")

It’s working great! How about storing the gifts? Is there a way to store what a child asked by year?

# Gift.pm6 use Red:api<2>; unit model Gift; has UInt $!id is serial; has Str $.name is column{ :unique }; has @.asked-by-year is relationship( *.gift-id, :model<ChildAskedOnYear> ); method child-asked-on-year(UInt $year = Date.today.year) { @!asked-by-year.grep(*.year == $year) } method asked-by(UInt $year) { self.child-asked-on-year(|($_ with $year)).map: *.child }

# Child.pm6 use Red:api<2>; unit model Child; has UInt $!id is id; has Str $.name is column; has Str $.country is column; has @.asked-by-year is relationship( *.child-id, :model<ChildAskedOnYear> ); method asked(UInt $year = Date.today.year) { @!asked-by-year.grep: *.year == $year }

# ChildAskedOnYear.pm6 use Red:api<2>; unit model ChildAskedOnYear; has UInt $!id is serial; has UInt $.year is column = Date.today.year; has UInt $!child-id is referencing(*.id, :model<Child>); has UInt $!gift-id is referencing(*.id, :model<Gift>); has $.child is relationship( *.child-id, :model<Child> ); has $.gift is relationship( *.gift-id, :model<Gift> );

Alabaster Snowball thought that way he could get all information he would need. Creating new gifts is easy!

for <doll ball car pokemon> -> $name { Gift.^create: :$name; }

How about searching? Alabaster Snowball writes a new line:

.say for Gift.^all

And it returns all the gifts. But what if we want only the gifts that end with “ll”?

.say for Gift.^all.grep: *.name.ends-with: "ll"

That will run a query like:

SELECT gift.id, gift.name FROM gift WHERE gift.name like '%ll'

Snowball wondered if it is possible to find what a child has asked:

.say for Child.^find(:name<Fernanda>).asked.map: *.gift

That runs:

SELECT child_asked_on_year_gift.id, child_asked_on_year_gift.name FROM child_asked_on_year LEFT JOIN gift as child_asked_on_year_gift ON child_asked_on_year.gift_id = child_asked_on_year_gift.id WHERE child_asked_on_year.child_id = ? AND child_asked_on_year.year = 2019

And what if we want to know the last year’s gift?

.say for Child.^find(:name<Fernanda>).asked(2018).map: *.gift

SELECT child_asked_on_year_gift.id, child_asked_on_year_gift.name FROM child_asked_on_year LEFT JOIN gift as child_asked_on_year_gift ON child_asked_on_year.gift_id = child_asked_on_year_gift.id WHERE child_asked_on_year.child_id = ? AND child_asked_on_year.year = '2018'

How do we know how many of each gift should be built?

say ChildAskedOnYear.^all.map(*.gift.name).Bag

SELECT child_asked_on_year_gift.name as "data_1", COUNT('*') as "data_2" FROM child_asked_on_year LEFT JOIN gift as child_asked_on_year_gift ON child_asked_on_year.gift_id = child_asked_on_year_gift.id GROUP BY child_asked_on_year_gift.name

The documentation for Red is on https://fco.github.io/Red/ and some examples used here can be found on https://github.com/FCO/Red/blob/join/examples/xmas/index.p6