Hi folks,
I'll try my best to explain my problem in detail:
I have two tables ("teachers" and "subjects"), and a third one called
"teachers_subjects" that basically describes a many-to-many
relationship between teachers and subjects - a row in this table
indicates that a given subject has been taught by the corresponding
teacher. The "teachers_subjects" table references teachers and
subjects by id, or in other words, a row in the "teachers_subjects"
table has only two fields: teacher_id and subject_id. A subject can be
associated to many teachers and a teacher can be associated to many
subjects. The only restriction is the uniqueness of a (teacher_id,
subject_id) key.
The goal behind having teachers and subjects modeled is creating
evaluations for a teacher in a given subject, ie, evaluating the work
of a teacher in the teaching of a given subject. This is a concept
called "evaluation" that's also modeled. I'm currently using SQLFORM
to maintain the "evaluations" table, and one of the fields in this
form is a dropdown for selecting a subject - the "subject_id" field of
the evaluations table has a IS_IN_DB requirement that links it to the
"subjects" table.
And now for the problem:
What I want is to sort the items on that dropdown so the subjects that
are associated (through "teachers_subjects") to the given teacher
(request.vars.teacher_id) appear on the top, as the first items in the
dropdown, followed by the subjects that aren't related to her in a
common alphabetical order.
So if we have:
Teachers: t1, t2
Subjects: a, b, c, d, e, f, g
Teachers_subjects: (t1,a), (t1,d), (t1,g), (t2,b)
When creating a new evaluation for teacher t1, the items on the
subject dropdown should appear in the following order:
a //related
d //related
g //related
b
c
e
f
Instead of a straightforward alphabetical order.
My question: what's the best way to implement this? I've searched
quite a bit but couldn't get a grip on it yet. Adding a virtual field
to the "evaluations" table that simply gets "1" if there's an entry in
"teachers_subjects" or "0" if there isn't seems like an option, but a
kinda ugly one, as it needs to get teacher_id from request.vars and is
not very straightforward.
Any help will be greatly appreciated! =)
[]s
