La séptima vida

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

Querying a database with DBIx::Mint::ResultSet

The first two articles of the series discussed mapping tables to classes and declaring relationships between classes in a schema. Having these mappings allows you to abstract database interactions and works well for creating, updating or deleting records. However, that mechanism is quite limited for data extraction, where you may want to fetch information from different tables using a single SQL statement.

Just as other ORMs for Perl, DBIx::Mint works with result sets. A result set is a collection of records obtained after querying the database for information. These records may result from joining several tables, from aggregating data, from a simple or from a very complex SELECT query.

You can use the DBIx::Mint::ResultSet interface to obtain information from your database. For this, you build a ResultSet object whose function is to build the query. Then, you ask for your results to be returned in any of several convenient formats.

One of the particularities of DBIx::Mint::ResultSet is that it does not use the schema we have previously defined. In fact, you don't need to define any schema, since result sets are not mapped to your classes (though you can bless results into any class you want; see below). Under this optic, DBIx::Mint::ResultSet is closer to the database than the rest of the artifacts in the distribution. The users of DBIx::Mint::ResultSet will be working directly with tables and columns, instead of our class abstractions.

The example

We will continue working with the example from the first article of this series.

Among the different aspects of our manufacturing floor database, the most complex must be that of the bill of materials. Consider that both products and components are in fact items, and their informaiton is kept on the items table. More over, The list of components for a product is kept in another table, bom, which has three columns: parent, child, and quantity. It is in fact a many-to-many relationship, as a product may have many components and each component may be included in many products. The complexity lies in that it is a many-to-many relationship between the table items and itself. This is shown in figure 1.

ER diagram of the bill of materials
ER diagram of the bill of materials. The table bom is in fact a many-to-many relationship between items and itself: A product (parent) may have many components (children), and any component may be included in many products. Components may in fact have children of their own.

Creating a DBIx::Mint::ResultSet object

Suppose we got an order to build a Fritzing keyfob. The last time I ordered some PCBs from Fritzing they kindly sent me a circuit board for their key-fob-duino. I am eager to assemble it, but that's another story. The idea here is that we have the bill of materials for the key-fob-duino in the database and we are ready to extract it using a result set object.

The fist step is to create the result set object. Result sets in DBIx::Mint start from a table; from there, you need to follow the path of table relationships needed for your query. In our example we know the reference of our assembly, the key-fob-duino. This is our starting point. Let's start from the items table:

# Get the list of materials for the Fritzing keyfob
my $rs = DBIx::Mint::ResultSet->new(table => 'items');

From there, we need to go to the bom table and join all the records who keep the key-fob-duino in the parent column:

# Get the list of materials for the Fritzing keyfob
my $rs = DBIx::Mint::ResultSet->new(table => 'items')
    ->inner_join(['bom',   'b'], { 'me.reference' => 'b.parent' });

That last bit requires some explanation.

We are joining the bom table and aliasing it as b. We will get the records where the reference of the initial table (always referred to as "me") matches the column parent from table "b". Aliasing the tables is needed only because we will have two instances of the same table. For most queries aliasing is not required.

So far, we have worked through the parent side of the relationship. The child is just the same:

# Get the list of materials for the Fritzing keyfob
my $rs = DBIx::Mint::ResultSet->new(table => 'items')
    ->inner_join(['bom',   'b'], { 'me.reference' => 'b.parent' })
    ->inner_join(['items', 'c'], { 'c.reference'  => 'b.child'  });

And the difficult part is over.

Extracting data from related tables

Once our result set knows the tables we are joining, it needs to learn the columns we need to fetch and the conditions that records must abide to become part of our results. It SQL parlance, the last bit corresponds to the where clause:

$rs = $rs->select('me.reference|parent', 'me.description|parent_desc',
    'b.quantity', 'c.reference', 'c.description');

$rs = $rs->search({'me.reference' => 'PRO-004'});

The fist statement is perhaps a little cryptic. The select method of the result set object declares the list of columns that we want to fetch. You can call it as many times as you want. The first argument, me.reference|parent, means that we are getting the reference column from the table aliased as me (that is, the items table), and that we will be calling it parent.

Finally, the search method means that, for data to be in our result set, the reference column of the initial table must be equal to PRO-004, which in this case is the reference of our Fritzing key fob.

At this point, our query is ready to be sent to the database. Would you like to inspect the SQL that DBIx::Mint::ResultSet will generate? Call the method select_sql and you shall receive the SQL and the list of data to fill the place holders:

say join "\n-->", $rs->select_sql;

In this example, the above statement says:

SELECT me.reference AS parent, me.description AS parent_desc, 
       b.quantity, c.reference, c.description 
FROM items AS me 
       INNER JOIN bom AS b ON ( me.reference = b.parent ) 
       INNER JOIN items AS c ON ( c.reference = b.child ) 
WHERE ( me.reference = ? )
-->PRO-004

(Yes, I added some line breaks for readability).

Now that we are sure the SQL is what we need, we are ready to fetch some data.

Fetching data

DBIx::Mint::ResultSet provides several options to actually query the database and obtain the data. We can choose to obtain all the records in one go, a single record, or an iterator that won't bring everything into memory at once. This is in contrast with the methods created by one_to_many and many_to_many from DBIx::Mint::Schema, where things are already set-up.

In our case, given that our key fob does not have that many components, we can simply grab them all at once:

# Fetch all components in one go
use Data::Dumper;
my @components = $rs->all;
say Dumper \@components;

which returns what we asked:

$VAR1 = [
          {
            'parent' => 'PRO-004',
            'parent_desc' => 'Fritzing keyfob',
            'reference' => 'CTR-001',
            'quantity' => '1',
            'description' => 'Atmega168P'
          },
          ...
          {
            'parent' => 'PRO-004',
            'parent_desc' => 'Fritzing keyfob',
            'reference' => 'PCB-001',
            'quantity' => '1',
            'description' => 'Platine Fritzing KeyFob'
          }
        ];

But iterators are more interesting. If you request an iterator, DBIx::Mint::ResultSet will return an object with a single method, next. Everytime you call next, you will actually go fetch the following record from the database. This way, you can safely work with queries that return lots of data:

# Fetch components one-by-one... essential for large result sets
my $iter = $rs->as_iterator;

while (my $rec = $iter->next) {
    say join " - ",
        map { $rec->{$_} }
        qw(parent parent_desc quantity reference description);
}

Blessing result sets into a class

In the example above, did you notice that $rec is a hash reference? Well, you can build a class around this result set and have methods that act on the returned data. The only thing you need to do is to bless the returned records into your class:

# Bless resulting records into this class
$rs = $rs->set_target_class('NanoERP::BillOfMats');

The above instruction must be given before we start pulling data from the database.

Conclusion

While DBIx::Mint::ResultSet is closer to the database than the rest of the tools in DBIx::Mint, it provides interesting methods to extract information and allows you to bless the resulting data into a class of your choice. It does not require a database schema or any class definition; you can use it by itself in your data-extraction programs.