Thanks guys for the assistance.
This is my testing code...
#controller
def show():
discussion = db((db.discussion.id==request.args(0)) &
(db.discussion.created_by==db.auth_user.id)).select().first()
replies = db((db.replies.discussion_id==request.args(0)) &
(db.replies.created_by==db.auth_user.id)).select()
return dict(discussion=discussion,replies=replies)
#view
{{for d in discussion:}}
<h1>{{=d.discussion.title}}</h1>
<div>{{=d.discussion.description}} - {{=d.auth_user.first_name}}</div>
{{pass}}
{{for r in replies:}}
<div class="reply">{{=r.replies.reply}} - {{=r.auth_user.first_name}}</
div>
{{pass}}
On Dec 1, 9:38 pm, Anthony <[email protected]> wrote:
> A simple join with a small amount of data probably shouldn't be taking
> several seconds, so perhaps something else is going on here. Can you post
> your code and data?
>
> Recursive selects will do a query for each record, so you should only use
> them if you need to extract just one or a few records. Otherwise, a join
> should be better.
>
> Anthony
>
>
>
>
>
>
>
> On Thursday, December 1, 2011 2:36:49 AM UTC-5, lyn2py wrote:
>
> > Appreciate your input on this guys :)
>
> > I have two tables that are related.
> > >> db.define_table('person', Field('name'))
> > >> db.define_table('dog', Field('name'), Field('owner', db.person))
>
> > According to the book:
> >http://web2py.com/book/default/chapter/06#Inner-Joins
>
> > I can JOIN:
> > >> rows = db(db.person.id==db.dog.owner).select()
> > >> rows = db(db.person).select(join=db.person.on(db.person.id
> > ==db.dog.owner))
>
> > I can recursive SELECT:
> > >> dog.owner.name
>
> > And recursive SELECT is supposed to be slower than JOIN, because there
> > are more database transactions?
> > I tried both methods out (disclaimer: I do not have many testing
> > entries in the database, and I'm currently observing this on localhost/
> > sqlite) and I have found JOIN to be slower, I was literally waiting
> > for the page to load, watching the loading bar... a few seconds.
> > But recursive SELECT loads the page in a snap.
>
> > Is this correct behavior?
> > Should I use recursive select for few entries (less than, say, 30) and
> > JOIN for many?
> > If I move the site to production, what database(s) would you
> > recommend, and for which method (recursive select vs join)?
>
> > Thanks!
