On Wed, Apr 3, 2013 at 2:46 AM, Peter Rabbitson <rabbit+d...@rabbit.us>wrote:

> 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
>
>
Thanks for the offer of help Peter.

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

__PACKAGE__->belongs_to(
  "section",
  "WsdSis::Schema::Result::Section",
  { id => "section_id" },
  { is_deferrable => 1, on_delete => "CASCADE", on_update => "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" => "self.id" },
  { cascade_copy => 0, cascade_delete => 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
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk

Reply via email to