You should read at : http://web2py.com/book/default/chapter/06?search=left+join#Left-Outer-Join
You need left join... Write your query at in raw SQL then write it in web2py... If you can't write the proper query in raw SQL you maybe have a wrong database schema... You are trying to do something liket this : SELECT blogposts.title, youtube.title FROM blogposts LEFT OUTER JOIN youtube ON (blog.blog_id = blogposts.id) You will have more then one row with this query if you have more then one youtube video title linked to your blogpost record... You maybe want to aggregate... Richard On Tue, Jul 26, 2011 at 11:16 AM, Web2Py Freak <[email protected]>wrote: > am losing my mind ... i am making the blogs i told u about , but with > images and videos .. but every post gets all the images and > videos .. whyyyyy :(( :: like this > > > here is my Code please guys help me . am going crazy > > > > ................................................................................ > db.py: > import datetime > now=datetime.datetime.today() > > > > db.define_table('blogposts', > SQLField('title', length=64), > SQLField('author', default=session.username), > SQLField('datetime', 'datetime',default=now), > SQLField('post', 'text'), > SQLField('numcomments', 'integer',default=0)) > > > > > > db.blogposts.title.requires = [IS_NOT_EMPTY(), > IS_NOT_IN_DB(db,db.blogposts.title)] > db.blogposts.author.requires = IS_NOT_EMPTY() > db.blogposts.post.requires = IS_NOT_EMPTY() > > db.define_table('blogcomments', > SQLField('url', default=''), > SQLField('blogpost_id'), # the blogpost this comment > belongs to > SQLField('email', default='[email protected]'), > SQLField('author'), > SQLField('datetime', 'datetime',default=now), > SQLField('comment', 'text')) > > db.blogcomments.author.requires = IS_NOT_EMPTY() > db.blogcomments.comment.requires = IS_NOT_EMPTY() > db.blogcomments.email.requires = IS_EMAIL() > > > db.define_table('image',Field('blog_id',db.blogposts),Field('title'),Field('discription','text'),Field('image','upload')) > > db.image.title.requires = IS_NOT_IN_DB(db, db.image.title) > db.image.blog_id.requires = IS_IN_DB(db, db.blogposts.id, '% > (title)s') > > > db.define_table('youtube',Field('blog_id',db.blogposts),Field('title'),Field('discription','text'),Field('code')) > db.youtube.title.requires = IS_NOT_IN_DB(db, db.youtube.title) > db.youtube.blog_id.requires = IS_IN_DB(db, db.blogposts.id, '% > (title)s') > > > db.define_table('vimo',Field('blog_id',db.blogposts),Field('title'),Field('discription','text'),Field('code')) > db.vimo.title.requires = IS_NOT_IN_DB(db, db.vimo.title) > db.vimo.blog_id.requires = IS_IN_DB(db, db.blogposts.id, '%(title)s') > > > > ............................................................................................................................ > controler: > # -*- coding: utf-8 -*- > # this file is released under public domain and you can use without > limitations > > ######################################################################### > import datetime > > def getpostsandcomments(): > > blogposts = db().select(db.blogposts.ALL, > orderby=~db.blogposts.datetime) > blogposts_numcomments = db().select(db.blogposts.ALL, > orderby=~db.blogposts.numcomments|~db.blogposts.datetime) > commentsnposts = db(db.blogcomments.blogpost_id == > db.blogposts.id) > comments = commentsnposts.select(db.blogcomments.author, > db.blogcomments.blogpost_id, db.blogposts.title, > orderby=~db.blogcomments.datetime) > images=db(db.image.blog_id==db.blogposts.id).select(db.image.ALL) > > youtube=db(db.youtube.blog_id==db.blogposts.id).select(db.youtube.ALL) > vimo=db(db.vimo.blog_id==db.blogposts.id).select(db.vimo.ALL) > return dict(blogposts=blogposts, > blogposts_numcomments=blogposts_numcomments, > comments=comments,images=images,youtube=youtube,vimo=vimo) > > ######################################################################### > def createblogpost(): > > myd = getpostsandcomments() > > form=SQLFORM(db.blogposts,fields=['title','post','author','video','audio']) > if form.accepts(request.vars,session): > response.flash='new blogpost inserted' > redirect('/' + request.application + "/default/index") > myd['form'] = form > return myd > > def index(): > > return getpostsandcomments() > > def createblogpost(): > > myd = getpostsandcomments() > form=SQLFORM(db.blogposts,fields=['title','post','author']) > if form.accepts(request.vars,session): > response.flash='new blogpost inserted' > redirect('/' + request.application + "/default/index") > myd['form'] = form > return myd > > > > def showblogpost(): > images=db(db.image.blog_id == > db.blogposts.id).select(db.image.ALL) > youtube=db(db.youtube.blog_id == > db.blogposts.id).select(db.youtube.ALL) > vimo=db(db.vimo.blog_id == db.blogposts.id).select(db.vimo.ALL) > myd = getpostsandcomments() > > blogid = request.args[0] > singleblogpost = db(db.blogposts.id == blogid).select()[0] > singleblogpost_comments = db(db.blogcomments.blogpost_id == > blogid).select(orderby=~db.blogcomments.datetime) > > db.blogcomments.blogpost_id.default=singleblogpost.id > form=SQLFORM(db.blogcomments,fields=['author', 'email', > 'comment'],labels={'author':'Your Name', 'email':'Your email address', > 'comment':'Comment'}) > if form.accepts(request.vars,session): > blogpostupdate=db(db.blogposts.id==blogid).select() > if len(blogpostupdate)>0: > > blogpostupdate[0].update_record(numcomments=blogpostupdate[0].numcomments > + 1) > redirect('/' + request.application + "/default/showblogpost/" > + str(singleblogpost.id) + "#Reader_Comments") > > myd['form'] = form > myd['singleblogpost'] = singleblogpost > myd['singleblogpost_comments'] = singleblogpost_comments > return myd > return dict(images=images,youtube=youtube,vimo=vimo) > > def user(): > """ > exposes: > http://..../[app]/default/user/login > http://..../[app]/default/user/logout > http://..../[app]/default/user/register > http://..../[app]/default/user/profile > http://..../[app]/default/user/retrieve_password > http://..../[app]/default/user/change_password > use @auth.requires_login() > @auth.requires_membership('group name') > @auth.requires_permission('read','table name',record_id) > to decorate functions that need access control > """ > return dict(form=auth()) > > > def download(): > """ > allows downloading of uploaded files > http://..../[app]/default/download/[filename] > """ > return response.download(request,db) > > > def call(): > """ > exposes services. for example: > http://..../[app]/default/call/jsonrpc > decorate with @services.jsonrpc the functions to expose > supports xml, json, xmlrpc, jsonrpc, amfrpc, rss, csv > """ > return service() > > > @auth.requires_signature() > def data(): > """ > http://..../[app]/default/data/tables > http://..../[app]/default/data/create/[table] > http://..../[app]/default/data/read/[table]/[id] > http://..../[app]/default/data/update/[table]/[id] > http://..../[app]/default/data/delete/[table]/[id[ > http://..../[app]/default/data/select/[table] > http://..../[app]/default/data/search/[table] > but URLs bust be signed, i.e. linked with > A('table',_href=URL('data/tables',user_signature=True)) > or with the signed load operator > > LOAD('default','data.load',args='tables',ajax=True,user_signature=True) > """ > return dict(form=crud()) > > > > ..................................................................................................................................... > index.html : > > {{extend 'layout.html'}} > > {{#=crud.create(db.image)}} > <div id="subContent"> > <h3>Most Commented On</h3> > <ul> > {{for blogpost in blogposts_numcomments:}} > <li class="statsclass1"><a href={{="/" + request.application > + "/default/showblogpost/" + str(blogpost.id)}}>{{=blogpost.title}}</ > a> ({{=blogpost.numcomments}})</li> > {{pass}} > </ul> > <p /> > <h3>Recent Comments</h3> > <ul> > {{for comment in comments:}} > <li class="statsclass1"><a href={{="/" + request.application > + "/default/showblogpost/" + str(comment.blogcomments.blogpost_id) + > "#Reader_Comments"}}>{{=comment.blogcomments.author}}</a> on <a > href={{="/" + request.application + "/default/showblogpost/" + > str(comment.blogcomments.blogpost_id)}}>{{=comment.blogposts.title}}</ > a></li> > {{pass}} > </ul> > </div> > > {{for blogpost in blogposts:}} > <div class="article" > > <h2>{{=blogpost.title}}</h2> > <i><span style="color : #666666;">{{=blogpost.datetime}}</ > span></i> > <div style="margin-left:200px"> <p><b> > > {{=XML(blogpost.post)}} > </p></b></div> > > {{for photo in images:}} > <div style="width:200px ; margin-left:200px"> > <div style="width:200px;margin-left:50px"> > <p><b>{{=XML(photo.title)}}</b></p> > </div> > <img width="200px" > src="{{=URL('download', args=photo.image)}}" /> > <div style="width:200px;margin-left:50px"> > <p>{{=XML(photo.discription)}}</p> > </div> > <div> > {{pass}} > </br> > {{for you in youtube:}} > <div style="width:200px;margin-left:150px"> > <p><b>{{=XML(you.title)}}</b></p> > </div> > {{=plugin_wiki.widget('youtube',code='' + XML(you.code))}} > <div style="width:200px;margin-left:150px"> > <p>{{=XML(you.discription)}}</p> > </div> > {{pass}} > > {{for v in vimo:}} > <div style="width:200px;margin-left:150px"> > <p><b>{{=XML(v.title)}}</b></p> > </div> > {{=plugin_wiki.widget('vimeo',code='' + XML(v.code))}} > <div style="width:200px;margin-left:150px"> > <p>{{=XML(v.discription)}}</p> > </div> > {{pass}} > > > > <ul class="comments"> > <li><a href="http://www.facebook.com";><img src="/ > AZEZ_BLOGS/static/images/Facebook-logo-100x100.png" width="40px" > height="40px" ></img></a></li> > <li><a href="http://www.vimeo.com";><img src="/AZEZ_BLOGS/ > static/images/th_vimeo-icon.png" width="40px" height="40px" ></img></ > a></li> > <li><a href="http://www.youtube.com";><img src="/AZEZ_BLOGS/ > static/images/youtube-icon.png" width="40px" height="40px" ></img></ > a></li> > <li>Posted by <a > href={{='mailto:[email protected]'}}>{{=blogpost.author}}</a> | </ > li> > <li><a href={{="/" + request.application + "/default/ > showblogpost/" + str(blogpost.id) + "#Reader_Comments"}} > >{{=blogpost.numcomments}}</a> comments | </li> > <li><a href={{="/" + request.application + "/default/ > showblogpost/" + str(blogpost.id)}}>permalink</a></li> > </ul> > </div> > {{pass}} >
