SQLFORM dropdown odd orderby

[Tiago Rosa]

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

[annet]

This being your model:

db.define_table('teachers',
Field('code',length=18,default='',notnull=True,unique=True),
migrate='teachers.table')

db.define_table('subjects',
Field('code',length=30,default='',notnull=True,unique=True),
migrate='subjects.table')

db.define_table('teachers_subjects',
Field('teacher_id',db.teachers),
Field('subject_id',db.subjects),
migrate='teachers_subjects.table')

db.teachers_subjects.teacher_id.requires=IS_IN_DB(db,db.teachers.id,'%
(code)s',zero=None)
db.teachers_subjects.subject_id.requires=IS_IN_DB(db,db.subjects.id,'%
(code)s',zero=None)

This being the population in the tables:

teachers:
1 t1
2 t2

subjects
1 a
2 b
3 c
4 d
5 e
6 f
7 9

teachers_subjects
1 1 1
2 1 4
3 1 7
4 2 2

The code to create the drop box would be:

def index():
rows=db().select(db.subjects.id,db.subjects.code,\

left=db.teachers_subjects.on((db.subjects.id==db.teachers_subjects.subject_id)
\
&(db.teachers_subjects.teacher_id==1)),\
orderby=~db.teachers_subjects.teacher_id|db.subjects.id)
key=[k.id for k in rows]
value=[v.code for v in rows]

form=SQLFORM.factory(Field('subject',requires=IS_IN_SET(key,value,zero=None)))
return dict(form=form)

Kind regards,

Annet.

[mdipierro]

you can pass orderby to IS_IN_DB

from the shell you can get help about every function like

>>> help(IS_IN_DB)

[annet]

Massimo,

> you can pass orderby to IS_IN_DB

To which IS_IN_DB do I have to pass the orderby to get the same
result. The resultset reads like:


1 1 a
1 4 d
1 7 9
None 2 b
None 3 c
None 5 e
None 6 f


The orderby: orderby=~db.teachers_subjects.teacher_id|db.subjects.id
first sorts it in reverse order by db.teachers_subjects.teacher_id and
then by db.subjects.id, to meet Tiago's requirement:

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

Annet.

[Tiago Rosa]

Hello annet and Massimo!

Thank you very much for your help! I still need some enlightenment
though =)

I've managed to make it work thanks to annet's tip. Here's how I am
currently doing it:

I have defined a helper function that takes a teacher_id and spits out
the dropdown field (!) generated through annet's method:

def subject_biased_dropdown(teacher_id):
rows = db().select(db.subjects.id, db.subjects.code,
left = db.teachers_subjects.on(
(db.subjects.id==db.teachers_subjects.subject_id)&
(db.teachers_subjects.teacher_id==teacher_id)),
orderby=db.teachers_subjects.teacher_id|db.subjects.id)
key = [row.id for row in rows]
value = [row.name for row in rows]
form = SQLFORM.factory(
Field('subject_id', requires=IS_IN_SET(key,value,zero=None)))
return form[0][0]

And then, in the "evaluation/create" function of the "evaluation"
controller, I replace the dropdown field in the autogenerated SQLFORM
with the "biased" one that comes from the above function:

def create():
(...)
form_add=SQLFORM(db.evaluations,
(...)
)
form_add[0][0] = subject_biased_dropdown(prof_id)
if form_add.accepts(request.vars, session):
(...)
return dict(form=form_add)

Yes, it works, but I feel something strange about the way I'm
implementing this. I guess I'm missing something obvious that would
make it easier/more "best-practice-like". Defining the orderby at the
model level seems like a better option, but I can't figure it out yet.

Again, thank you very much for your help!

Regards,

Tiago Rosa

[Tiago Rosa]

An even deeper problem: how to do something like that in a GAE
environment? The BigTable-like datastore has a lot of query
restrictions.

[Tiago Rosa]

Well, answering my own question, I've managed to get it to work with
GAE with a kinda bizarre data juggling:

The helper function that returns the form element to be inserted into
the SQLFORM is now like this:

def gae_subject_biased_dropdown(teacher_id):
subjects = db(db.subjects.id>0).select(db.subjects.id,
db.subjects.code)
teachers_subjects =
db(db.teachers_subjects.id>0).select(db.teachers_subjects.subject_id,
db.teachers_subjects.teacher_id).as_list()
results = []
for subj in subjects:
if {'teacher_id': int(teacher_id), 'subject_id': subj.id} in
teachers_subjects:
results.append([subj.id, subj.code, 1])
else:
results.append([subj.id, subj.code, 0])
key = [res[0] for res in sorted(results, key=lambda x: x[2],
reverse=True)]
value = [res[1] for res in sorted(results, key=lambda x: x[2],
reverse=True)]
form = SQLFORM.factory(
Field('subject_id', label="Subject", requires=IS_IN_SET(key, value,
zero=None)))
return form[0][0]

On Oct 12, 1:15 am, Tiago Rosa <tiago.str...@gmail.com> wrote:
> An even deeper problem: how to do something like that in a GAE
> environment? The BigTable-like datastore has a lot of query
> restrictions.
>
> On Oct 6, 9:25 pm, Tiago Rosa <tiago.str...@gmail.com> wrote:
>

[Tiago Rosa]

Just to correct myself and register a working solution for the case:

The code below spits out the dropdown element HTML, which you can
manually insert into your SQLFORM HTML (replacing the default
dropdown) to acquire the desired result:

def gae_subject_biased_dropdown(teacher_id):
subjects = db(db.subjects.id>0).select(db.subjects.ALL)
teachers_subjects =
db(db.teachers_subjects.id>0).select(db.teachers_subjects.ALL).as_list()
ts = map(lambda x: {'teacher_id': x['teacher_id'], 'subject_id':
x['subject_id']}, teachers_subjects)
results = []
for subject in subjects:
dictkey = {'teacher_id': int(teacher_id), 'subject_id':
subject.id}
if dictkey in ts:
results.append([subject.id, subject.name,
teachers_subjects[ts.index(dictkey)]['count']])
else:
results.append([subject.id, subject.name, 0])
key = [res[0] for res in sorted(sorted(results, key=lambda x:
x[1]), key=lambda x: x[2], reverse=True)]
value = [res[1] for res in sorted(sorted(results, key=lambda x:
x[1]), key=lambda x: x[2], reverse=True)]

form = SQLFORM.factory(
Field('subject_id', label="Subject",
requires=IS_IN_SET(key, value, zero=None)))
return form[0][0]

It's damn ugly dict/list manipulation and can certainly be improved,
although possibly compromising readability.

Best regards,
Tiago Rosa