DESCRIPTION

Correlated queries are one of the coolest things I've learned about for SQL since my initial learning of SQL. Unfortunately they are somewhat confusing. DBIx::Class has supported doing them for a long time, but generally people don't think of them because they are so rare. I won't go through all the details of how they work and cool things you can do with them, but here are a couple high level things you can use them for to save you time or effort.

If 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 it would just be a hassle forever after that. The "SYNOPSIS" is a perfect example of how to implement this.

If 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. Here is an example of how you might do that:

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.