La séptima vida

...o el gato así lo espera/teme

Mapping relationships with DBIx::Mint

Relational databases are all about relationships, and they can be quite promiscuous. One-to-one, one-to-many, and even many-to-many relationships are frequent when you get a close look into database schemas. If you don't think they are sexy, wait until you meet DBIx::Mint and its ability to relate different classes.

We shall continue with the example from the introductory article. There, we talked about how a customer can place orders and how each order must be from a client.

Let's represent that relationship with DBIx::Mint. Because an order must come from one client while a client may place many orders, there is a one-to-many relationship between the client and order classes. And what about clients and items? A client may order many different items, and an item may be bought by many different customers. Here we are talking about a many-to-many relationship.

Let's take a deep breath and take things one-by-one.

One-to-many relationships

We start by writing a test for one of our one-to-many relationships, that between clients and orders (and vice-versa):

# Fetch a client
my $client = NanoERP::Client->find({ name => 'EndeMox'});
isa_ok $client, 'NanoERP::Client';

# Get the different orders from that client
my @orders = $client->get_orders;
foreach my $obj (@orders) {
    isa_ok $order, 'NanoERP::Order';
    is $order->get_customer->name, 'EndeMox',
        'The orders from a client point indeed to the client';
}

The interesting methods here are $client->get_orders and $order->get_customer. Note that they are really the two ends of the same rope; on the client side, we get the (many) orders placed by them and, on the order side, we get the (one) customer who placed the order. These methods are built from the following declarations in our schema:

my $s = DBIx::Mint->instance->schema;

### Class definitions
$s->add_class(
    class   => 'NanoERP::Client',
    table   => 'clients',
    pk      => 'id',
    auto_pk => 1
);

$s->add_class(
    class   => 'NanoERP::Order',
    table   => 'orders',
    pk      => 'id',
    auto_pk => 1
);

### Relationships
$s->one_to_many(
    conditions     =>
        ['NanoERP::Client', {id => 'client'}, 'NanoERP::Order'],
    method         => 'get_orders',
    inverse_method => 'get_customer',
    insert_into    => 'add_order'
);

Class definitions were introduced in the first article of this series.

At the bottom of the above schema definition you find the one_to_many relationship. In our case, the order objects have a client attribute where they keep the id of their client. In other words, the conditions for two objects to be related is that their respective id and client attributes are equal.

Next, DBIx::Mint will create the method get_orders in the client class, which will return an array of orders. In a symmetric way, it will add get_customer to order objects. This method will return the customer who placed the order in the first place.

The relationship definition will also create a method that allows customers to place orders directly. This method will insert an order into the database and it will fill automatically the client column:

# Add an order for a product
$order_id = $client->add_order({
    item     => 'PRO-001',
    quantity => 3,
    due      => '2015-03-27'
});

The one_to_many declaration just gave us three different methods in the two related classes. Not bad!

Many-to-many relationships

It would also be interesting to get the list of objects ordered by a given client without having to go through the list of orders. In the same way, we could interrogate a product to see what customers have bought it. This is a many-to-many relationship, as customers may buy many items and items may have many customers. As it happens, the declaration is about the same:

$s->many_to_many(
    conditions     => [
        'NanoERP::Client', {id   => 'client'   },
        'NanoERP::Order',  {item => 'reference'},
        'NanoERP::Item'
    ],
    method         => 'get_items',
    inverse_method => 'get_customers'
);

And it is used like this:

# Fetch the list of items ordered by $client
my @items = $client->get_items;

my @clients = $item->get_customers;

These methods return the full list of blessed objects of the corresponding class. But, what if there are just too many of them? We will defer that discussion for a later article (but go ahead and read the documentation for DBIx::Mint::Schema in MetaCPAN)

Spoiler

Both one_to_many and many_to_many are created using the more powerful add_relationship. This later method allows you to map one-to-many or many-to-many relationships as well, but it will also return the resulting data as a result set object, as an iterator, or as plain SQL. We will defer its discussion until we see DBIx::Mint::ResultSet in the next article of this series.

Conclusion

Relationships between tables in a database are mapped into methods on the respective classes. The declaration of these relationship is quite simple, and yet, classes gain several methods from them. We have done both the table-to-class and relationship mappings in a schema file. To learn more and see the reference documentation of this part of DBIx::Mint, you can go to MetaCPAN.