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.

In reply to Re: correlated subqueries in DBIx::Class by morgon
in thread correlated subqueries in DBIx::Class by morgon

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.