DAL db.table.field.contains

[BlueShadow]

Hi
I got two tables Books and Authors
Book(Name,List:Author,...)
each book has at least one author
now I want to have a list of all the books one Author has written.
So I came up with this line of code:

l=db(db.Books.Author.contains(Author.id)).select(db.Books.ALL, orderby=db:Books.Name)

which works but it only gives me one Book per Author.
I got no clue why this happens I know that I have several Authors which have more than one book.

[Richard Vézina]

Each book that refer to an Author will only has one Author what the problem there?

You may want to aggregate the book for one author in the same cell of a table that is another thing that you could address in different way... Most probably iter over all the book for an author and build manually a table with web2py helpers...

Richard

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups "web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Kevin Bethke]

each book has one author but I want to select all Books which have Author id=7

You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/bnP-qe7Nwwk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.

[Richard Vézina]

.contains() should do that...

Could you post model and controller, the issue is maybe somewhere else in your code.

Richard

[Richard Vézina]

Also, version of web2py and backend you use may be helpfull, if you found a bug...

Richard

[BlueShadow]

def Authors():
    AL=db().select(db.Author.ALL, orderby=db.Author.Name)
    TheList=[]
    for Author in AL:
        l=db(db.Book.Author.contains(Author.id)).select(db.Book.ALL, orderby=db.Book.Name)
        if len(l)>0:
            TheList.append([l[0]])
        else:
            TheList.append([])
    return dict(AL=AL,BookList=TheList)

here is my controller funktion
and my view:

<table>
{{i=0}}
{{for Author in AL:}}
<tr>
    <td>{{=Author.Name}}</td><td></td>
</tr>
    {{for b in BookList[i]:}}
    <tr>
        <td></td><td>{{=b.Name}}</td>
    </tr>
    {{pass}}
    {{i+=1}}
{{pass}}
</table>

its supposed to give an alphabetical list of all authors which it does.
and list all their books (only one is displayed)

[Richard Vézina]

Ok, figured out, I think, what happen is that you use index to get record in l (that actually is a pretty bad variable name since it could be confused with 1 - ONE with some fonts)... By using an index you always get only the first row in rows because your "l" variable is a rows object that contains many row(s)... So you only get the first one each time.

You don't have to make complicated code like so...

This should work better :

thead = THEAD(TR(TH(Author), TH(Books)))

tr = []

for Author in AL:

    rows = db(db.Book.Author.contains(Author.id)).select(db.Book.ALL, orderby=db.Book.Name)

    tr.append(TR(TD(Author.Name), TD([row.Name for row in rows])))

table = TABLE(thead, TBODY(tr))

return dict(table=table)

In your view :

{{=table}}

Richard

[Niphlod]

why contains instead of == ?
db(db.Book.Author == Author.id).select(db.Book.ALL, orderby=db.Book.Name)

[Richard Vézina]

I guess because his Author reference field is of type list:reference ?!

Richard

[Kevin Bethke]

thats why.

thanks richard for your solution it works perfectly.

[Richard Vézina]

:)

On Fri, Nov 22, 2013 at 10:20 AM, Kevin Bethke <kevin....@gmail.com> wrote:

thats why.

thanks richard for your solution it works perfectly.

--