Using joins and prefetch ...

Now let's say you want to display a list of CDs, each with the name of the artist. The following will work fine:

 
  while (my $cd = $rs->next) {
    print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
  }
[download]

There is a problem however. We have searched both the cd and artist tables in our main query, but we have only returned data from the cd table. To get the artist name for any of the CD objects returned, the DBIx::Class manpage will go back to the database:

 
  SELECT artist.* FROM artist WHERE artist.id = ?
[download]

A statement like the one above will run for each and every CD returned by our main query. Five CDs, five extra queries. A hundred CDs, one hundred extra queries! Thankfully, the DBIx::Class manpage has a prefetch attribute to solve this problem. This allows you to fetch results from related tables in advance:

 
  my $rs = $schema->resultset('CD')->search(
    {
      'artist.name' => 'Bob Marley'
    },
    {
      join     => [qw/ artist /],
      order_by => [qw/ artist.name /],
      prefetch => [qw/ artist /] # return artist data too!
    }
  );

  # Equivalent SQL (note SELECT from both "cd" and "artist"):
  # SELECT cd.*, artist.* FROM cd
  # JOIN artist ON cd.artist = artist.id
  # WHERE artist.name = 'Bob Marley'
  # ORDER BY artist.name
[download]

The code to print the CD list remains the same:

 
  while (my $cd = $rs->next) {
    print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
  }
[download]

the DBIx::Class manpage has now prefetched all matching data from the artist table, so no additional SQL statements are executed.

$c->stash->{nodes} = [$c->model('QDB::Nodes')->search(
    {},
    {
      join     => { 'sdh_mux_settings' => 'mux_types' },
      #'+select' => [ qw/ sdh_mux_settings.ip_address mux_types.mux_ty
+pe/ ],
      #'+as'     => [ qw/ ip_address mux_type / ],
      #columns => [qw/foo bar relationship_name.baz 
      #select   => [ 'me.net_id', 'me.net_name', \'COUNT(nodes.node_id
+) as node_count' ], #{ count => 'nodes.node_id' }
      #as       => [qw/ net_id net_name node_count /],
      #group_by => [qw/ me.net_id me.net_name /],
      #prefetch => [qw/ sdh_mux_settings /]
      prefetch => {
        'sdh_mux_settings' => 'mux_types'
      }
    }
      )];
[download]

package Qinfo::QDBSchema::MuxTypes;

use base qw/DBIx::Class/;

__PACKAGE__->load_components(qw/PK::Auto Core/);
__PACKAGE__->table('mux_types');
__PACKAGE__->add_columns(qw/mux_type_id mux_type description/);
__PACKAGE__->set_primary_key('mux_type_id');
__PACKAGE__->has_many('sdh_mux_settings' => 'Qinfo::QDBSchema::SdhMuxS
+ettings','mux_type_id');

1;
[download]

package Qinfo::QDBSchema::Nodes;

use base qw/DBIx::Class/;

__PACKAGE__->load_components(qw/PK::Auto Core/);
__PACKAGE__->table('nodes');
__PACKAGE__->add_columns(qw/net_id node_id node_name node_type_id/);
__PACKAGE__->set_primary_key('node_id');
__PACKAGE__->has_many('sdh_mux_settings' => 'Qinfo::QDBSchema::SdhMuxS
+ettings','node_id');
__PACKAGE__->belongs_to('networks' => 'Qinfo::QDBSchema::Networks','ne
+t_id');

1;
[download]

package Qinfo::QDBSchema::SdhMuxSettings;

use base qw/DBIx::Class/;

__PACKAGE__->load_components(qw/PK::Auto Core/);
__PACKAGE__->table('sdh_mux_settings');
__PACKAGE__->add_columns(qw/ node_id mux_type_id ip_address nsap /);
__PACKAGE__->belongs_to('nodes' => 'Qinfo::QDBSchema::Nodes','node_id'
+);
__PACKAGE__->belongs_to('mux_types' => 'Qinfo::QDBSchema::MuxTypes','m
+ux_type_id');

1;
[download]

SELECT me.net_id, me.node_id, me.node_name, me.node_type_id, sdh_mux_s
+ettings.no
de_id, sdh_mux_settings.mux_type_id, sdh_mux_settings.ip_address, sdh_
+mux_settin
gs.nsap, mux_types.mux_type_id, mux_types.mux_type, mux_types.descript
+ion FROM n
odes me LEFT JOIN sdh_mux_settings sdh_mux_settings ON ( sdh_mux_setti
+ngs.node_i
d = me.node_id ) LEFT JOIN mux_types mux_types ON ( mux_types.mux_type
+_id = sdh_
mux_settings.mux_type_id ) ORDER BY sdh_mux_settings.node_id:
SELECT me.node_id, me.mux_type_id, me.ip_address, me.nsap FROM sdh_mux
+_settings
me WHERE ( me.node_id = ? ): '2'
SELECT me.node_id, me.mux_type_id, me.ip_address, me.nsap FROM sdh_mux
+_settings
me WHERE ( me.node_id = ? ): '3'
SELECT me.node_id, me.mux_type_id, me.ip_address, me.nsap FROM sdh_mux
+_settings
me WHERE ( me.node_id = ? ): '4'
SELECT me.node_id, me.mux_type_id, me.ip_address, me.nsap FROM sdh_mux
+_settings
me WHERE ( me.node_id = ? ): '5'
SELECT me.node_id, me.mux_type_id, me.ip_address, me.nsap FROM sdh_mux
+_settings
me WHERE ( me.node_id = ? ): '6'
SELECT me.node_id, me.mux_type_id, me.ip_address, me.nsap FROM sdh_mux
+_settings
me WHERE ( me.node_id = ? ): '7'
SELECT me.node_id, me.mux_type_id, me.ip_address, me.nsap FROM sdh_mux
+_settings
me WHERE ( me.node_id = ? ): '8'
[download]