db query problem

[Robert Marklund]

I would like to do a join but get results even if it was unsucessfull.

Like this

query = (db.images.id == db.image_info.image_id)  & (db.images.id==db.image_keyword.image_id) & \
              (db.keyword.id==db.image_keyword.keyword_id) 

I want to get the resulting rows even doe no keywords exists.
like this row.image = the image
row.image_info = the image info
row.keyword = None if there is no keyword.

Is this possible ?

/R

--
______________________________________________
Robert Marklund

Phone: +46 (0)46 19 36 82
Mobile: +46 (0)70 213 22 76
E-mail: robbel...@gmail.com
______________________________________________

[Fran]

On Jun 25, 1:51 pm, Robert Marklund <robbelibob...@gmail.com> wrote:
> I would like to do a join but get results even if it was unsucessfull.
> Like this
> query = (db.images.id == db.image_info.image_id)  &
> (db.images.id==db.image_keyword.image_id)
> & \
>               (db.keyword.id==db.image_keyword.keyword_id)
> I want to get the resulting rows even doe no keywords exists.
> like this row.image = the image
> row.image_info = the image info
> row.keyword = None if there is no keyword.
> Is this possible ?

Surely you just do 2 queries.
1st do the JOIN:

query = (db.images.id == db.image_info.image_id) &
(db.images.id==db.image_keyword.image_id)

Then add keywords via lookups:
sqlrows = db(query).select()
for row in sqlrows:
query2 = (db.image_keyword.image_id==row.id) &
(db.keyword.id==db.image_keyword.keyword_id)
row.keyword = db(query2).select()[0]

(Untested code)

F

[Johann.Sch...@easytouch-edv.com]

Maybe I'm wrong but I think Robert needs a left join. something like...

result = db(db.images.id==db.image_info.image_id).select(\
        left=[db.keyword.on(db.keyword.id==db.image_keyword.keyword_id)])

[Trollkarlen]

that didnt do it.

And i would like to doit with one query if possible.

my db looks like this:

db.define_table('images',
SQLField('posted_on','datetime', default=request.now,
writable=False, readable=False),
SQLField('posted_by', db.auth_user, writable=False,
readable=False),
SQLField('public', 'string', requires=IS_IN_SET({'no': T('No'),
'yes': T('Yes')}), default="no"),
SQLField('sha256', 'string', writable=False, readable=False),
SQLField('org_filename','string', writable=False,
readable=False),
SQLField('image','upload'))

db.define_table('keyword',
SQLField('keyword', length=40))


db.define_table('image_info',
SQLField('image_id', db.images),
SQLField('Caption'),
SQLField('Description','text'),
SQLField('Creator','string'),
SQLField('Copyright_Notice','string'),
)


db.define_table('image_keyword',
SQLField('image_id', db.images),
SQLField('keyword_id', db.keyword));

[Johann.Sch...@easytouch-edv.com]

out of my head the following should work

result = db(db.images.id>0).select(\
        left=[db.image_info.on(db.image_info.image_id==db.images.id),\
               db.image_keyword.on(db.image_keyword.image_id==db.images.id),\
                   db.keyword.on(db.keyword.id==db.image_keyword.keyword_id)])

Hans

[Trollkarlen]

Thx it worked like a charm.

/R