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
web2py@googlegroups.com schrieb am 25.06.2009 18:19:54:
>
> 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));
>
>
>
> On Jun 25, 4:04 pm, johann.scheibelho...@easytouch-edv.com wrote:
> > 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)])
> >
> > web2py@googlegroups.com schrieb am 25.06.2009 15:19:50:
> >
> >
> >
> > > 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
> >
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"web2py Web Framework" group.
To post to this group, send email to web2py@googlegroups.com
To unsubscribe from this group, send email to
web2py+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en
-~----------~----~----~----~------~----~------~--~---
