multi-column group by in e.g. .classify

[jonathanstowe]

I might be missing something quite obvious but it doesn't seen possible to create a group by with more than one column using .classify.

It's probably implement .reduce as per #61 as .classify doesn't seem the right place to do this.

[FCO]

Sorry, I'm on vacation without my computer... but isn't it just a case of returning a list os columns from the classify block?

[jonathanstowe]

I thought so too, but no it doesn't work,

Enjoy your holiday.

[FCO]

I'm fixing it, I hadn't enough time yet today, but .classify(...).keys is already working on my implementation, now I'm fixing the rest. I hope I have some time tomorrow to play with that.

[FCO]

@jonathanstowe could you see if something like this would solve the problem? I still have to add some tests and resolve how Bag and Set should work,

[jonathanstowe]

I'll take a look in the morning 👍

[jonathanstowe]

I couldn't get it to work, do you have an example?

What I'm really trying to do is illustrated by the SQL:

select a.foo, a.bar, count(*)
from a
join b on b.a_id = a.id
group by a.foo, a.bar;

And I can't for the life of me think of a way to express it.

[FCO]

Now it accepts multiple columns for .classify, but I don't think it will solve your issue... still working on that...

[FCO]

@jonathanstowe I think that might be closer to what you want now (but not there yet). .classify now accepts a :&reduce (it still needs to use what-does-it-do (as the other functions)). Here is an example of usage:

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

model Bla { has $.id is serial; has $.value is column }

my $*RED-DB = database "SQLite";
Bla.^create-table; Bla.^create(:value(<test1 test2>.pick)) xx 10;
my $*RED-DEBUG = True;

say Bla.^all.classify({ .value }, :reduce{ .elems })

'
SQL : SELECT
   DISTINCT("bla".value) as "data_1"
FROM
   "bla"
BIND: []
SQL : SELECT
   count('*') as "data_1"
FROM
   "bla"
WHERE
   "bla".value = ?
LIMIT 1
BIND: ["test1"]
SQL : SELECT
   count('*') as "data_1"
FROM
   "bla"
WHERE
   "bla".value = ?
LIMIT 1
BIND: ["test2"]
{test1 => 7, test2 => 3}

[FCO]

Maybe we could try something like:

Model.^all.classify({ MyClassifiableClass.new: .foo, .bar }).Bag

(https://glot.io/snippets/gh56566qcg)

and create that class specifically for Red. That class could also be used to make map return objects, maybe something like:

say “a: { .a }, b: { .b }” for Model.^all.map: { MyNewClass.new: .a, .b }