Database reference issue

[Josh Butterworth]

Hi everyone, 

this is probably a trivial issue but I'm struggling with it! When I try and reference a field from another table I can get it to reference the id of the desired row, however I can't get it to reference the name associated with that id. I've highlighted the lines of code that are causing me issues.

This is my code atm:

In views:

  <tr>

            <td><a href="{{=URL('editdevtasklist', args=row.devtasklist.id)}}">{{=row.auth_user.FullName}}</a></td>

            <td>{{=row.devtasklist.jobtype}}</td>

            <td>{{=row.devtasklist.priority}}</td>

            <td>{{=row.devtasklist.projectid}}</td>

            <td>{{=row.devtasklist.heading}}</td>

            <td>{{=row.devtasklist.attachment}}</td>

            <td>{{=row.devtasklist.duedate}}</td>

        </tr>

# When the above code is referenced  I get only the 'id number'

# When the code below is used I receive an attribute error...

<td>{{=row.Project.ProjectName}}</td>

In controller:

def listdevtasklist():

    

    print_time = datetime.now()

    rows = db(db.devtasklist.userid == db.auth_user.id).select(orderby=db.auth_user.FullName)

    db(db.devtasklist.projectid == db.Project.id).select(db.Project.ProjectName)

    return locals()

In db:

db.define_table('devtasklist',

                Field("userid", "reference auth_user", label="User", requires=IS_IN_DB(db, db.auth_user.id, '%(first_name)s %(last_name)s')),

                Field('projectid', 'reference Project', label="Project", requires=IS_IN_DB(db, db.Project.id, '%(ProjectName)s')),

                Field("jobtype", requires = IS_IN_SET(("Admin", "Data analysis", "Data upload", "Fulcrum", "GIS", "Survey rep", "Programming", "Reports"))),

                Field("priority", label="Priority Level", requires = IS_IN_SET(task_priorities)),

                Field("duedate", "date", label="Due on", requires = IS_DATE(format=('%d-%m-%Y'))),

                Field("attachment","upload", autodelete=True, uploadseparate=True),

                Field("heading", label="Heading"),

                Field('completed', "boolean")

               )

The confusing thing for me is that the userid converts into a 'FullName' absolutely fine, but the projectid won't convert into a 'ProjectName'...

Any help greatly appreciated.

J

[Massimo Di Pierro]

there are two issues:

1) this

db(db.devtasklist.projectid == db.Project.id).select(db.Project.ProjectName)

does not do anything as it.

2) this

row.devtasklist.projectid

is an Project object but you do not access its attributes.

SOLUTION II (one extra query per record)

In views:

  <tr>

            <td>{{=row.devtasklist.projectid.ProjectName}}</td>

  </tr>

In controller:

def listdevtasklist():

    

    print_time = datetime.now()

    rows = db(db.devtasklist.userid == db.auth_user.id).select(orderby=db.auth_user.FullName)

    return locals()

SOLUTION II (build a map)

SOLUTION II (one extra query per record)

In views:

  <tr>

            <td>{{=names[row.devtasklist.projectid]}}</td>

  </tr>

In controller:

def listdevtasklist():

    

    print_time = datetime.now()

    rows = db(db.devtasklist.userid == db.auth_user.id).select(orderby=db.auth_user.FullName)

    ids = set(row.devtasklist.projectid for row in rows)

    projects = db(db.project.id.belongs(ids)).select(db.project.id, db.Project.ProjectName)

    names = {row.id: row.ProjectName for row in projects}

    return locals()