Your join is performing a union, your queries could (and will) get
quite huge, since your getting a record for every tag. (so for two
tags you actually get two rows for that snippet). A left join will
provide you with everything, but you still get a row for every tag.
Even with a left join you will still have to query for the title of the tag
>>> rows = db().select(db.snippets.ALL, db.snippet_tags_link.ALL,
>>> left=db.snippet_tags_link.on(db.snippets.id ==
>>> db.snippet_tags_link.snippet_id))
>>> print rows
snippets.id,snippets.title,snippets.content,snippets.created,snippet_tags_link.id,snippet_tags_link.snippet_id,snippet_tags_link.tag_id
1,hi,hello world,2010-04-05 09:21:20,1,1,1
1,hi,hello world,2010-04-05 09:21:20,2,1,1
2,woot,erifica,2010-04-05 09:21:20,<NULL>,<NULL>,<NULL>
Or you can do a secondary query to get the tags.
snippets = db().select()
for snip in snippets:
print snippet.title
for tag in snip.snippet_tags_link.select():
print tag.snippet_tags.name
-Thadeus
On Mon, Apr 5, 2010 at 8:46 AM, Ishbir <ishbi...@gmail.com> wrote:
> I've got some problem with the MANY-MANY relation in Web2Py. This is
> my code-
>
> http://pastie.textmate.org/903738
>
> Now, the problem is that the query returns only those records which
> have tags, not the ones without them. However, if I specify query|
> db.snippets.id>0 or anything, it gives me garbled results with all the
> tables mixed up and nothing right..
>
> Is there any way around this? I thought about using the tagging plugin
> but the issue is that I don't think it would be very efficient since I
> need to also display the tags on the index page and that would result
> in n+1 queries where n is the no. of snippets. Or I am just getting
> apprehensive and it will just take 2-3 queries to get the tags of
> snippet?
>
> --
> You received this message because you are subscribed to the Google Groups
> "web2py-users" group.
> To post to this group, send email to web...@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.
>
>
--
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To post to this group, send email to web...@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.
