Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine

Re: correlated subqueries in DBIx::Class

by morgon (Priest)
on Jan 16, 2011 at 05:20 UTC ( #882510=note: print w/replies, xml ) Need Help??

in reply to correlated subqueries in DBIx::Class

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( { "" => { '=' => \'' }}, { join => + { "boxes" => "devices" }, "+select" => [""], +as =>["devic +e_id"], alias => 'you'})->count_rs->as_query, ], "+as" => [ "device_count" , ], } );
What may look as utter gibberish in fact generates the following SQL:

(SELECT, 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 = INNER JOIN + device devices ON devices.box_id = WHERE ( = ) +) FROM customer me)
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.

Replies are listed 'Best First'.
Re^2: correlated subqueries in DBIx::Class
by Anonymous Monk on Jan 20, 2014 at 00:00 UTC
    The "+as" of "device_count" does not exist in the generated SQL. Anyone have a solution????
      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.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://882510]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (3)
As of 2020-09-25 20:58 GMT
Find Nodes?
    Voting Booth?
    If at first I donít succeed, I Ö

    Results (141 votes). Check out past polls.