CODEWITHSUNDEEP
databasejoinweb2py
Valentin H
Valentin H

Reputation: 7448

Double-JOIN in web2py. How to apply this SQL in web2py?

In my image-labeling app I want to display a list of labels assigned to an image along with lable-type and user information, who assigned this lable. The main table is imageLabel. The tables auth_user and labelType contain additional information to the label.

Could you help me converting this SQL-statement to web2py:

SELECT labelType.name, imageLabel.labelValue, auth_user.first_name, auth_user.last_name  from imageLabel
LEFT JOIN labelType 
ON imageLabel.labelId = labelType.id
LEFT JOIN auth_user
ON imageLabel.userId = auth_user.id
WHERE imageLabel.imageId = 581
ORDER BY labelType.name

The data model is like this:

db.define_table('labelType',
    Field('name','string'),
)
db.define_table('imageLabel',
    Field('imageId','string'),
    Field('labelId','integer'),
    Field('userId','integer'), 
    Field('labelValue','text'),
)
db.define_table('image',
    Field('imageId','string')
)
# + built-in auth tables

My attempt was:

labels = db((db.imageLabel.id == db.labelType.id) ).select( 
        db.imageLabel.labelValue, db.labelType.name, db.auth_user.first_name, db.auth_user.last_name, db.imageLabel.labelTimeStamp, 
        left=db.auth_user.on(db.imageLabel.userId==db.auth_user.id)
        )

Which abviously doesn't work as there is no WHERE imageLabel.imageId = 581 part in the code. And I can't figure out how to use WHERE along with 2 "JOINS" in web2py :-(

Thank you very much in advance for any help!

EDIT: SOLUTION After reading answer from Anthony:

labels = db(
    db.imageLabel.imageId == img.id
).select(
    db.imageLabel.labelValue,
    db.labelType.name,
    db.auth_user.first_name,
    db.auth_user.last_name,
    db.imageLabel.labelTimeStamp,
    left=[
        db.labelType.on(db.imageLabel.labelId == db.labelType.id),
        db.auth_user.on(db.imageLabel.userId==db.auth_user.id)
    ],
    orderby=~db.labelType.name
)

Upvotes: 1

Views: 3784

Answers (1)

Anthony
Anthony

Reputation: 25536

In general, a select looks like db(query).select(...). The query part of that represents the WHERE clauses. If you have multiple WHERE clauses, you just use the & operator:

db((condition 1) & (condition 2) & ...)

As for left joins, the left argument of the .select() method can be a list if you need to specify multiple left joins:

left=[db.auth_user.on(db.imageLabel.userId==db.auth_user.id),
      db.labelType.on(db.imageLabel.labelId==db.labelType.id)]

However, it's not clear that you really want left joins here -- you might just want inner joins (which can be specified using the join argument to the .select() method, or more simply as conditions in the query):

db((db.imageLabel.labelId == db.labelType.id) &  # joins imageLabel and labelType
   (db.imageLabel.userId == db.auth_user.id) &  # joins imageLabel and auth_user
   (db.imageLabel.imageId == 581))\
  .select(db.imageLabel.labelValue, db.labelType.name, db.auth_user.first_name,
          db.auth_user.last_name, db.imageLabel.labelTimeStamp)

Also, you should probably specify the three "Id" fields as reference type fields:

db.define_table('imageLabel',
    Field('imageId', 'reference image'),
    Field('labelId', 'reference labelType'),
    Field('userId', 'reference auth_user'),
    Field('labelValue', 'text'),
)

Finally, why do you need an imageId string field? The db.image table will already have an auto-incrementing integer "id" field to serve as a primary key for the table and uniquely identify each image.

Upvotes: 5

Related Questions