[Dbix-class] Query Building Help

[Brandon Black]

On 8/10/06, Ash Berlin <ash at cpan.org> wrote:
>
> 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 = ?;


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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.rawmode.org/pipermail/dbix-class/attachments/20060810/34aadcdb/attachment.htm