[Dbix-class] trouble with using 'select' in a search clause

[Matt S Trout]

Brandon Black wrote:
> On 5/18/06, Matt S Trout <dbix-class at trout.me.uk> wrote:
>> equivalent to the pure hand-crafted SQL you would have done anyway, I want to
>> know what the difference is so I can hack it in as a supported feature.
> 
> Just for the record, here's one of my queries I've been
> unable/unwilling to convert yet, just to chew on.  This really runs
> correctly and gives exactly the desired results, and is the fastest
> form of this query I was able to invent - even just rearranging a few
> of the joins slows it down substantially.
> 
> SELECT host_group.groupname, version, exec_start_stamp, project, name,
> exechost, submitted_by, fmodule, slave_stat((ncpu,
> host_asdfstatus.status, cpu_idle, num_cpus)) FROM host_group LEFT JOIN
> (host_to_host_group RIGHT JOIN (xyzjob LEFT JOIN (xyzslave JOIN
> host_asdfstatus ON (host_asdfstatus.hostname = xyzslave.slave) JOIN
> host_cfg_allcpu ON (host_cfg_allcpu.hostname = xyzslave.slave AND
> host_cfg_allcpu.max_stamp = host_asdfstatus.max_cfg_stamp)) ON
> (xyzslave.xyzid = xyzjob.xyzid)) ON (xyzjob.exechost =
> host_to_host_group.hostname)) ON (host_group.groupname =
> host_to_host_group.groupname) WHERE host_group.is_cluster = 'TRUE' AND
> host_group.groupname IN (SELECT groupname FROM connectby('host_group',
> 'groupname', 'parentgroup', ?, 0) AS t(groupname text, parentgroup
> text, level int)) GROUP BY host_group.groupname, version,
> exec_start_stamp, project, name, exechost, submitted_by, fmodule ORDER
> BY host_group.groupname, exechost, name LIMIT ? OFFSET ?

Wow. Ok, I'm stuffed on doing the IN without literal SQL since we don't 
have sub-selects yet, but other than that ...

$rs->search(
   {
     -and => [
       { 'host_group.is_cluster' => 'TRUE' },
       { 'host_group.groupname' =>
           \"IN
             (SELECT groupname
             FROM connectby('host_group','groupname',
                            'parentgroup', ?, 0)
             AS t(groupname text, parentgroup text, level int))"
       }
     ]
   },
   {
     bind => [ $connect_by_arg ],
     select => [
       'host_group.group_name', 'version', 'exec_start_stamp', 'project',
       'name', 'exechost', 'submitted_by', 'fmodule',
       \"slave_stat((ncpu, host_asdfstatus.status, cpu_idle, num_cpus))"
     ],
     as => [ <insert names to inflate to here> ],
     alias => 'host_group'
     from => [
       <I could do the nested from. but it's 1am and my brain hurts>
     ]
   }
);