On 5/18/06, Matt S Trout <[EMAIL PROTECTED]> 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
_______________________________________________
List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
Wiki: http://dbix-class.shadowcatsystems.co.uk/
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
