Are course_week1 and course_week2 not null??
If not you need ...requires=IS_NULL_OR(IS_IN_DB(your requires) and in
...represent= you should use zero option (see book about that) or something
like that :
db.t_registration_form.course_week1.represent=\
lambda value: (value!=None and "%(code)s" %db.courses[value]) or 'None'
Richard
On Mon, Apr 4, 2011 at 9:21 AM, Johann Spies <[email protected]> wrote:
> I want to get a proper representation of a field in the result of a left
> join but am struggling to do so.
>
> Here is a simplified version of the problem.
>
> db.define_table('courses',
> Field('week','integer', requires=IS_IN_SET([1,2]),
> widget = SQLFORM.widgets.radio.widget),
> Field('code'),
> format='%(code)s')
>
> week1 = db(db.courses.week ==1)
> week2 = db(db.courses.week ==2)
>
> db.define_table('t_registration_form',
> Field('f_attendee', db.auth_user,default=auth.user_id,
> label=T('Attendee'),writable=False,readable=True),
> Field('course_week1', db.courses,
> requires = IS_IN_DB(db(db.courses.week == 1),db.courses.id
> ,'%(code)s',),
> label=T('Course for week 1')),
> Field('course_week2', db.courses,
> requires = IS_IN_DB(db(db.courses.week == 2),db.courses.id
> ,'%(code)s',),
> label=T('Course for week 2')),
> )
>
> #db.t_registration_form.course_week1.represent = lambda x:
> db.courses(x).code
> #db.t_registration_form.course_week2.represent = lambda x:
> db.courses(x).code
>
> The query:
> r1 = db().select(
> db.auth_user.first_name, db.auth_user.last_name,
> db.t_registration_form.course_week1,
> db.t_registration_form.course_week2,
> left = db.t_registration_form.on((db.auth_user.id ==
> db.t_registration_form.f_attendee)&
>
> (db.t_registration_form.active==True)),
>
> orderby=db.t_registration_form.course_week1|db.t_registration_form.course_week2)
>
> Not all the users have registered for courses.
>
> In the result I get the id's for the fields db.t_registration_form.week1
> and ....week2. I would like the db.courses.code there but when I uncomment
> the commented lines above, I get the error:
>
> AttributeError: 'NoneType' object has no attribute 'code'
>
>
> How can get represent to work in this case?
>
> If I do a left join with three tables (db.courses included) I have to use
> ((db.t_registration_form.course_week1 ==
> db.courses.id)|(db.t_registration_form.course_week2
> == db.courses.id)) and then ends up with two lines per user who have
> registered for both weeks - and my client does not like that.
>
> Regards
> Johann
>
> --
> May grace and peace be yours in abundance through the full knowledge of
> God and of Jesus our Lord! His divine power has given us everything we need
> for life and godliness through the full knowledge of the one who called us
> by his own glory and excellence.
> 2 Pet. 1:2b,3a
>
>
