NAME

DBIx::Class::Tutorial::ExploringRelations - Joining, related data and chaining searches

CONTENTS

See "CONTENTS" in DBIx::Class::Tutorial

INTRODUCTION

You can already select simple data rows, display them and update them. This chapter will introduce you using relationships to join tables.

Databases are all about relationships between rows of data, we can exploit those relations to return exactly the data we need, without running multiple queries.

GLOSSARY

Some terms needed for this chapter:

"Joins" in DBIx::Class::Manual::Glossary

"Table alias" in DBIx::Class::Manual::Glossary

DESCRIPTION

Counting comments

Each blog post can have many comments. On our post display, we'd like to display how many comments there are.

Our posts display for a particular user was fetched like this:

  my $posts = $user->posts->search(
    {}, 
    { 
      rows => 10,
      page => 1,
      order_by => [ 'created_date desc' ],
    }
  );

To add a count of associated comments for each post, we amend it to this:

  my $posts = $user->posts->search(
    {}, 
    { 
      '+select' => [ { count => 'comments.id' } ],
      '+as'     => [ 'num_comments' ],
      join      => 'comments',
      rows      => 10,
      page      => 1,
      order_by  => [ 'created_date desc' ],
    }
  );

The join attribute instructs the search method to join to another table via a relationship. Since we're searching on the posts table, the relationship to the comments table needs to exist in Post.pm like this:

  __PACKAGE__->has_many('comments', 'MyBlog::Schema::Result::Comment', 'post_id');

Remember the first argument to a relationship method is its name, this is the value that is used in the join attribute. Not table names!

Once the join exists, we can add new columns to the query using +select. The + is significant, it adds new columns to the existing list, instead of replacing it, which we could do with select.

The hashref passed as a value is used to call an SQL function, in this case an aggregate function, count. We're counting the number of comment IDs.

The result can be illustrated with the actual SQL it produces. The original SQL would have produced a query like this:

  SELECT me.id, me.title, me.post, me.user_id FROM posts me WHERE user_id = ? LIMIT 10

Now we have:

  SELECT me.id, me.title, me.post, me.user_id, count(comments.id) FROM posts me 
    LEFT JOIN comments comments ON me.id = comments.post_id 
    WHERE user_id = ? LIMIT 10

To output the result, we loop over the posts ResultSet as before:

  while (my $post = $posts->next) {
    print $post->title, "\n";
    print "Posted on: ", $post->created_date->ymd, "\n";
    print "Post has ", $post->get_column('num_comments'), " comments\n";
  }

Notice that to fetch the new value we can't use an accessor on the $post object as there is no such column defined in the Post class. Instead we use get_column to fetch the value, using the name given in the +as attribute.

Filtering queries

Fetching more data

Fetching related rows

Chaining searches

GLOSSARY

EXERCISES

WHERE TO GO NEXT

User and post statistics

TODO

Patches and suggestions welcome.

AUTHOR

Jess Robinson <castaway@desert-island.me.uk>

Last modified:

Home