That is a difference between NoSQL and RDBS.
In web2py you can do it with one query+code but I would to it with
three queries and cache:
query = db.message.id>0
tags_d = db(db.tag.id>0).select(cache=(cache.ram,600)).as_dict()
message_d = db(query).select().as_dict()
links = db(db.tagging.message.belongs(message_d.keys()).select()
rows=[]
for link in links:
if rows and rows[-1][0].id==link.tagging.message:
tags=rows[-1][1]
else:
tags=[]
rows.append((message_d[link.tagging.message],tags))
if link.tagging.tag: tags.append(tags_d[link.tagging.tag])
for row in rows:
print 'message object:', row[0], 'list of tags', row[1]
On Aug 4, 4:38 am, David Marko <dma...@tiscali.cz> wrote:
> And how do you solve this in general? It seems to me very common
> thing. How to do it to minimalize database queries? (iIm not a SQL
> guy, my Job is Lotus Notes development) and recently I did project
> with MongoDB, which has quite different approach for this case)
>
> David
>
> On 4 srp, 11:29, mdipierro <mdipie...@cs.depaul.edu> wrote:
>
> > The problem is that there is no SQL query that allows you to return
> > something like this. Once you get
>
> > returns items in a way that gives me result like this:
> > 1. message text 01 (tags: tag01)
> > 2. message text 01 (tags: tag03)
> > 3. message text 02 (tags: tag03)
> > 4. message text 02 (tags: tag05)
> > 5. message text 02 (tags: tag06)
>
> > you have to group them into message + list of tags programmatically
>
> > On Aug 4, 4:25 am, David Marko <dma...@tiscali.cz> wrote:
>
> > > How to define DAL query to retrieve items and theirs tags? I have
> > > following tables:
>
> > > 'message' <--> 'tagging'(maty-to-many between message and tag tables)
> > > <---> 'tag'
>
> > > How to retrieve list of all mesages and their assotiated tags so I can
> > > list messages in a view like this:
> > > 1. message text 01 (tags: tag01, tag03)
> > > 2. message text 02 (tags: tag03, tag05, tag06)
> > > etc.
>
> > > I looked to web2py book to 'One to Many Relation' examples but they
> > > returns items in a way that gives me result like this:
> > > 1. message text 01 (tags: tag01)
> > > 2. message text 01 (tags: tag03)
> > > 3. message text 02 (tags: tag03)
> > > 4. message text 02 (tags: tag05)
> > > 5. message text 02 (tags: tag06)
>
> > > There must be something I missed.
> > > David
