DBIx::...ionship(3pm)

📦
version 2.037000 · 5 versions
, in debian trixie, ubuntu resolute, ubuntu stonking

💖 DBIx::...ionship — bestie edition 💖

okay so this is the manual page for DBIx::Class::Helper::ResultSet::CorrelateRelationship, and it lives in the User Contributed Perl Documentation section of your man pages. the full name is lowkey a mouthful (DBIx::...ionship) but the whole point is: it helps you easily correlate your ResultSets. let's get into it 💅

✨ NAME ✨

DBIx::Class::Helper::ResultSet::CorrelateRelationship — literally just here to make correlating your ResultSets easy. that's it, that's the job. 🫶

🦋 SYNOPSIS 🦋

okay so first you gotta set up your ResultSet class like this:

package MyApp::Schema::ResultSet::Author;

use parent 'DBIx::Class::ResultSet';

__PACKAGE__->load_components(qw(Helper::ResultSet::CorrelateRelationship));

sub with_book_count {
  my $self = shift;

  $self->search(undef, {
    '+columns' => {
      book_count => $self->correlate('books')->count_rs->as_query
    }
  });
}

1;

and then somewhere else — like, say, in a controller — you just:

my $rows = $schema->resultset('Author')->with_book_count->all;

and boom. you're done. slay. ✨

👑 DESCRIPTION 👑

okay real talk: correlated queries are genuinely one of the coolest things I've ever learned about SQL, and I mean that. ever since I first learned SQL. no cap. 😭

but... they're also kinda confusing. DBIx::Class has supported doing them for a long, long time, but people generally don't even think of them because they're so rare. so I'm not gonna walk you through every single detail of how they work and all the cool stuff you can do — but here are a couple of high-level things you can use them for, to save you time or effort. 💖

so, scenario one: you want to select a list of authors and counts of books for each author. you could use group_by and something like COUNT(book.id), but then you'd need to make your select list match your group_by, and honestly? it would just be a hassle forever after that. 🙈 the SYNOPSIS is a perfect example of how to implement this. like, chef's kiss.

scenario two: you want to select a list of authors and two separate kinds of counts of books for each author. as far as I know, you must use a correlated subquery in DBIx::Class for that. here's how you might do it:

package MyApp::Schema::ResultSet::Author;

use parent 'DBIx::Class::ResultSet';

__PACKAGE__->load_components(qw(Helper::ResultSet::CorrelateRelationship));

sub with_good_book_count {
  my $self = shift;

  $self->search(undef, {
    '+columns' => {
      good_book_count => $self->correlate('books')->good->count_rs->as_query
    }
  });
}

sub with_bad_book_count {
  my $self = shift;

  $self->search(undef, {
    '+columns' => {
      bad_book_count => $self->correlate('books')->bad->count_rs->as_query
    }
  });
}

1;

and then elsewhere, like in a controller:

my $rows = $schema->resultset('Author')
  ->with_bad_book_count
  ->with_good_book_count
  ->all;

quick heads up: this assumes that the Book resultset has good and bad methods. iykyk. 💅

also see "NOTE" in DBIx::Class::Helper::ResultSet for a nice way to apply it to your entire schema. obsessed with that trick fr fr.

🎀 METHODS 🎀

🌸 correlate 🌸

$rs->correlate($relationship_name)

correlate takes a single argument — a relationship for the invocant — and returns a resultset that can be used in the selector list. one argument, one resultset, no drama. ✨

🌈 EXAMPLES 🌈

🍓 counting CD's and Tracks of Artists 🍓

okay so if you had an Artist ResultSet and you wanted to count the tracks and the CD's per Artist, here's a recipe that will work:

sub with_track_count {
  my $self = shift;

  $self->search(undef, {
    '+columns' => {
      track_count => $self->correlate('cds')
        ->related_resultset('tracks')
        ->count_rs
        ->as_query
    }
  });
}

sub with_cd_count {
  my $self = shift;

  $self->search(undef, {
    '+columns' => {
      cd_count => $self->correlate('cds')
        ->count_rs
        ->as_query
    }
  });
}

# elsewhere

my @artists = $artists->with_cd_count->with_track_count->all;
💀 now... note that the following will NOT work:
sub BUSTED_with_track_count {
  my $self = shift;

  $self->search(undef, {
    '+columns' => {
      track_count => $self->related_resultset('cds')
        ->correlate('tracks')
        ->count_rs
        ->as_query
    }
  });
}

the above is broken because correlate returns a fresh resultset that will only work as a subquery to the ResultSet it was chained off of. the upshot of that is that the above tracks relationship is on the cds ResultSet, whereas the query is for the Artist ResultSet, so the correlation will be "broken" by effectively "joining" to columns that are not in the current scope. 😭 not the vibe.

and for the exact same reason, this one will also not work:

sub BUSTED2_with_track_count {
  my $self = shift;

  $self->search(undef, {
    '+columns' => {
      track_count => $self->correlate('cds')
        ->correlate('tracks')
        ->count_rs
        ->as_query
    }
  });
}

two BUSTEDs, zero survivors. 🙈

🧚 SEE ALSO 🧚

both absolute bangers, go read them. 📚✨

💜 AUTHOR 💜

Arthur Axel "fREW" Schmidt <frioux+cpan@gmail.com>

iconic name, iconic email, we stan. 👑

This software is copyright (c) 2024 by Arthur Axel "fREW" Schmidt.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.

and that's the whole page bestie!! this was DBIx::...ionship(3pm) from perl v5.40.0, dated 2024-11-16. go forth and correlate responsibly, mwah 💋💖🦋

📦 where this page comes from