[Dbix-class] Generating DISTINCT ON with sub query

[Rippl, Steve]

On Wed, Apr 3, 2013 at 2:46 AM, Peter Rabbitson <rabbit+dbic at rabbit.us>wrot=
e:

> On Tue, Apr 02, 2013 at 08:20:34PM -0700, Rippl, Steve wrote:
> > 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.na=
me'
> ]
> >             }
> >         );
> >
> > 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_id"
> >     WHERE ( "section"."active" =3D 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 th=
is
>   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
>
>
Thanks for the offer of help Peter.

CourseSection.pm
...
 __PACKAGE__->belongs_to(
  "course",
  "WsdSis::Schema::Result::Course",
  { id =3D> "course_id" },
  { is_deferrable =3D> 1, on_delete =3D> "CASCADE", on_update =3D> "CASCADE=
" },
);

__PACKAGE__->belongs_to(
  "section",
  "WsdSis::Schema::Result::Section",
  { id =3D> "section_id" },
  { is_deferrable =3D> 1, on_delete =3D> "CASCADE", on_update =3D> "CASCADE=
" },
);

Each Section only has one teacher and belongs to one course, so it's not
the +select columns that are giving me multiple values as those are all the
same for each repeated row.  A course can have multiple grades and so
that's where the join is producing multiple rows per section_id.

Course.pm
...
__PACKAGE__->has_many(
  "course_grades",
  "WsdSis::Schema::Result::CourseGrade",
  { "foreign.course_id" =3D> "self.id" },
  { cascade_copy =3D> 0, cascade_delete =3D> 0 },
);

I was trying to group by section_id, but when sorting on grade_id I have to
include that in the group by and it doesn't seem to actually do any
grouping by!

Have I given you enough info?

Thanks again,
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/20130403/52b=
3241e/attachment.htm