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

[Brandon Black]

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 ?

-- Brandon