dbo.md

Customer model (t/lib/X/Model/Customer.pm6)

use DBO::Model;
unit class X::Model::Customer does DBO::Model['customer', 'X::Row::Customer'];

has @.columns = [
  id => {
    type           => 'integer',
    nullable       => False,
    is-primary-key => True,
    auto-increment => 1,
  },
  name => {
    type           => 'text',
  },
  contact => {
    type => 'text',
  },
  country => {
    type => 'text',
  },
];

has @.relations = [
  orders => { :has-many, :model<Order>, :relate(id => 'customer_id') },
  open_orders => { :has-many, :model<Order>, :relate(id => 'customer_id', '+status' => 'open') },
  completed_orders => { :has-many, :model<Order>, :relate(id => 'customer_id', '+status' => 'closed') },
];

Order model (t/X/Model/Order.pm6)

use DBO::Model;
unit class X::Model::Order does DBO::Model['order', 'X::Row::Order'];

has @.columns = [
  id => {
    type           => 'integer',
    nullable       => False,
    auto_increment => 1,
    is-primary-key => True,
  },
  customer_id => {
    type           => 'integer',
  },
  status => {
    type => 'text',
  },
  order_date => {
    type => 'date',
  },
];

has @.relations = [
  customer => { :has-one, :model<Customer>, :columns<customer_id customer_id> },
];

Usage (t/04.t)

#!/usr/bin/env perl6

use lib 'lib';
use lib 't/lib';
use DBO;
use Test;
use DBO::Test;
use DBIish;

plan 14;

configure-sqlite;

my $cwd = $*CWD;
$*CWD = 't'.IO;

my DBO $d .=new;
my $db     = DBIish.connect('SQLite', database => 'test.sqlite3');

$d.connect(:$db, :options({
  prefix => 'X',
}));

my ($sth, $scratch);
my $customers = $d.model('Customer');
my $orders    = $d.model('Order');

my $c      = $customers.new-row;
$c.name('customer 1');
$c.contact('joe schmoe');
$c.country('usa');
$c.update;

ok $c.orders.count == 0, 'should have no orders in fresh order table';
for 0..^5 {
  my $o = $orders.new-row;
  $o.set-columns(
    status => ($_ < 3) ?? 'closed' !! 'open',
    customer_id => $c.id,
    order_date => time,
  );
  $o.update;
}

ok $c.orders.count == 5, 'should have 5 orders after inserts';

ok $c.open_orders.count == 2, '2/5 orders for customer should be open';
ok $c.completed_orders.count == 3, '3/5 orders for customer should be complete';

$*CWD = $cwd;