.represent = ... in left join

[Johann Spies]

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

[Richard Vézina]

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

[Johann Spies]

On 4 April 2011 16:11, Richard Vézina <ml.richa...@gmail.com> wrote:

Are course_week1 and course_week2 not null??

No.
 

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'

Thanks!  That solved the problem.  I could not find something in the book about a 'zero option' though.

Regards
Johann

[Johann Spies]

On 5 April 2011 09:16, Johann Spies <johann...@gmail.com> wrote:

Thanks!  That solved the problem.  I could not find something in the book about a 'zero option' though.

Now .represent works on the screen but not in the CSV-output.  I have used:

{{
import cStringIO
stream=cStringIO.StringIO()
rows.export_to_csv_file(stream,represent=True)
response.headers['Content-Type']='application/vnd.ms-excel'
response.write(stream.getvalue(), escape=False)
}}
 
but putting the option 'represent=True) in there did not make any difference.

Regards
Johann

[Richard Vézina]

About zero option, search in page for zero : http://www.web2py.com/book/default/chapter/07

There is some explanation near IS_IN_SET...

There maybe more detailed information somewhere else too... I don't use it in my code since it arrive after I start my dev or I was not aware at the begining... I should make refoctoring ;-)

Richard

[Richard Vézina]

I did not use export_to_csv for now... I just test it sometimes ago... But I guest you should define your .represent for stream table before try to apply them to your csv output...

Hope it helps.

Richard

[Mike Veltman]

Well as a beginner with web2py I first have to say that I like it. So thank
you to all the people who work on it.

I am also a beginner with Python so do not expect very complicated questions
(yet)

Well I am building a application to deploy AIX unix installs on power
machines. My application creates the logical volumes on the storage, creates
the virtual machines and starts the deployments. Its all coordinated with
web2py.

Now my question.

When I start a deployment the function could take a while and one solution
would be to create a new window with the status of the deployment. Would it be
possible to spawn a separate window and let the original form go on ?

Thanks in advance.

Mike Veltman

[Johann Spies]

On 5 April 2011 15:51, Richard Vézina <ml.richa...@gmail.com> wrote:

About zero option, search in page for zero : http://www.web2py.com/book/default/chapter/07

There is some explanation near IS_IN_SET...

Thanks.  I found it.

Johann