Thank you Anthony, I have given it a thought and I will create another table called "status".
The "message" table contains the fields : "id" and "to" among others. The "status" table contains the fields : "id", "message_id", "person" and "status" among others. "to" is readable and writable. "message_id", "person" and "status" are non readable and non writable fields. I think that what I need is one form for multiple tables : http://web2py.com/book/default/chapter/07#One-form-for-multiple-tables Here are two rows of the "message" table (without all the fields) : id / to 1 / steve,jimmy 2 / julia Here are the corresponding rows of the "status" table (without all the fields) : id / message_id / person / status 1 / 1 / steve / 0 2 / 1 / jimmy / 0 3 / 2 / julia / 0 The "message_id" field in the "status" table refers to the "id" in the "message" table. Here is what I have tried in the controller : form = SQLFORM.factory(db.message,db.status) if form.process().accepted: id = db.message.insert(**db.message._filter_fields(form.vars)) form.vars.message_id = id for to in form.vars.to: form.vars.person = to id = db.status.insert(**db.status._filter_fields(form.vars)) Unfortunately the "to" field of the "message" table remains empty. And an empty row is inserted in the "status" table. Where does the problem come from ? Could there be a problem with the "_filter_fields" method when it deals with a "list:reference" field ? I am just giving it a go ;) Thanks a lot, Archibald On 26 oct, 23:57, Anthony <abasta...@gmail.com> wrote: > That's what I was thinking. list: type fields are good if you just need to > store a list of things associated with a given record and retrieve the list > when the record is retrieved, but they aren't necessarily easy or efficient > for querying the data (depending on the application). If you really want to > stick with the list: fields, I suppose you could do a select to get the > Julia records, and then use some Python code to further filter the records > based on status. You might also be able to create either a computed or > virtual field that concatenates name and status into a new list, and query > that. Depending on how many records you're dealing with, though, it might > be more efficient to go with a more normalized data structure. > > Anthony > > > > On Wednesday, October 26, 2011 5:38:41 PM UTC-4, Archibald Linx wrote: > > > Dear Anthony, > > > I have asked the question on Stackoverflow and it seems it is a bad > > database structure ;) Sorry. > > > See :http://stackoverflow.com/questions/7908024/sql-query-list-fields > > > I will put the status information somewhere else. Maybe in a separate > > database. I don't know yet. > > > Thanks for the help you gave me, > > Archibald > > > On 26 oct, 20:35, Archibald Linx <archib...@gmail.com> wrote: > > > Thank you Anthony. > > > > I don't know about the raw SQL query. I will ask on Stackoverflow and > > > post the link here. > > > > Best, > > > Archibald > > > > On 26 oct, 19:07, Anthony <abas...@gmail.com> wrote: > > > > > On Wednesday, October 26, 2011 12:21:33 PM UTC-4, Archibald Linx > > wrote: > > > > > > Thank you Anthony ! > > > > > > Is the length "len" always defined in Python ? > > > > > No, I think the len() function will fail if you pass None to it, so if > > you > > > > were using request.vars, you'd want something like: > > > > > default=len(request.vars.to) if request.vars.to is not None else > > [whatever > > > > you want the default to be otherwise] > > > > > > I couldn't find much tools in the documentation to query lists of > > > > > references apart from the "contains" operator. > > > > > > For example, let's have the following "message" table : > > > > > id / to / status > > > > > 1 / steve,jimmy / 0,2 > > > > > 2 / john,julia / 1,2 > > > > > 3 / julia,peggy / 0,1 > > > > > > I want to get the rows where "Julia" is in "to" and where her status > > > > > is "0" (in this particular case, that is row n°3). > > > > > With the "contains" operator I only know how to get the rows where > > > > > "Julia" is in "to" (that is row n°2 and n°3). > > > > > > Should I write raw SQL ? > > > > > How would you write it in raw SQL?- Masquer le texte des messages > > > > précédents - > > - Afficher le texte des messages précédents -
