Right then guys:
I am trying to build the following query:
# SELECT
# SUM(ABS(q.x_axis)) * MAX(ABS(a.value))
# FROM
# tests_workplaceculture_answer a,
# tests_workplaceculture_phaseanswer pa,
# tests_workplaceculture_phase me,
# tests_workplaceculture_question q
# WHERE
# me.test_id = ?
# AND pa.phase_id = me.phase_id
# AND a.answer_id = pa.answer_id
# AND q.phase_id = me.phase_id;
The best I've come up with so far is (doing the multiply seems beyond
SQL::A at the moment so i can do that myself) :
$self->{test_row}->phases->search(
undef,
{
prefetch => [
{ phase_answers => 'answer' },
'questions'
],
select => [
{ SUM => { ABS => 'question.x_axis' } },
{ SUM => { ABS => 'question.y_axis' } },
{ MAX => { ABS => 'answer.value' } },
],
as => [ qw/
max_x
max_y
max_answer
/],
}
)->first;
However this creates the following SQL.
SELECT SUM( ABS( `question`.`x_axis` ) ), MAX( ABS( `answer`.`value` )
), `phase_answers`.`phase_id`, `phase_answers`.`answer_id`,
`answer`.`answer_id`, `answer`.`short_name`, `answer`.`long_name`,
`answer`.`value`, `questions`.`phase_id`, `questions`.`question_id`,
`questions`.`text`, `questions`.`x_axis`, `questions`.`y_axis` FROM
`tests_workplaceculture_phase` `me` LEFT JOIN
`tests_workplaceculture_phaseanswer` `phase_answers` ON (
`phase_answers`.`phase_id` = `me`.`phase_id` ) JOIN
`tests_workplaceculture_answer` `answer` ON ( `answer`.`answer_id` =
`phase_answers`.`answer_id` ) LEFT JOIN
`tests_workplaceculture_question` `questions` ON (
`questions`.`phase_id` = `me`.`phase_id` ) WHERE ( `me`.`test_id` = ? )
ORDER BY `phase_answers`.`phase_id`, `questions`.`phase_id`
Any recourse?
Thanks
Ash
_______________________________________________
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/
Searchable Archive: http://www.mail-archive.com/[email protected]/
