morgon has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

I have dabbled a bit in the excellent DBIx::Class but currently I am having problems with correlated subqueries..

As I can't really find good documentation, could someone please provide an example for me?

For simplicity I refer to the example-schema in DBIx::Class::Manual::Example (the well-known artists-cds-tracks setting).

To keep it simple, could someone please show me how to do a search on the artist-resultset that has an extra column containing the number of cds belonging to the artist.

I am sure it is easy, but I cannot figure it out (too much beer already?) - sp please enlighten me...

Many thanks!

Replies are listed 'Best First'.
Re: correlated subqueries in DBIx::Class
by morgon (Priest) on Jan 16, 2011 at 05:20 UTC
    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.

[reply]
[d/l]
[select]
      The "+as" of "device_count" does not exist in the generated SQL. Anyone have a solution????
[reply]
        Sorry my error; the "+as" of "device_count" is not for the SQL but the resultant row in PERL. So device_count is a column name in each row.
[reply]

Back to Seekers of Perl Wisdom