[Dbix-class] Generating DISTINCT ON with sub query

[Rippl, Steve]

I have something like the following

$self->search(
            $search,
            {   join =3D> [
                    { 'course'  =3D> [
                        { course_coursetypes  =3D> 'type' },
                        { course_grades =3D> 'grade' },
                        'course_locations',
                    ] },
                    { 'section' =3D> { 'section_staffs' =3D> 'staff' } }
                ],
                '+select' =3D> [
                    'staff.name_first', 'staff.name_last',
                    'course.name',
                ],
                '+as'     =3D> [ qw( name_first name_last course_name ) ],
                order_by  =3D> [ 'grade.id', 'course.name', 'section.name' ]
            }
        );

which give or take generates

SELECT "me"."section_id", "me"."course_id", "staff"."name_first",
"staff"."name_last", "course"."name"
FROM "course_section" "me" JOIN "course" "course"
ON "course"."id" =3D "me"."course_id"
LEFT JOIN "course_grade" "course_grades" ON "course_grades"."course_id" =3D
"course"."id"
LEFT JOIN "grade" "grade" ON "grade"."id" =3D "course_grades"."grade_id"
LEFT JOIN "course_location" "course_locations" ON
"course_locations"."course_id" =3D "course"."id"
JOIN "section" "section" ON "section"."id" =3D "me"."section_id"
LEFT JOIN "section_staff" "section_staffs" ON "section_staffs"."section_id"
=3D "section"."id"
LEFT JOIN "staff" "staff" ON "staff"."id" =3D "section_staffs"."staff_id"
WHERE ( "section"."active" =3D 1 )
ORDER BY "grade"."id", "course"."name", "section"."name"

Sections can have multiple grades, so the join to course_grades can produce
multiple lines for each section. I need that join so I can still sort by
their lowest grade (for sensible ordered display) but then I want to go
back to distinct sections.  The following SQL (in postgres) seems to do
that for me...

SELECT DISTINCT ON (section_id) * FROM (
    SELECT "me"."section_id" AS section_id, "me"."course_id",
"staff"."name_first", "staff"."name_last", "course"."name"
    FROM "course_section" "me" JOIN "course" "course"
    ON "course"."id" =3D "me"."course_id"
    LEFT JOIN "course_grade" "course_grades" ON "course_grades"."course_id"
=3D "course"."id"
    LEFT JOIN "grade" "grade" ON "grade"."id" =3D "course_grades"."grade_id"
    LEFT JOIN "course_location" "course_locations" ON
"course_locations"."course_id" =3D "course"."id"
    JOIN "section" "section" ON "section"."id" =3D "me"."section_id"
    LEFT JOIN "section_staff" "section_staffs" ON
"section_staffs"."section_id" =3D "section"."id"
    LEFT JOIN "staff" "staff" ON "staff"."id" =3D "section_staffs"."staff_i=
d"
    WHERE ( "section"."active" =3D 1 )
    ORDER BY "grade"."id", "course"."name", "section"."name"
) AS foo

Can I generate that in dbic?

Thanks,
Steve

-- =

Steve Rippl
Technology Director
Woodland Public Schools
360 841 2730
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20130402/280=
83e0a/attachment.htm