I figured out my real problem, which is similar.

Instead of artists/cds/tracks I have customers/boxes/devices.

A customer has many boxes, a box has many devices.

I want to have a customer-resultset that contain a count-column of all devices that belong to a customer (via a box).

Here is what seems to do the trick, comments would be very welcome as this is the most complex thing I ever did with dbix::class:

my $crs = MyDBClass->resultset("Customer");

my $rs = $crs->search( undef, {
          "+select" => [
           $crs->search( { "you.id" => { '=' => \'me.id' }}, { join =>
+ { "boxes" => "devices" }, "+select" => ["devices.id"], +as =>["devic
+e_id"], alias => 'you'})->count_rs->as_query,
                       ],
          "+as" => [
                       "device_count" ,
                    ],
                                                      } );
[download]

What may look as utter gibberish in fact generates the following SQL:

(SELECT me.id, me.bu_id, me.description, me.contact_name, me.contact_p
+hone, me.contact_email, me.creation_ts, (SELECT COUNT( * ) FROM custo
+mer you INNER JOIN box boxes ON boxes.customer_id = you.id INNER JOIN
+ device devices ON devices.box_id = boxes.id WHERE ( you.id = me.id )
+) FROM customer me)
[download]

I would like to stress that while this may look complex it actually is not too bad once you've wrapped your brain around the way DBIx::Class represents queries.

While this may not be the best demonstration of DBIx::Class strenghts I cannot recommend it high enough - while sometimes it may take some time figuring it out it saves tons of time in the long run.

If you don't use it already check it out.