On 8/10/06, Ash Berlin <[EMAIL PROTECTED]> wrote:
AFAICS, this is equivalent to what DBIC generated. The only substantive changes are the LEFT JOINs in place of two of your JOINs, but assuming you have set up belongs_to and has_many correctly, I would assume DBIC is making a better choice of JOIN type than you did. What's wrong with the DBIC query?
The only other change is that its selecting a lot more columns than you are, because you're using prefetch instead of plain join.
-- Brandon
Brandon Black wrote:
>
> On 8/10/06, *Ash Berlin* <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>> 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 = ?;
AFAICS, this is equivalent to what DBIC generated. The only substantive changes are the LEFT JOINs in place of two of your JOINs, but assuming you have set up belongs_to and has_many correctly, I would assume DBIC is making a better choice of JOIN type than you did. What's wrong with the DBIC query?
The only other change is that its selecting a lot more columns than you are, because you're using prefetch instead of plain join.
-- 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/ Searchable Archive: http://www.mail-archive.com/[email protected]/
