[Dbix-class] Generating DISTINCT ON with sub query

[Peter Rabbitson]

On Tue, Apr 02, 2013 at 08:20:34PM -0700, Rippl, Steve wrote:
> I have something like the following
> 
> $self->search(
>             $search,
>             {   join => [
>                     { 'course'  => [
>                         { course_coursetypes  => 'type' },
>                         { course_grades => 'grade' },
>                         'course_locations',
>                     ] },
>                     { 'section' => { 'section_staffs' => 'staff' } }
>                 ],
>                 '+select' => [
>                     'staff.name_first', 'staff.name_last',
>                     'course.name',
>                 ],
>                 '+as'     => [ qw( name_first name_last course_name ) ],
>                 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" = "me"."course_id"
>     LEFT JOIN "course_grade" "course_grades" ON "course_grades"."course_id"
> = "course"."id"
>     LEFT JOIN "grade" "grade" ON "grade"."id" = "course_grades"."grade_id"
>     LEFT JOIN "course_location" "course_locations" ON
> "course_locations"."course_id" = "course"."id"
>     JOIN "section" "section" ON "section"."id" = "me"."section_id"
>     LEFT JOIN "section_staff" "section_staffs" ON
> "section_staffs"."section_id" = "section"."id"
>     LEFT JOIN "staff" "staff" ON "staff"."id" = "section_staffs"."staff_id"
>     WHERE ( "section"."active" = 1 )
>     ORDER BY "grade"."id", "course"."name", "section"."name"
> ) AS foo
> 
> Can I generate that in dbic?

Not directly because this is highly non-standard SQL, and there isn't 
smooth support for this kind of thing in the API yet. However there are 
other ways of achieveing what you want. I want to clarify however what 
is your final goal. From your relationship map it is unclear:

- What is the relationship between me (course_section) and section. Is this
  a belongs_to ? a might_have? a has_many?

- Your Pg query would return *multiple* identical course_section's that 
only differ by the related names injected into them by the +select. This 
seems rather non-DBIC-ish - the usual way os to have has_many related 
data hanging as separate objects.

Clarify the above and I'll get back to you with practical ways to 
achieve what you want

Cheers