Hi Bart I'd take a look at whether you are producing the correct SQL. Try placing an underscore in front of your select to expose in the commandline console whether the SQL is what you want. See the underscore here:
print db(db.topics.id== request.args(0))._select(db.topics.title, db.topics.content,db.replys.content,left=db.topics.on(db.topics.id==db.replys.topic_id)) You can then play around with the query until you get it right. Sorry I'm not answering your question directly but it's often better to show a man how to fish :-) -D On Feb 8, 8:55 am, Bart <[email protected]> wrote: > Hi all, > > Just starting with Web2py and i feel like a kid in a play garden. ;) > > Ok my problem, i am trying to make a simple JOIN but when i do that i > have a problem with my views. > My controlle looks like this: > > def read(): > > thispage = db.topics[request.args(0)] > > if not thispage: > redirect(URL(r=request, f='read')) > > if auth.user_id: > form = form = SQLFORM(db.replys) > form.vars.topic_id = request.args(0) > form.vars.user_id = auth.user_id > if form.accepts(request.vars, session): > response.flash = 'Topic created!' > else: > form = 'need to login to reply' > > db(db.topics.id==request.args(0)).select()[0] > #topics = db(db.topics.id==request.args(0)).select(db.topics.id== > db.replys.topic_id) > #replys = > db(db.replys.topic_id==request.args(0)).select(db.replys.ALL, > orderby=~db.replys.posted) > #query = (db.topics.id==db.replys.topic_id)&(db.topics.id == > request.args(0)) > rows= db(db.topics.id== request.args(0)).select(db.topics.title, > db.topics.content, > db.replys.content, > left=db.topics.on(db.topics.id==db.replys.topic_id)) > > return dict(rows=rows,form=form) > > My view looks like this: > > {{extend 'layout.html'}} > > {{for row in rows:}} > <h1> {{=row.topics.title}} </h1> > <p> {{=row.topics.content}}</p> > {{pass}} > > {{for row in rows:}} > <hr> > <p> {{=row.replys.content}}</p> > {{pass}} > > <b> {{=form}} </b> > When i have a few reply's then i have row.topics.titles and > row.topics.content as the same amount as the row.replys.content. > When i don't have a reply, then i don get the record back from topics. > > Now my question: How do i get one time the topics record back, and > when there is none replys just the the record of the topic? > > What am i missing at this point? > > Thanks for the help. > regards, Bart
