Day 21: Searching for a Red gift

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