[Dbix-class] Query Building Help

[Ash Berlin]

Brandon Black wrote:
>
> On 8/10/06, *Ash Berlin* <ash at cpan.org <mailto:ash at cpan.org>> wrote:
>
>     Right then guys:
>
>     I am trying to build the following query:
>     [......]
>     Any recourse?
>
>
> I don't think you've got enough join/prefetch's in your DBIC version 
> to be doing what you want it to do.  All total, there should be three 
> JOIN operations taking place.
>
> I would suggest re-writing your original query using real JOINs first, 
> then attacking it from DBIC.  I know Pg accepts what you've got (maybe 
> other DBs do too?) by basically running a query analysis and then 
> creating what it thinks is the optimal set of JOINs for your list of 
> tables and WHERE conditions.  But it will be easier to see the 
> discrepancies if you specify your JOINs explicitly (its also easier to 
> tune your queries for performance that way).  DBIC will always 
> generate explicit JOIN syntax.
>
> -- Brandon
*mumble mumble mumble* - making me remember how to do joins. *mumble 
mumble mumble*

SELECT
 SUM(ABS(questions.x_axis)) max_x, SUM(ABS(questions.y_axis)) max_y,  
MAX(ABS(answer.value)) max_answer
FROM
  tests_workplaceculture_phase me
  JOIN tests_workplaceculture_phaseanswer phase_answers ON ( 
phase_answers.phase_id = me.phase_id)
  JOIN tests_workplaceculture_answer answer ON ( phase_answers.answer_id 
= answer.answer_id)
  JOIN tests_workplaceculture_question questions ON ( questions.phase_id 
= me.phase_id)
WHERE
  me.test_id = ?;

Ash