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' ]
}
);
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" = "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"
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?
Thanks,
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
