[Dbix-class] Query Building Help

[Ash Berlin]

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