Re: [web2py] Re: many to many fields representation
Thanks for the info. Best Regards Itamar Cohen Picatek Ahornvej 27 DK-7700 Thisted Denmark www.picatek.dk i...@picatek.dk T: +45 97 91 07 82 M:+45 29 89 07 82 On 22 August 2015 at 20:19, Anthony abasta...@gmail.com wrote: Yes, I forgot to mention, the dropdowns in the forms are based in the IS_IN_DB validator attached to each field. So, you will also have to overwrite the requires attribute of these two fields with a custom IS_IN_BD validator. Anthony -- 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 a topic in the Google Groups web2py-users group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/rgtfBjm_S38/unsubscribe. To unsubscribe from this group and all its topics, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout. -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] Re: Many to many to many?
Thank you Massimo that works perfectly. Il giorno domenica 23 agosto 2015 02:16:04 UTC+2, Massimo Di Pierro ha scritto: Should'd that be override_code = override[k].ceo_schemes_and_overrides_and_override_codes. override_code.code1 anyway this should be faster (no nested queries) override = db((db.ceo_schemes_and_overrides_and_override_codes.ceo_scheme == ceo_scheme.id) (db.ceo_schemes_and_overrides_and_override_codes. override == override_id)).select(left=db.override_code.on(db. ceo_schemes_and_overrides_and_override_codes.override_code== db.override_code.id)) override_code = override[k].override_code.code1 On Saturday, 22 August 2015 07:41:35 UTC-5, Gael Princivalle wrote: Hello. I've make a many to many to many relationship but I've got problems on select results. Is it the correct way to do it? My DB: db.define_table('ceo_schemes', Field('name', type='string', requires=IS_NOT_EMPTY()), format='%(name)s') db.define_table('overrides', Field('name', type='string', requires=IS_NOT_EMPTY()), format='%(name)s') db.define_table('override_codes', Field('code1', type='string'), Field('code2', type='string'), format='%(code1)s %(code2)s') db.define_table('ceo_schemes_and_overrides_and_override_codes', Field('ceo_scheme', 'reference ceo_schemes'), Field('override', 'reference overrides'), Field('override_code', 'reference override_codes')) My controller: override = db((db.ceo_schemes_and_overrides_and_override_codes.ceo_scheme == ceo_scheme.id) (db.ceo_schemes_and_overrides_and_override_codes. override == override_id)).select() In my view I can see this result with BEAUTIFY(response._vars): override:ceo_schemes_and_overrides_and_override_codes.id ceo_schemes_and_overrides_and_override_codes.ceo_scheme ceo_schemes_and_overrides_and_override_codes.override ceo_schemes_and_overrides_and_override_codes.override_code302_D Without20 But in the controller if I call: override_code = override.ceo_schemes_and_overrides_and_override_codes. override_code.code1 The ticket is : 'Rows' object has no attribute 'ceo_schemes_and_overrides_and_override_codes' Someone knows why? Is is the way that I've done the 3 tables relations ship that's wrong? Thanks. -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] Re: Many to many to many?
Should'd that be override_code = override[k].ceo_schemes_and_overrides_and_override_codes. override_code.code1 anyway this should be faster (no nested queries) override = db((db.ceo_schemes_and_overrides_and_override_codes.ceo_scheme == ceo_scheme.id) (db.ceo_schemes_and_overrides_and_override_codes.override == override_id)).select(left=db.override_code.on(db. ceo_schemes_and_overrides_and_override_codes. override_code==db.override_code.id)) override_code = override[k].override_code.code1 On Saturday, 22 August 2015 07:41:35 UTC-5, Gael Princivalle wrote: Hello. I've make a many to many to many relationship but I've got problems on select results. Is it the correct way to do it? My DB: db.define_table('ceo_schemes', Field('name', type='string', requires=IS_NOT_EMPTY()), format='%(name)s') db.define_table('overrides', Field('name', type='string', requires=IS_NOT_EMPTY()), format='%(name)s') db.define_table('override_codes', Field('code1', type='string'), Field('code2', type='string'), format='%(code1)s %(code2)s') db.define_table('ceo_schemes_and_overrides_and_override_codes', Field('ceo_scheme', 'reference ceo_schemes'), Field('override', 'reference overrides'), Field('override_code', 'reference override_codes')) My controller: override = db((db.ceo_schemes_and_overrides_and_override_codes.ceo_scheme == ceo_scheme.id) (db.ceo_schemes_and_overrides_and_override_codes. override == override_id)).select() In my view I can see this result with BEAUTIFY(response._vars): override:ceo_schemes_and_overrides_and_override_codes.id ceo_schemes_and_overrides_and_override_codes.ceo_scheme ceo_schemes_and_overrides_and_override_codes.override ceo_schemes_and_overrides_and_override_codes.override_code302_DWithout 20 But in the controller if I call: override_code = override.ceo_schemes_and_overrides_and_override_codes. override_code.code1 The ticket is : 'Rows' object has no attribute 'ceo_schemes_and_overrides_and_override_codes' Someone knows why? Is is the way that I've done the 3 tables relations ship that's wrong? Thanks. -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [web2py] Re: many to many fields representation
Yes, I forgot to mention, the dropdowns in the forms are based in the IS_IN_DB validator attached to each field. So, you will also have to overwrite the requires attribute of these two fields with a custom IS_IN_BD validator. Anthony -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] Re: many to many fields representation
By default, the represent attribute of a reference field is based on the format attribute of the table it references. So, you have two options -- you could either change the format attributes of the auth_user and auth_group tables: db.auth_user._format = '%(first_name)s %(last_name)s' # notice the id field is excluded or you could directly change the represent attributes of the auth_membership.user_id and auth_membership.group_id fields: db.auth_membership.user_id.represent = lambda id, row: '%(first_name)s %(last_name)s' % db.auth_user(id) Note, if you exclude the id field from the representation, two users with the same name will appear identically in the list (that's why the default representation includes the id -- in order to ensure a unique set of values). Anthony On Saturday, August 22, 2015 at 7:11:12 AM UTC-4, icodk wrote: I made a form for editing the auth_membership table using smart grid, it works fine. The only issue I have is that the id of the user_id and the id of the group_id are appended to the user name and to the group name. How can I get rid of this 'extra' addition ? For example the user John Smith is shown as: John Smith(15) both in the create/edit and in the grid The group named Teachers is shown as Teachers(4) -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [web2py] Re: many to many fields representation
Thanks Anthony, It works (both solutions) in the grid but not in the edit/new forms you get when clicking on the auth_memebership link in the grid (the id still appended to the name/role. May be it is OK as you mentioned regarding uniqueness. Best Regards Itamar Cohen Picatek Ahornvej 27 DK-7700 Thisted Denmark www.picatek.dk i...@picatek.dk T: +45 97 91 07 82 M:+45 29 89 07 82 On 22 August 2015 at 17:33, Anthony abasta...@gmail.com wrote: By default, the represent attribute of a reference field is based on the format attribute of the table it references. So, you have two options -- you could either change the format attributes of the auth_user and auth_group tables: db.auth_user._format = '%(first_name)s %(last_name)s' # notice the id field is excluded or you could directly change the represent attributes of the auth_membership.user_id and auth_membership.group_id fields: db.auth_membership.user_id.represent = lambda id, row: '%(first_name)s %(last_name)s' % db.auth_user(id) Note, if you exclude the id field from the representation, two users with the same name will appear identically in the list (that's why the default representation includes the id -- in order to ensure a unique set of values). Anthony On Saturday, August 22, 2015 at 7:11:12 AM UTC-4, icodk wrote: I made a form for editing the auth_membership table using smart grid, it works fine. The only issue I have is that the id of the user_id and the id of the group_id are appended to the user name and to the group name. How can I get rid of this 'extra' addition ? For example the user John Smith is shown as: John Smith(15) both in the create/edit and in the grid The group named Teachers is shown as Teachers(4) -- 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 a topic in the Google Groups web2py-users group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/rgtfBjm_S38/unsubscribe. To unsubscribe from this group and all its topics, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout. -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [web2py] Re: many to many with SQLFORM.grid
Thanks, so the use of field_id is important! I tried your first test which can be written with the proper field_id *m.id.* This Works: def test1(): id_company = 1 m, cm = db.meeting, db.co_meet q = cm.ref_company == id_company q = q (m.id == cm.ref_meeting) grid = SQLFORM.grid(q, field_id=m.id http://cm.id/, fields=[m.title]) return locals() On Sunday, May 31, 2015 at 2:34:21 AM UTC-7, Massimiliano wrote: Sorry. What I wrote is wrong. Try this ways: def test1(): id_company = 1 m, cm = db.meeting, db.co_meet cm.ref_company.default = id_company cm.ref_company.readable = cm.ref_company.writable = False cm.ref_meeting.requires = IS_IN_DB(db, m.id ,'%(title)s') q = cm.ref_company == id_company q = q (m.id == cm.ref_meeting) grid = SQLFORM.grid(q, field_id=cm.id, fields=[m.title]) return locals() def test2(): id_company = 1 m, cm = db.meeting, db.co_meet cm.ref_company.default = id_company cm.ref_company.readable = cm.ref_company.writable = False cm.ref_meeting.represent = lambda value, row: m[value].title cm.ref_meeting.requires = IS_IN_DB(db, m.id ,'%(title)s') q = cm.ref_company == id_company grid = SQLFORM.grid(q) return locals() On Sun, May 31, 2015 at 11:01 AM, Massimiliano mbel...@gmail.com javascript: wrote: m, cm = db.meeting, db.co_meet q = m.id == cm.ref_meeting q = q (cm.ref_company == 1) grid = SQLFORM.grid(q, field_id=m.id, fields=[m.id, m.title]) On Sun, May 31, 2015 at 4:48 AM, Ben Lawrence benla...@gmail.com javascript: wrote: Both of your answers give a grid of co_meet. What would the query be in SQLFORM.grid such that it would be a grid of db.meeting for one company (not db.co_meet)? On Thursday, May 7, 2015 at 8:26:47 PM UTC-7, 黄祥 wrote: had you tried it? another work around is u can use smartgrid constraints e.g. def test(): table = db.co_meet query = db.co_meet.ref_company == 1 # whatever value that refer to table company grid = SQLFORM.smartgrid(table, constraints = dict(co_meet=query) ) return locals() best regards, stifan -- 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 javascript:. For more options, visit https://groups.google.com/d/optout. -- Massimiliano -- Massimiliano -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [web2py] Re: many to many with SQLFORM.grid
Sorry. What I wrote is wrong. Try this ways: def test1(): id_company = 1 m, cm = db.meeting, db.co_meet cm.ref_company.default = id_company cm.ref_company.readable = cm.ref_company.writable = False cm.ref_meeting.requires = IS_IN_DB(db, m.id ,'%(title)s') q = cm.ref_company == id_company q = q (m.id == cm.ref_meeting) grid = SQLFORM.grid(q, field_id=cm.id, fields=[m.title]) return locals() def test2(): id_company = 1 m, cm = db.meeting, db.co_meet cm.ref_company.default = id_company cm.ref_company.readable = cm.ref_company.writable = False cm.ref_meeting.represent = lambda value, row: m[value].title cm.ref_meeting.requires = IS_IN_DB(db, m.id ,'%(title)s') q = cm.ref_company == id_company grid = SQLFORM.grid(q) return locals() On Sun, May 31, 2015 at 11:01 AM, Massimiliano mbelle...@gmail.com wrote: m, cm = db.meeting, db.co_meet q = m.id == cm.ref_meeting q = q (cm.ref_company == 1) grid = SQLFORM.grid(q, field_id=m.id, fields=[m.id, m.title]) On Sun, May 31, 2015 at 4:48 AM, Ben Lawrence benlawr...@gmail.com wrote: Both of your answers give a grid of co_meet. What would the query be in SQLFORM.grid such that it would be a grid of db.meeting for one company (not db.co_meet)? On Thursday, May 7, 2015 at 8:26:47 PM UTC-7, 黄祥 wrote: had you tried it? another work around is u can use smartgrid constraints e.g. def test(): table = db.co_meet query = db.co_meet.ref_company == 1 # whatever value that refer to table company grid = SQLFORM.smartgrid(table, constraints = dict(co_meet=query) ) return locals() best regards, stifan -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout. -- Massimiliano -- Massimiliano -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [web2py] Re: many to many with SQLFORM.grid
m, cm = db.meeting, db.co_meet q = m.id == cm.ref_meeting q = q (cm.ref_company == 1) grid = SQLFORM.grid(q, field_id=m.id, fields=[m.id, m.title]) On Sun, May 31, 2015 at 4:48 AM, Ben Lawrence benlawr...@gmail.com wrote: Both of your answers give a grid of co_meet. What would the query be in SQLFORM.grid such that it would be a grid of db.meeting for one company (not db.co_meet)? On Thursday, May 7, 2015 at 8:26:47 PM UTC-7, 黄祥 wrote: had you tried it? another work around is u can use smartgrid constraints e.g. def test(): table = db.co_meet query = db.co_meet.ref_company == 1 # whatever value that refer to table company grid = SQLFORM.smartgrid(table, constraints = dict(co_meet=query) ) return locals() best regards, stifan -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout. -- Massimiliano -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] Re: many to many with SQLFORM.grid
Both of your answers give a grid of co_meet. What would the query be in SQLFORM.grid such that it would be a grid of db.meeting for one company (not db.co_meet)? On Thursday, May 7, 2015 at 8:26:47 PM UTC-7, 黄祥 wrote: had you tried it? another work around is u can use smartgrid constraints e.g. def test(): table = db.co_meet query = db.co_meet.ref_company == 1 # whatever value that refer to table company grid = SQLFORM.smartgrid(table, constraints = dict(co_meet=query) ) return locals() best regards, stifan -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] Re: many to many with SQLFORM.grid
had you tried it? another work around is u can use smartgrid constraints e.g. def test(): table = db.co_meet query = db.co_meet.ref_company == 1 # whatever value that refer to table company grid = SQLFORM.smartgrid(table, constraints = dict(co_meet=query) ) return locals() best regards, stifan -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] Re: many to many with SQLFORM.grid
Thanks! But wouldn't that just give me a grid of 'co_meet' and not 'meeting'? On Monday, May 4, 2015 at 9:20:01 AM UTC-7, 黄祥 wrote: perhaps, you can do e.g. def test(): query = db.co_meet.ref_company == 1 # whatever value that refer to table company grid=SQLFORM.grid(query) return locals() best regards, stifan -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] Re: many to many with SQLFORM.grid
perhaps, you can do e.g. def test(): query = db.co_meet.ref_company == 1 # whatever value that refer to table company grid=SQLFORM.grid(query) return locals() best regards, stifan -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] Re: Many to many
Thanks a lot Anthony. Now it works. Il giorno giovedì 31 ottobre 2013 00:12:43 UTC+1, Anthony ha scritto: {{for company_image in company_images.select():}} You've already done a select, so don't call .select() again. {{for company_image in company_images:}} li{{=A(IMG(_src=URL('download', args=company_image.image_file_s)), _href=URL('download', args=company_image.image_file))}} span class=ppy-extcaption strong{{=(company_image.title_it)}}/strong When you do a join, you can't just reference the field name -- you have to reference the table name and the field name. So, for example, company_image.image_file_s should be company_image.images.image_file_s. Anthony -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: Many to many
I'm not sure if I understand you right. What do you mean with category? Maybe you could try this to get all images with a specific gallery-id: images = db(db.int_table_galleries_images.gallery.id==1).select() return dict(images=images) - Andreas Am Mittwoch, 30. Oktober 2013 11:51:19 UTC+1 schrieb Gael Princivalle: Hi. I'm still making a website where I'm gone have to manage a lot of little image galleries. An image could be shown inside more galleries, galleries have many images, so my relation is many to many. Here is my db: db.define_table('images', Field('title_en'), Field('title_it'), Field('contents'), Field('image_file', 'upload'), Field('image_file_s', 'upload')) db.define_table('galleries', Field('title_en'), Field('title_it')) db.define_table('int_table_galleries_images', Field('gallery', 'reference galleries'), Field('image', 'reference images')) Now in the company page, I want to show only images from the 1 category. In the controller: def company(): gallery_company = ? return dict(gallery_company=gallery_company) How can I select all fields from images and galleries where galleries.id==1 ? Thanks. -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: Many to many
Thanks Andreas. Sorry I've made a mistake. Now in the company page, I want to show only images from the 1 gallery. With images = db(db.int_table_galleries_images.gallery.id==1).select() I'm still having all images, also from gallery.id==2. I've also tried with images = db(db.int_table_galleries_images.galleryhttp://db.int_table_galleries_images.gallery.id==1).select() (without id). I have all rows. Il giorno mercoledì 30 ottobre 2013 12:42:01 UTC+1, Andreas Wienes ha scritto: I'm not sure if I understand you right. What do you mean with category? Maybe you could try this to get all images with a specific gallery-id: images = db(db.int_table_galleries_images.gallery.id==1).select() return dict(images=images) - Andreas Am Mittwoch, 30. Oktober 2013 11:51:19 UTC+1 schrieb Gael Princivalle: Hi. I'm still making a website where I'm gone have to manage a lot of little image galleries. An image could be shown inside more galleries, galleries have many images, so my relation is many to many. Here is my db: db.define_table('images', Field('title_en'), Field('title_it'), Field('contents'), Field('image_file', 'upload'), Field('image_file_s', 'upload')) db.define_table('galleries', Field('title_en'), Field('title_it')) db.define_table('int_table_galleries_images', Field('gallery', 'reference galleries'), Field('image', 'reference images')) Now in the company page, I want to show only images from the 1 category. In the controller: def company(): gallery_company = ? return dict(gallery_company=gallery_company) How can I select all fields from images and galleries where galleries.id==1 ? Thanks. -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: Many to many
This is discussed here: http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Many-to-many db((db.images.id == db.int_table_galleries_images.image) (db.galleries.id == db.int_table_galleries_images.gallery) (db.galleries.id == 1)).select() Anthony On Wednesday, October 30, 2013 6:51:19 AM UTC-4, Gael Princivalle wrote: Hi. I'm still making a website where I'm gone have to manage a lot of little image galleries. An image could be shown inside more galleries, galleries have many images, so my relation is many to many. Here is my db: db.define_table('images', Field('title_en'), Field('title_it'), Field('contents'), Field('image_file', 'upload'), Field('image_file_s', 'upload')) db.define_table('galleries', Field('title_en'), Field('title_it')) db.define_table('int_table_galleries_images', Field('gallery', 'reference galleries'), Field('image', 'reference images')) Now in the company page, I want to show only images from the 1 category. In the controller: def company(): gallery_company = ? return dict(gallery_company=gallery_company) How can I select all fields from images and galleries where galleries.id==1 ? Thanks. -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: Many to many
Also, you might consider shortening the name of db.int_table_galleries_images to just db.galleries_images? You already know it's a table, so no need for table in the name, and galleries_images already implies it's a many-to-many intermediate table. Anthony On Wednesday, October 30, 2013 8:52:00 AM UTC-4, Anthony wrote: This is discussed here: http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Many-to-many db((db.images.id == db.int_table_galleries_images.image) (db.galleries.id == db.int_table_galleries_images.gallery) (db.galleries.id == 1)).select() Anthony On Wednesday, October 30, 2013 6:51:19 AM UTC-4, Gael Princivalle wrote: Hi. I'm still making a website where I'm gone have to manage a lot of little image galleries. An image could be shown inside more galleries, galleries have many images, so my relation is many to many. Here is my db: db.define_table('images', Field('title_en'), Field('title_it'), Field('contents'), Field('image_file', 'upload'), Field('image_file_s', 'upload')) db.define_table('galleries', Field('title_en'), Field('title_it')) db.define_table('int_table_galleries_images', Field('gallery', 'reference galleries'), Field('image', 'reference images')) Now in the company page, I want to show only images from the 1 category. In the controller: def company(): gallery_company = ? return dict(gallery_company=gallery_company) How can I select all fields from images and galleries where galleries.id==1 ? Thanks. -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: Many to many
Hi Anthony. This is discussed here: http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Many-to-many I've read it before posting. Result was the same as with the code you've post: 'Rows' object has no attribute 'select' Now I've update my db as you've suggest: db.define_table('galleries', Field('title_en'), Field('title_it')) db.define_table('galleries_images', Field('gallery', 'reference galleries'), Field('image', 'reference images')) My controller: def company(): company_images = db((db.images.id == db.galleries_images.image) (db.galleries.id == db.galleries_images.gallery) (db.galleries.id == 1)).select() return dict(company_images=company_images) Perhaps it's in the view that I have to call filed in another way ? I've tried with the .select() in the for: {{for company_image in company_images.select():}} li{{=A(IMG(_src=URL('download', args=company_image.image_file_s)), _href=URL('download', args=company_image.image_file))}} span class=ppy-extcaption strong{{=(company_image.title_it)}}/strong /span /li {{pass}} Error was : 'Rows' object has no attribute 'select' And also without the select() in the for: {{for company_image in company_images:}} li{{=A(IMG(_src=URL('download', args=company_image.image_file_s)), _href=URL('download', args=company_image.image_file))}} span class=ppy-extcaption strong{{=(company_image.title_it)}}/strong /span /li {{pass}} Error was: 'Row' object has no attribute 'image_file_s' Can you help me ? -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: Many to many
{{for company_image in company_images.select():}} You've already done a select, so don't call .select() again. {{for company_image in company_images:}} li{{=A(IMG(_src=URL('download', args=company_image.image_file_s)), _href=URL('download', args=company_image.image_file))}} span class=ppy-extcaption strong{{=(company_image.title_it)}}/strong When you do a join, you can't just reference the field name -- you have to reference the table name and the field name. So, for example, company_image.image_file_s should be company_image.images.image_file_s. Anthony -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: Many to many skills matrix
I have a group of employees and a set of skills. One employee can have many skills and one skill can be had by many employees. What I want to do is to represent the skills of all employees in one table, where the score of each employee in each skill is given on a scale from 0 to 5 like: I think that given your model, you need a score field in the ownership table so you can retrieve each team member's score for every skill name when filling the score table Then it's matter of building a custom html table with the TABLE helper that probably will involve using nested recordset loops. -- --- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: Many to many skills matrix
As Alan says, you don't seem to have a score field. Maybe try this: db.define_table('online_team', Field('first_name', notnull=True), Field('last_name', notnull=True), Field('email', requires=IS_EMAIL(), notnull=True, unique=True), Field('initials', notnull=True, unique=True)) format='%(initials)s') db.define_table('skills', Field('skill', notnull=True, unique=True) format='%(skill)s') db.define_table('ownership', Field('initials', db.online_team), Field('skill', db.skills) Field('score', 'integer')) Maybe look at the smartgrid to get something going fast. -- --- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
Re: [web2py] Re: Many to many relationships
No need. It is a book issue. I will take care of it. On Monday, 25 February 2013 23:49:48 UTC-6, Toby Wilson wrote: Thanks Massimo. Is the second point regarding the shell I raise a known issue as well? *From:* web...@googlegroups.com javascript: [mailto: web...@googlegroups.com javascript:] *On Behalf Of *Massimo Di Pierro *Sent:* Tuesday, 26 February 2013 4:36 PM *To:* web...@googlegroups.com javascript: *Subject:* [web2py] Re: Many to many relationships we need to che change the book. owner is a keyword in postgresql. When we wrote the 4th edition of the book, web2py was not checking by default. recent versions of web2py are checking. On Monday, 25 February 2013 17:02:09 UTC-6, Toby Wilson wrote: Thanks Cliff and Niphlod... appreciate your assistance. I'll go through the book a little slower and get clear on this. In the book it shows an example using the word owner. When I copy and paste this in, I can't access the database administration option as I get an internal error with the message in the ticket stating: type 'exceptions.SyntaxError' invalid table/column name owner is a ALL reserved SQL keyword Anyway, so I just changed it to owne instead and it all works. Another thing, while going through the steps outlined in the book, I've been using the shell in Controllers. I type a line, for example: db.thing.insert(name='Chair', owne=1) and press enter and nothing happens. Then I'll type it again and hit enter and it outputs: In [11] : db.thing.insert(name='Chair', owne=1) 4 Showing that it worked. As I keep doing this and even change what I type in, it only works every second time. One time I hit enter and nothing happens, then after typing I hit enter and it seems to work. Am I doing something wrong or is this a bug in web2py or in my installation perhaps? Toby On Monday, 25 February 2013 18:24:48 UTC+11, Toby Wilson wrote: Hi all, New to web development and web2py. Know a bit of Python. As a learning experience I’ve written a food tracking program that works really well in Python and it just runs on my local PC. It just reads and writes values into text files. Now I’m trying to transfer that into web2py as a next step learning experience. I’m trying to figure out the database side of it (models). It seems what I’m dealing with is many-to-many relationships. I have a list of meals and each meal has a list of associated ingredients. So one ingredient can be in many meal and each meal has many ingredients. So essentially, what the program I've written can do is add 3 x “Meal 1” to the quantity existing in the database for “Meal 1” and then after doing that prompt the user with: “Do you want this to impact the level of the ingredient ‘carrot’?” Y/N And then: “Do you want this to impact the level of the ingredient ‘steak’? Y/N etc. And the program knows that “Meal 1” needs 3 carrots, so upon entering “Y” to the first question, 3 carrots are automatically subtracted from the stock level of carrots in the database. So as meals are added, the user is prompted with associated ingredients and can choose to update or not update the level of that associated ingredient. In the local Python program I’ve written, all of this is just done through listing things in dictionaries and using functions to manipulate the data and then read and write to text files. Now I want to use database tables and I’m not sure how much of the association should be in the model and how much (if any) should be in the controller (for example in the form of a dictionary). I’ve read through the documentation on many to many relationships but haven’t got a full grasp of it yet. I’ve coded a few different database structures but haven’t managed to get it right yet. The way I've been thinking about it is, a table for meals, a table for ingredients, and perhaps a table for associations? I've used reference to get the drop down box associated, but I can't select more than ingredient. I had a look at the Django admin login and it visually made this look a bit easier - i.e. building the one to many relationship. How is this done in web2py? Any help or advice would be much appreciated. Toby P.S. If I have a database file in web2py and open the file through the web2py IDE and replace the contents with a totally different database structure and save it, what happens? Does web2py just remove what was there in the database and replace the database tables with the new ones or does it manage it in a different way (i.e. only add new elements etc.)? -- --- 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 javascript:. For more options, visit https://groups.google.com/groups/opt_out. No virus
[web2py] Re: Many to many relationships
On Monday, February 25, 2013 8:24:48 AM UTC+1, Toby Wilson wrote: Hi all, New to web development and web2py. Know a bit of Python. As a learning experience I’ve written a food tracking program that works really well in Python and it just runs on my local PC. It just reads and writes values into text files. Now I’m trying to transfer that into web2py as a next step learning experience. I’m trying to figure out the database side of it (models). It seems what I’m dealing with is many-to-many relationships. I have a list of meals and each meal has a list of associated ingredients. So one ingredient can be in many meal and each meal has many ingredients. So essentially, what the program I've written can do is add 3 x “Meal 1” to the quantity existing in the database for “Meal 1” and then after doing that prompt the user with: “Do you want this to impact the level of the ingredient ‘carrot’?” Y/N And then: “Do you want this to impact the level of the ingredient ‘steak’? Y/N etc. And the program knows that “Meal 1” needs 3 carrots, so upon entering “Y” to the first question, 3 carrots are automatically subtracted from the stock level of carrots in the database. So as meals are added, the user is prompted with associated ingredients and can choose to update or not update the level of that associated ingredient. In the local Python program I’ve written, all of this is just done through listing things in dictionaries and using functions to manipulate the data and then read and write to text files. Now I want to use database tables and I’m not sure how much of the association should be in the model and how much (if any) should be in the controller (for example in the form of a dictionary). I’ve read through the documentation on many to many relationships but haven’t got a full grasp of it yet. I’ve coded a few different database structures but haven’t managed to get it right yet. The way I've been thinking about it is, a table for meals, a table for ingredients, and perhaps a table for associations? I've used reference to get the drop down box associated, but I can't select more than ingredient. I had a look at the Django admin login and it visually made this look a bit easier - i.e. building the one to many relationship. How is this done in web2py? Depends on what you need to build upon that model. If you're tracking stock too, then an ingredient table with the current quantity stored seems fair, and a table with meals too. On the association part, for an app like that you don't need to over-normalize the schema with a table holding the associations only: I doubt the ingredient carrot needs to change one day into watermelon. You can fill the association part having a column in the meals table that references multiple ingredients with the list: types ... http://web2py.com/books/default/chapter/29/06#list:%3Ctype%3E,-and-contains Any help or advice would be much appreciated. Toby P.S. If I have a database file in web2py and open the file through the web2py IDE and replace the contents with a totally different database structure and save it, what happens? Does web2py just remove what was there in the database and replace the database tables with the new ones or does it manage it in a different way (i.e. only add new elements etc.)? web2py only watches .table files inside the databases/ folder and your models. If you have 2 table defined in your models and in the db there are other 15 tables, web2py can access only the 2 you defined. Additionally, migrations are fired only if models aren't in sync with the .table files, there is no introspection whatsoever on the db side. So, if you have a table x with 3 columns and you change the structure outside web2py, web2py will try to access the table as it's defined in your model. If column names don't match, etc etc etc, you'll get errors trying to access something that isn't there. -- --- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: Many to many relationships
Toby, The online Web2py manual explains how to set up many-to-many relationships. If this is new to you, start reading about one-to-many relationships and continue from there. http://web2py.com/books/default/chapter/29/06#One-to-many-relation On Monday, February 25, 2013 2:24:48 AM UTC-5, Toby Wilson wrote: Hi all, New to web development and web2py. Know a bit of Python. As a learning experience I’ve written a food tracking program that works really well in Python and it just runs on my local PC. It just reads and writes values into text files. Now I’m trying to transfer that into web2py as a next step learning experience. I’m trying to figure out the database side of it (models). It seems what I’m dealing with is many-to-many relationships. I have a list of meals and each meal has a list of associated ingredients. So one ingredient can be in many meal and each meal has many ingredients. So essentially, what the program I've written can do is add 3 x “Meal 1” to the quantity existing in the database for “Meal 1” and then after doing that prompt the user with: “Do you want this to impact the level of the ingredient ‘carrot’?” Y/N And then: “Do you want this to impact the level of the ingredient ‘steak’? Y/N etc. And the program knows that “Meal 1” needs 3 carrots, so upon entering “Y” to the first question, 3 carrots are automatically subtracted from the stock level of carrots in the database. So as meals are added, the user is prompted with associated ingredients and can choose to update or not update the level of that associated ingredient. In the local Python program I’ve written, all of this is just done through listing things in dictionaries and using functions to manipulate the data and then read and write to text files. Now I want to use database tables and I’m not sure how much of the association should be in the model and how much (if any) should be in the controller (for example in the form of a dictionary). I’ve read through the documentation on many to many relationships but haven’t got a full grasp of it yet. I’ve coded a few different database structures but haven’t managed to get it right yet. The way I've been thinking about it is, a table for meals, a table for ingredients, and perhaps a table for associations? I've used reference to get the drop down box associated, but I can't select more than ingredient. I had a look at the Django admin login and it visually made this look a bit easier - i.e. building the one to many relationship. How is this done in web2py? Any help or advice would be much appreciated. Toby P.S. If I have a database file in web2py and open the file through the web2py IDE and replace the contents with a totally different database structure and save it, what happens? Does web2py just remove what was there in the database and replace the database tables with the new ones or does it manage it in a different way (i.e. only add new elements etc.)? -- --- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: Many to many relationships
Thanks Cliff and Niphlod... appreciate your assistance. I'll go through the book a little slower and get clear on this. In the book it shows an example using the word owner. When I copy and paste this in, I can't access the database administration option as I get an internal error with the message in the ticket stating: type 'exceptions.SyntaxError' invalid table/column name owner is a ALL reserved SQL keyword Anyway, so I just changed it to owne instead and it all works. Another thing, while going through the steps outlined in the book, I've been using the shell in Controllers. I type a line, for example: db.thing.insert(name='Chair', owne=1) and press enter and nothing happens. Then I'll type it again and hit enter and it outputs: In [11] : db.thing.insert(name='Chair', owne=1) 4 Showing that it worked. As I keep doing this and even change what I type in, it only works every second time. One time I hit enter and nothing happens, then after typing I hit enter and it seems to work. Am I doing something wrong or is this a bug in web2py or in my installation perhaps? Toby On Monday, 25 February 2013 18:24:48 UTC+11, Toby Wilson wrote: Hi all, New to web development and web2py. Know a bit of Python. As a learning experience I’ve written a food tracking program that works really well in Python and it just runs on my local PC. It just reads and writes values into text files. Now I’m trying to transfer that into web2py as a next step learning experience. I’m trying to figure out the database side of it (models). It seems what I’m dealing with is many-to-many relationships. I have a list of meals and each meal has a list of associated ingredients. So one ingredient can be in many meal and each meal has many ingredients. So essentially, what the program I've written can do is add 3 x “Meal 1” to the quantity existing in the database for “Meal 1” and then after doing that prompt the user with: “Do you want this to impact the level of the ingredient ‘carrot’?” Y/N And then: “Do you want this to impact the level of the ingredient ‘steak’? Y/N etc. And the program knows that “Meal 1” needs 3 carrots, so upon entering “Y” to the first question, 3 carrots are automatically subtracted from the stock level of carrots in the database. So as meals are added, the user is prompted with associated ingredients and can choose to update or not update the level of that associated ingredient. In the local Python program I’ve written, all of this is just done through listing things in dictionaries and using functions to manipulate the data and then read and write to text files. Now I want to use database tables and I’m not sure how much of the association should be in the model and how much (if any) should be in the controller (for example in the form of a dictionary). I’ve read through the documentation on many to many relationships but haven’t got a full grasp of it yet. I’ve coded a few different database structures but haven’t managed to get it right yet. The way I've been thinking about it is, a table for meals, a table for ingredients, and perhaps a table for associations? I've used reference to get the drop down box associated, but I can't select more than ingredient. I had a look at the Django admin login and it visually made this look a bit easier - i.e. building the one to many relationship. How is this done in web2py? Any help or advice would be much appreciated. Toby P.S. If I have a database file in web2py and open the file through the web2py IDE and replace the contents with a totally different database structure and save it, what happens? Does web2py just remove what was there in the database and replace the database tables with the new ones or does it manage it in a different way (i.e. only add new elements etc.)? -- --- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: Many to many relationships
we need to che change the book. owner is a keyword in postgresql. When we wrote the 4th edition of the book, web2py was not checking by default. recent versions of web2py are checking. On Monday, 25 February 2013 17:02:09 UTC-6, Toby Wilson wrote: Thanks Cliff and Niphlod... appreciate your assistance. I'll go through the book a little slower and get clear on this. In the book it shows an example using the word owner. When I copy and paste this in, I can't access the database administration option as I get an internal error with the message in the ticket stating: type 'exceptions.SyntaxError' invalid table/column name owner is a ALL reserved SQL keyword Anyway, so I just changed it to owne instead and it all works. Another thing, while going through the steps outlined in the book, I've been using the shell in Controllers. I type a line, for example: db.thing.insert(name='Chair', owne=1) and press enter and nothing happens. Then I'll type it again and hit enter and it outputs: In [11] : db.thing.insert(name='Chair', owne=1) 4 Showing that it worked. As I keep doing this and even change what I type in, it only works every second time. One time I hit enter and nothing happens, then after typing I hit enter and it seems to work. Am I doing something wrong or is this a bug in web2py or in my installation perhaps? Toby On Monday, 25 February 2013 18:24:48 UTC+11, Toby Wilson wrote: Hi all, New to web development and web2py. Know a bit of Python. As a learning experience I’ve written a food tracking program that works really well in Python and it just runs on my local PC. It just reads and writes values into text files. Now I’m trying to transfer that into web2py as a next step learning experience. I’m trying to figure out the database side of it (models). It seems what I’m dealing with is many-to-many relationships. I have a list of meals and each meal has a list of associated ingredients. So one ingredient can be in many meal and each meal has many ingredients. So essentially, what the program I've written can do is add 3 x “Meal 1” to the quantity existing in the database for “Meal 1” and then after doing that prompt the user with: “Do you want this to impact the level of the ingredient ‘carrot’?” Y/N And then: “Do you want this to impact the level of the ingredient ‘steak’? Y/N etc. And the program knows that “Meal 1” needs 3 carrots, so upon entering “Y” to the first question, 3 carrots are automatically subtracted from the stock level of carrots in the database. So as meals are added, the user is prompted with associated ingredients and can choose to update or not update the level of that associated ingredient. In the local Python program I’ve written, all of this is just done through listing things in dictionaries and using functions to manipulate the data and then read and write to text files. Now I want to use database tables and I’m not sure how much of the association should be in the model and how much (if any) should be in the controller (for example in the form of a dictionary). I’ve read through the documentation on many to many relationships but haven’t got a full grasp of it yet. I’ve coded a few different database structures but haven’t managed to get it right yet. The way I've been thinking about it is, a table for meals, a table for ingredients, and perhaps a table for associations? I've used reference to get the drop down box associated, but I can't select more than ingredient. I had a look at the Django admin login and it visually made this look a bit easier - i.e. building the one to many relationship. How is this done in web2py? Any help or advice would be much appreciated. Toby P.S. If I have a database file in web2py and open the file through the web2py IDE and replace the contents with a totally different database structure and save it, what happens? Does web2py just remove what was there in the database and replace the database tables with the new ones or does it manage it in a different way (i.e. only add new elements etc.)? -- --- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
RE: [web2py] Re: Many to many relationships
Thanks Massimo. Is the second point regarding the shell I raise a known issue as well? From: web2py@googlegroups.com [mailto:web2py@googlegroups.com] On Behalf Of Massimo Di Pierro Sent: Tuesday, 26 February 2013 4:36 PM To: web2py@googlegroups.com Subject: [web2py] Re: Many to many relationships we need to che change the book. owner is a keyword in postgresql. When we wrote the 4th edition of the book, web2py was not checking by default. recent versions of web2py are checking. On Monday, 25 February 2013 17:02:09 UTC-6, Toby Wilson wrote: Thanks Cliff and Niphlod... appreciate your assistance. I'll go through the book a little slower and get clear on this. In the book it shows an example using the word owner. When I copy and paste this in, I can't access the database administration option as I get an internal error with the message in the ticket stating: type 'exceptions.SyntaxError' invalid table/column name owner is a ALL reserved SQL keyword Anyway, so I just changed it to owne instead and it all works. Another thing, while going through the steps outlined in the book, I've been using the shell in Controllers. I type a line, for example: db.thing.insert(name='Chair', owne=1) and press enter and nothing happens. Then I'll type it again and hit enter and it outputs: In [11] : db.thing.insert(name='Chair', owne=1) 4 Showing that it worked. As I keep doing this and even change what I type in, it only works every second time. One time I hit enter and nothing happens, then after typing I hit enter and it seems to work. Am I doing something wrong or is this a bug in web2py or in my installation perhaps? Toby On Monday, 25 February 2013 18:24:48 UTC+11, Toby Wilson wrote: Hi all, New to web development and web2py. Know a bit of Python. As a learning experience I've written a food tracking program that works really well in Python and it just runs on my local PC. It just reads and writes values into text files. Now I'm trying to transfer that into web2py as a next step learning experience. I'm trying to figure out the database side of it (models). It seems what I'm dealing with is many-to-many relationships. I have a list of meals and each meal has a list of associated ingredients. So one ingredient can be in many meal and each meal has many ingredients. So essentially, what the program I've written can do is add 3 x Meal 1 to the quantity existing in the database for Meal 1 and then after doing that prompt the user with: Do you want this to impact the level of the ingredient 'carrot'? Y/N And then: Do you want this to impact the level of the ingredient 'steak'? Y/N etc. And the program knows that Meal 1 needs 3 carrots, so upon entering Y to the first question, 3 carrots are automatically subtracted from the stock level of carrots in the database. So as meals are added, the user is prompted with associated ingredients and can choose to update or not update the level of that associated ingredient. In the local Python program I've written, all of this is just done through listing things in dictionaries and using functions to manipulate the data and then read and write to text files. Now I want to use database tables and I'm not sure how much of the association should be in the model and how much (if any) should be in the controller (for example in the form of a dictionary). I've read through the documentation on many to many relationships but haven't got a full grasp of it yet. I've coded a few different database structures but haven't managed to get it right yet. The way I've been thinking about it is, a table for meals, a table for ingredients, and perhaps a table for associations? I've used reference to get the drop down box associated, but I can't select more than ingredient. I had a look at the Django admin login and it visually made this look a bit easier - i.e. building the one to many relationship. How is this done in web2py? Any help or advice would be much appreciated. Toby P.S. If I have a database file in web2py and open the file through the web2py IDE and replace the contents with a totally different database structure and save it, what happens? Does web2py just remove what was there in the database and replace the database tables with the new ones or does it manage it in a different way (i.e. only add new elements etc.)? -- --- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out. No virus found in this message. Checked by AVG - www.avg.com Version: 2013.0.2899 / Virus Database: 2639/6113 - Release Date: 02/18/13 Internal Virus Database is out of date. -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop
[web2py] Re: Many to Many?
I think your idea will work, that sounds the best. On Friday, March 9, 2012 5:21:33 AM UTC-7, Wikus van de Merwe wrote: What pbreit suggested assumes that all the company servers are at the same location as the company, which I guess is not what you wanted. It think it should rather be: db.define_table('company', Field('name'), Field('location', db.location)) db.define_table('server', Field('name'), Field('company', db.company), Field('location', db.location)) db.define_table('location', Field('name'))
[web2py] Re: Many to Many?
What pbreit suggested assumes that all the company servers are at the same location as the company, which I guess is not what you wanted. It think it should rather be: db.define_table('company', Field('name'), Field('location', db.location)) db.define_table('server', Field('name'), Field('company', db.company), Field('location', db.location)) db.define_table('location', Field('name'))
[web2py] Re: Many to Many?
I don't really understand the question but I would think something like this: db.define_table('company', Field('name')) db.define_table('server', Field('name'), Field('company', db.company), Field('location', db.location)) db.define_table('location', Field('name')) Or since each company only has one location: db.define_table('company', Field('name'), Field('location')) db.define_table('server', Field('name'), Field('company_id', db.company)) To get the location of the server #1, you have to do a query: record = db(db.server.id==1)(db.server.company_id==db.company.id).select().first() location = record.company.location
[web2py] Re: Many to Many?
Nope, your way is the better way :) On Friday, March 9, 2012 8:14:57 AM UTC+8, Derek wrote: Let's say I am designing a database. It has these elements: Companies, Locations, Servers Each company has one location, and one or more servers. Each server has one and only one location. So, company Telefónica de España has location Madrid. They have four servers, Pablo, Alejandro, Guido, and Franco. There are three locations in the database: Madrid (Spain), Paris (France), and Riccone (Italy). Obviously, Pablo and Alejandro are in Spain, Guido is in Italy, and Franco is in France. So, I want it so that if I change the name of the location (such as changing 'Madrid' to 'al-Mudaina') it will be updated on both the companies and servers. To set this up, do I need to have two extra tables location to server and location to company or is there a better way to do it?
[web2py] Re: Many-to-many relations with CRUD Insert
This might give you some ideas: http://web2py.com/books/default/chapter/29/7#One-form-for-multiple-tables. I suppose you would have to loop through the list of submitted authors to insert multiple records into the verb table. Anthony On Monday, January 16, 2012 3:35:16 PM UTC-5, Christian Winterhager wrote: One of the standard examples of many-to-many Relations is a library. For simplicity i choose only 3 Tables: db.define_table ('author', Field ('name', 'string', length=100)) db.define_table ('verb', Field ('bnr', db.book ), Field ('anr', db.author ), ) db.define_table ('book', Field('title', 'string', length=250)) It is very easy to insert a new book, one or more author and the corresponding record in the table verb. But i found no way to insert a book with the CRUD-Method. The complexityhttp://dict.leo.org/ende?lp=endep=DOKJAAsearch=complexitytrestr=0x8001 is that i first must insert a row into table book, then one into table author and last i have to connect the inserted rows with the corresponding numbers 'bnr' and 'anr' in table verb. Does anyone know if there is a way to do that with CRUD?
[web2py] Re: Many-to-many relations with CRUD Insert
Antony, thank you, i tried exactly this some weeks ago and it did not work. Since now... may be, that i worked not enough. I will try more and we will see.
[web2py] Re: Many-to-many relations with CRUD Insert
Anthony, the basic recommendation was to use SQLFORM.factory, and that was a good idea. I tried it again and i had now more success than some weeks before. So now i can insert rows in the table book and the table verb in one FORM and with only one submit-button. The FORM looks like this: form=SQLFORM.factory(db.book, db.verb.anr) if form.process().accepted: id = db.book.insert(**db.book._filter_fields(form.vars)) db.commit() booknumber = id authornumber = form.vars.anr id = db.verb.insert(bnr=booknumber,anr=authornumber) db.commit() response.flash='Bingo!' It looks very good but you know, if you have succesfully finished a job, you find some other problems...
[web2py] Re: Many-to-many admin form
OK, Here are some resources you will want to check out if you have not found them on your own already. # Web2py Plugins ## http://web2py.com/plugins/default ### looks good http://web2py.com/plugins/default/tagging ### interesting http://web2py.com/plugins/default/multiselect http://web2py.com/plugins/default/dropdown # Powerform ## http://labs.blouweb.com/powerformwizard Multi table forms # http://dev.s-cubism.com/web2py_plugins ## Other the top functionality, slightly bleeding edge: http://dev.s-cubism.com/plugin_multiselect_widget http://dev.s-cubism.com/plugin_solidform
Re: [web2py] Re: Many-to-many admin form
OK thanks, I will take a look through the resources you have provided. -- Regards, Bruce On Wed, Dec 28, 2011 at 12:35 PM, Christopher Steel chris.st...@gmail.comwrote: OK, Here are some resources you will want to check out if you have not found them on your own already. # Web2py Plugins ## http://web2py.com/plugins/default ### looks good http://web2py.com/plugins/default/tagging ### interesting http://web2py.com/plugins/default/multiselect http://web2py.com/plugins/default/dropdown # Powerform ## http://labs.blouweb.com/powerformwizard Multi table forms # http://dev.s-cubism.com/web2py_plugins ## Other the top functionality, slightly bleeding edge: http://dev.s-cubism.com/plugin_multiselect_widget http://dev.s-cubism.com/plugin_solidform -- -- Regards, Bruce Wade http://ca.linkedin.com/in/brucelwade http://www.wadecybertech.com http://www.warplydesigned.com http://www.fitnessfriendsfinder.com
Re: [web2py] Re: Many-to-many admin form
OK, If your database table is setup correctly then in the database examples section you should be able to create a form similar to the one in example 33 for a many to many relationship. http://web2py.com/examples/database_examples/buy ( http://web2py.com/examples/default/examples#database_examples see number 33 )
[web2py] Re: Many-to-many admin form
That example doesn't look like a true many-to-many it looks like a one-to-many. Because in the example it searches for both id's already being in the many-to-many table and if they are it only updates. Where as a true many to many you would have multiple entries IE: (1, 1, 20), (1, 1, 40), (1, 1, 50) etc Not so. One buyer can purchase many products and one product can have many purchases. That makes it many-to-many. While it's true that example 33 allows only one relationship between one buyer and one product, that does not disqualify it from being a many to many relationship. If the example were designed to allow multiple purchases between one single buyer and one single product, it would check the id of the linking record before making the insert/update decision. If the id of the linking record is absent, then do an insert. Otherwise an update. Maybe it's just my personal paranoia about connection reliability, etc. but I much prefer smartgrid's approach to what Django does. Getting the primary table's record in the db is much more important, from my perspective, than the seeming convenience of having it all on one screen. On Dec 25, 1:01 pm, Bruce Wade bruce.w...@gmail.com wrote: That example doesn't look like a true many-to-many it looks like a one-to-many. Because in the example it searches for both id's already being in the many-to-many table and if they are it only updates. Where as a true many to many you would have multiple entries IE: (1, 1, 20), (1, 1, 40), (1, 1, 50) etc See the attached of how django handles it and what I want to see how to bend web2py database admin to handle automatically as well. The multiple select box's are many-to-many built into a single parent form. You can also click the plus symbol to add new items in the select box if they do not already exist. We should never have to manual select the parent ID if it is already in the same form. On Sun, Dec 25, 2011 at 8:29 AM, Christopher Steel chris.st...@gmail.comwrote: OK, If your database table is setup correctly then in the database examples section you should be able to create a form similar to the one in example 33 for a many to many relationship. http://web2py.com/examples/database_examples/buy (http://web2py.com/examples/default/examples#database_examplessee number 33 ) -- -- Regards, Bruce Wadehttp://ca.linkedin.com/in/brucelwadehttp://www.wadecybertech.comhttp://www.warplydesigned.comhttp://www.fitnessfriendsfinder.com many_to_many.png 159KViewDownload
Re: [web2py] Re: Many-to-many admin form
I think what you mean to say is that you are are looking for the ability to make multiple selections in one or more tables using a single form that allows you create a user record. So for example a table of users, a table of email address types and a table of email addresses but the user on see a single form where they can enter their name and one or more email addresses and email address types. Does that sound about right? This is a bit different then a many-to-many database relationship. Actually in your form I do not see any things that appear to be what are commonly referred to as many-to-many relationships in database theory. Here is what Wikipedia has to say about database many-to-many relationships: http://en.wikipedia.org/wiki/Many-to-many_%28data_model%29 From my understanding in classic database theory we talk about many to many 'relationships' and the 'relationships are recorded to a third table. The tables to the left and right of the relationship table both have what resembles a many to one relationship with the middle table but they have a many to many relationship with one another. The relationship table is what allows for the many to many relationship. For example: table one = products table two = purchases table three = buyers or products -- purchases -- buyers so if products contains soap and john buys some soap then we have the following entry in the purchases table: john, soap but we could also have jill, shoes jill, soap and so on
Re: [web2py] Re: Many-to-many admin form
Hi Christopher, * * *That is pretty much what I am after.* * * *table = keywords values ('Art', 'Games', 'Money') # Used to find all ads related based on keywords they select* *table = ad (* * url,* * created_by* * keywords - can have 0 or more keywords, keywords can belong to 0 or more ad's* *)* *table = ad_keyword value (ad.id = 1, keyword.id = 1) etc...* * * *form:* *url* *created_by = logged in member* *keywords = shows a list of existing keywords, or uses auto completion, [can select 0 or more and create a new keyword if not exist] if the keyword does not exist it is inserted (only inserted as a new keyword not linked to * *this ad until the ad is saved). **you then save the ad, and add the record to ad_keyword table* * * *If the above is still not clear let me know and I will try to figure out a different way to explain it. If I can't think of a better way to explain I will just write working code and provide it once it is done :)* * * *--* *Regards,* *Bruce* * * On Sun, Dec 25, 2011 at 11:11 PM, Christopher Steel chris.st...@gmail.comwrote: I think what you mean to say is that you are are looking for the ability to make multiple selections in one or more tables using a single form that allows you create a user record. So for example a table of users, a table of email address types and a table of email addresses but the user on see a single form where they can enter their name and one or more email addresses and email address types. Does that sound about right? This is a bit different then a many-to-many database relationship. Actually in your form I do not see any things that appear to be what are commonly referred to as many-to-many relationships in database theory. Here is what Wikipedia has to say about database many-to-many relationships: http://en.wikipedia.org/wiki/Many-to-many_%28data_model%29 From my understanding in classic database theory we talk about many to many 'relationships' and the 'relationships are recorded to a third table. The tables to the left and right of the relationship table both have what resembles a many to one relationship with the middle table but they have a many to many relationship with one another. The relationship table is what allows for the many to many relationship. For example: table one = products table two = purchases table three = buyers or products -- purchases -- buyers so if products contains soap and john buys some soap then we have the following entry in the purchases table: john, soap but we could also have jill, shoes jill, soap and so on -- -- Regards, Bruce Wade http://ca.linkedin.com/in/brucelwade http://www.wadecybertech.com http://www.warplydesigned.com http://www.fitnessfriendsfinder.com
[web2py] Re: Many-to-many admin form
For an example of a Web2py database with a many to many relationship you can see example number 29 under the database examples section: http://web2py.com/examples/default/examples Go to the section called Database Examples See the first example
Re: [web2py] Re: Many-to-many admin form
I looked at the examples they seem to just show how to link at the database level. From my example code I already have that part working. It is at the form/view level I am looking at adding functionality. On Sat, Dec 24, 2011 at 9:41 AM, Christopher Steel chris.st...@gmail.comwrote: For an example of a Web2py database with a many to many relationship you can see example number 29 under the database examples section: http://web2py.com/examples/default/examples Go to the section called Database Examples See the first example -- -- Regards, Bruce Wade http://ca.linkedin.com/in/brucelwade http://www.wadecybertech.com http://www.warplydesigned.com http://www.fitnessfriendsfinder.com
[web2py] Re: Many-to-many admin form
I am guessing from the lack of responses this is not possible with Web2py's current admin? On Wed, Dec 21, 2011 at 4:49 PM, Bruce Wade bruce.w...@gmail.com wrote: Hello, I know django has this ability so I am assuming web2py also does. (Here is to hoping :D) First the models: db.define_table('adreports', Field('reporttype', unique=True) ) db.define_table('ads', Field('member_id', db.auth_user, default=auth.user_id), Field('points', 'integer', default=0), Field('url'), Field('language'), Field('location'), Field('accepted', 'boolean', default=False), Field('viewable', 'boolean'), Field('updated_at','datetime', default=request.now), Field('added_at','datetime', default=request.now) ) db.define_table('keywords', Field('isapproved', 'boolean', default=False), Field('key') ) db.define_table('ad_report', Field('ad_id', db.ads), Field('reportad_id', db.adreports) ) db.define_table('ad_keyword', Field('ad_id', db.ads), Field('keyword_id', db.keywords) ) db.ads.url.requires = (IS_URL(), IS_NOT_IN_DB(db, db.ads.url)) db.ad_report.ad_id.requires = IS_IN_DB(db, db.ads.id, %(url)s) db.ad_report.reportad_id.requires = IS_IN_DB(db, db.adreports.id, %(reporttype)s) db.ad_keyword.ad_id.requires = IS_IN_DB(db, db.ads.id, %(url)s) db.ad_keyword.keyword_id.requires = IS_IN_DB(db, db.keywords.id, %(key)s) -- Now I would like to be able, from the admin create a new ad and on the same page add multiple keywords and/or reports for the ad. This would have to happen with creating a new ad as well as when editing an existing ad. With Django you use: admin.TabularInline and inlines on admin.ModelAdmin classes https://docs.djangoproject.com/en/dev/ref/contrib/admin/#inlinemodeladmin-objects Has something similar being developed for web2py admin application? -- Regards, Bruce Wade http://ca.linkedin.com/in/brucelwade http://www.wadecybertech.com http://www.warplydesigned.com http://www.fitnessfriendsfinder.com -- -- Regards, Bruce Wade http://ca.linkedin.com/in/brucelwade http://www.wadecybertech.com http://www.warplydesigned.com http://www.fitnessfriendsfinder.com
Re: [web2py] Re: Many-to-many admin form
The web2py admin works diferently to the django admin. The web2py admin is for install apps and manage instances. 2011/12/23 Bruce Wade bruce.w...@gmail.com I am guessing from the lack of responses this is not possible with Web2py's current admin? On Wed, Dec 21, 2011 at 4:49 PM, Bruce Wade bruce.w...@gmail.com wrote: Hello, I know django has this ability so I am assuming web2py also does. (Here is to hoping :D) First the models: db.define_table('adreports', Field('reporttype', unique=True) ) db.define_table('ads', Field('member_id', db.auth_user, default=auth.user_id), Field('points', 'integer', default=0), Field('url'), Field('language'), Field('location'), Field('accepted', 'boolean', default=False), Field('viewable', 'boolean'), Field('updated_at','datetime', default=request.now), Field('added_at','datetime', default=request.now) ) db.define_table('keywords', Field('isapproved', 'boolean', default=False), Field('key') ) db.define_table('ad_report', Field('ad_id', db.ads), Field('reportad_id', db.adreports) ) db.define_table('ad_keyword', Field('ad_id', db.ads), Field('keyword_id', db.keywords) ) db.ads.url.requires = (IS_URL(), IS_NOT_IN_DB(db, db.ads.url)) db.ad_report.ad_id.requires = IS_IN_DB(db, db.ads.id, %(url)s) db.ad_report.reportad_id.requires = IS_IN_DB(db, db.adreports.id, %(reporttype)s) db.ad_keyword.ad_id.requires = IS_IN_DB(db, db.ads.id, %(url)s) db.ad_keyword.keyword_id.requires = IS_IN_DB(db, db.keywords.id, %(key)s) -- Now I would like to be able, from the admin create a new ad and on the same page add multiple keywords and/or reports for the ad. This would have to happen with creating a new ad as well as when editing an existing ad. With Django you use: admin.TabularInline and inlines on admin.ModelAdmin classes https://docs.djangoproject.com/en/dev/ref/contrib/admin/#inlinemodeladmin-objects Has something similar being developed for web2py admin application? -- Regards, Bruce Wade http://ca.linkedin.com/in/brucelwade http://www.wadecybertech.com http://www.warplydesigned.com http://www.fitnessfriendsfinder.com -- -- Regards, Bruce Wade http://ca.linkedin.com/in/brucelwade http://www.wadecybertech.com http://www.warplydesigned.com http://www.fitnessfriendsfinder.com -- Alvaro Lizama Molina - http://alvarolizama.net
Re: [web2py] Re: Many-to-many admin form
I understand that, but you can also use the database portion to create new data. That is mainly the part I am talking about. When clicking database then insert in a table, it would be nice if the relationships could be linked on that insert. This would make performing inserts more efficiently. Anyway which files should I start looking at to see how I can implement this functionality for myself and anyone else who wants it? Best Regards, Bruce On Fri, Dec 23, 2011 at 7:05 AM, Alvaro Lizama Molina nek...@gmail.comwrote: The web2py admin works diferently to the django admin. The web2py admin is for install apps and manage instances. 2011/12/23 Bruce Wade bruce.w...@gmail.com I am guessing from the lack of responses this is not possible with Web2py's current admin? On Wed, Dec 21, 2011 at 4:49 PM, Bruce Wade bruce.w...@gmail.com wrote: Hello, I know django has this ability so I am assuming web2py also does. (Here is to hoping :D) First the models: db.define_table('adreports', Field('reporttype', unique=True) ) db.define_table('ads', Field('member_id', db.auth_user, default=auth.user_id), Field('points', 'integer', default=0), Field('url'), Field('language'), Field('location'), Field('accepted', 'boolean', default=False), Field('viewable', 'boolean'), Field('updated_at','datetime', default=request.now), Field('added_at','datetime', default=request.now) ) db.define_table('keywords', Field('isapproved', 'boolean', default=False), Field('key') ) db.define_table('ad_report', Field('ad_id', db.ads), Field('reportad_id', db.adreports) ) db.define_table('ad_keyword', Field('ad_id', db.ads), Field('keyword_id', db.keywords) ) db.ads.url.requires = (IS_URL(), IS_NOT_IN_DB(db, db.ads.url)) db.ad_report.ad_id.requires = IS_IN_DB(db, db.ads.id, %(url)s) db.ad_report.reportad_id.requires = IS_IN_DB(db, db.adreports.id, %(reporttype)s) db.ad_keyword.ad_id.requires = IS_IN_DB(db, db.ads.id, %(url)s) db.ad_keyword.keyword_id.requires = IS_IN_DB(db, db.keywords.id, %(key)s) -- Now I would like to be able, from the admin create a new ad and on the same page add multiple keywords and/or reports for the ad. This would have to happen with creating a new ad as well as when editing an existing ad. With Django you use: admin.TabularInline and inlines on admin.ModelAdmin classes https://docs.djangoproject.com/en/dev/ref/contrib/admin/#inlinemodeladmin-objects Has something similar being developed for web2py admin application? -- Regards, Bruce Wade http://ca.linkedin.com/in/brucelwade http://www.wadecybertech.com http://www.warplydesigned.com http://www.fitnessfriendsfinder.com -- -- Regards, Bruce Wade http://ca.linkedin.com/in/brucelwade http://www.wadecybertech.com http://www.warplydesigned.com http://www.fitnessfriendsfinder.com -- Alvaro Lizama Molina - http://alvarolizama.net -- -- Regards, Bruce Wade http://ca.linkedin.com/in/brucelwade http://www.wadecybertech.com http://www.warplydesigned.com http://www.fitnessfriendsfinder.com
[web2py] Re: Many-to-many admin form
On Dec 23, 10:11 am, Bruce Wade bruce.w...@gmail.com wrote: Anyway which files should I start looking at to see how I can implement this functionality for myself and anyone else who wants it? Look for controller appadmin under your or any other app folder.
Re: [web2py] Re: Many-to-many admin form
I have solved these kinds of concerns with pyramid based applications using sqlalchemy. However I need to look into the core of how Web2py handles things to figure out the best way. Sqlachemy allows you to put everything in: 1) Transactions 2) Sessions So on submit you first create the parent record: ad = Ad() session.add(ad) session.flush() Now you have access to ad.id (parent id) report = Report(...) report.ad_id = ad.id session.add(report) Now if you used Sqlachemy relationships ad.reports.append(report) # use this instead of report.ad_id = ad.id session.flush() Or in the case of having multiple children created before creating the parent on a web form. You just submit all the form data at once and loop through all reports. ad = Ad(...) session.add(ad) session.flush() get list of all reports in form submit for report in reports: session.add(report) ad.reports.append(report) session.flush() If anything fails the transaction automatically roles everything back. Or a easier solution is to only enable the children on edits. So you first create the parent, then you can view/edit/delete children on the parent edit page. -- Regards, Bruce On Fri, Dec 23, 2011 at 10:00 AM, Cliff cjk...@gmail.com wrote: Bruce, you are more brave than me ;) I always like to get the parent record committed in the database before adding child records. that way you know the ID of the parent record and it has the minimal required fieldset safely saved away. If there's a session interruption you don't have some potential orphan child records floating around somewhere. If you've solved those kinds of concerns, please share it with us. Thanks, Cliff Kachinske On Dec 23, 10:34 am, DenesL denes1...@yahoo.ca wrote: On Dec 23, 10:11 am, Bruce Wade bruce.w...@gmail.com wrote: Anyway which files should I start looking at to see how I can implement this functionality for myself and anyone else who wants it? Look for controller appadmin under your or any other app folder. -- -- Regards, Bruce Wade http://ca.linkedin.com/in/brucelwade http://www.wadecybertech.com http://www.warplydesigned.com http://www.fitnessfriendsfinder.com
Re: [web2py] Re: Many-to-many admin form
web2py wraps each request in a single transaction by default, so if you make multiple inserts during a request and the request ultimately fails due to an error, the inserts should be rolled back automatically. See http://web2py.com/books/default/chapter/29/4#Workflow and http://web2py.com/books/default/chapter/29/6#commit-and-rollback. Anthony On Friday, December 23, 2011 1:22:34 PM UTC-5, Detectedstealth wrote: I have solved these kinds of concerns with pyramid based applications using sqlalchemy. However I need to look into the core of how Web2py handles things to figure out the best way. Sqlachemy allows you to put everything in: 1) Transactions 2) Sessions So on submit you first create the parent record: ad = Ad() session.add(ad) session.flush() Now you have access to ad.id (parent id) report = Report(...) report.ad_id = ad.id session.add(report) Now if you used Sqlachemy relationships ad.reports.append(report) # use this instead of report.ad_id = ad.id session.flush() Or in the case of having multiple children created before creating the parent on a web form. You just submit all the form data at once and loop through all reports. ad = Ad(...) session.add(ad) session.flush() get list of all reports in form submit for report in reports: session.add(report) ad.reports.append(report) session.flush() If anything fails the transaction automatically roles everything back. Or a easier solution is to only enable the children on edits. So you first create the parent, then you can view/edit/delete children on the parent edit page. -- Regards, Bruce On Fri, Dec 23, 2011 at 10:00 AM, Cliff cjk...@gmail.com wrote: Bruce, you are more brave than me ;) I always like to get the parent record committed in the database before adding child records. that way you know the ID of the parent record and it has the minimal required fieldset safely saved away. If there's a session interruption you don't have some potential orphan child records floating around somewhere. If you've solved those kinds of concerns, please share it with us. Thanks, Cliff Kachinske On Dec 23, 10:34 am, DenesL dene...@yahoo.ca wrote: On Dec 23, 10:11 am, Bruce Wade bruce...@gmail.com wrote: Anyway which files should I start looking at to see how I can implement this functionality for myself and anyone else who wants it? Look for controller appadmin under your or any other app folder. -- -- Regards, Bruce Wade http://ca.linkedin.com/in/brucelwade http://www.wadecybertech.com http://www.warplydesigned.com http://www.fitnessfriendsfinder.com
[web2py] Re: Many-to-many admin form
This something I would definitely be interested in seeing, as well as input forms for many-to-many in general (not just admin). Thanks Vincent On Dec 23, 12:50 pm, Anthony abasta...@gmail.com wrote: web2py wraps each request in a single transaction by default, so if you make multiple inserts during a request and the request ultimately fails due to an error, the inserts should be rolled back automatically. Seehttp://web2py.com/books/default/chapter/29/4#Workflow andhttp://web2py.com/books/default/chapter/29/6#commit-and-rollback. Anthony On Friday, December 23, 2011 1:22:34 PM UTC-5, Detectedstealth wrote: I have solved these kinds of concerns with pyramid based applications using sqlalchemy. However I need to look into the core of how Web2py handles things to figure out the best way. Sqlachemy allows you to put everything in: 1) Transactions 2) Sessions So on submit you first create the parent record: ad = Ad() session.add(ad) session.flush() Now you have access to ad.id (parent id) report = Report(...) report.ad_id = ad.id session.add(report) Now if you used Sqlachemy relationships ad.reports.append(report) # use this instead of report.ad_id = ad.id session.flush() Or in the case of having multiple children created before creating the parent on a web form. You just submit all the form data at once and loop through all reports. ad = Ad(...) session.add(ad) session.flush() get list of all reports in form submit for report in reports: session.add(report) ad.reports.append(report) session.flush() If anything fails the transaction automatically roles everything back. Or a easier solution is to only enable the children on edits. So you first create the parent, then you can view/edit/delete children on the parent edit page. -- Regards, Bruce On Fri, Dec 23, 2011 at 10:00 AM, Cliff cjk...@gmail.com wrote: Bruce, you are more brave than me ;) I always like to get the parent record committed in the database before adding child records. that way you know the ID of the parent record and it has the minimal required fieldset safely saved away. If there's a session interruption you don't have some potential orphan child records floating around somewhere. If you've solved those kinds of concerns, please share it with us. Thanks, Cliff Kachinske On Dec 23, 10:34 am, DenesL dene...@yahoo.ca wrote: On Dec 23, 10:11 am, Bruce Wade bruce...@gmail.com wrote: Anyway which files should I start looking at to see how I can implement this functionality for myself and anyone else who wants it? Look for controller appadmin under your or any other app folder. -- -- Regards, Bruce Wade http://ca.linkedin.com/in/brucelwade http://www.wadecybertech.com http://www.warplydesigned.com http://www.fitnessfriendsfinder.com
[web2py] Re: Many to many query with two left joins - how to do in Web2py?
Grazie. This unlocks a door. I thought I had to repeat the call to left() same as the SQL. On Oct 6, 5:13 pm, Massimo Di Pierro massimo.dipie...@gmail.com wrote: Here is your SQL: SELECT purchase_orders.issue_date, purchase_orders.number, products.name FROM purchase_orders LEFT JOIN purchase_order_products ON purchase_order_products.purchase_order_id = purchase_orders.id LEFT JOIN products ON purchase_order_products.product_id = products.id web2py-ese: db().select(db.purchase_orders.issue_date, db.purchase_orders.number, db.products.name, left=[db.purchase_order_products.on(db.purchase_order_products.purchase_order_id == db.purchase_orders.id), db.products.on(db.purchase_order_products.product_id == db.products.id)]) On Oct 6, 1:46 pm, Cliff cjk...@gmail.com wrote: Actually 'name' comes from products. Maybe it would be clearer this way: SELECT purchase_orders.issue_date, purchase_orders.number, products.name FROM purchase_orders LEFT JOIN purchase_order_products ON purchase_order_products.purchase_order_id = purchase_orders.id LEFT JOIN products ON purchase_order_products.product_id = products.id On Oct 6, 12:13 pm, Massimo Di Pierro massimo.dipie...@gmail.com wrote: Why do you left join products if you do not select anything from it? On Oct 6, 7:40 am, Cliff cjk...@gmail.com wrote: Here is the model, simplified: db.define_table('purchase_orders', Field('issue_date', 'date', comment = SPAN(a_comment, _style='color:orange;')), Field('number', length=24, requires=IS_NOT_EMPTY(), required=True, notnull=True, comment=SPAN('Required', _style='color:orange;')), Field('closed', 'boolean', readable=False, writable=False, default=False), format = '%(number)s' ) db.define_table( 'products', Field('name', length=256, required=True, notnull=True), Field('internal_item_number', length=36), format = '%(name)s' ) db.define_table('purchase_order_products', Field('purchase_order_id', db.purchase_orders), Field('product_id', db.products), Field('quantity', 'decimal(12,3)'), ) in SQL I can make this query: SELECT issue_date, number, name FROM purchase_orders LEFT JOIN purchase_order_products ON purchase_order_products.purchase_order_id = purchase_orders.id LEFT JOIN products ON purchase_order_products.product_id = products.id How do I do this in Web2py?
[web2py] Re: Many to many query with two left joins - how to do in Web2py?
Why do you left join products if you do not select anything from it? On Oct 6, 7:40 am, Cliff cjk...@gmail.com wrote: Here is the model, simplified: db.define_table('purchase_orders', Field('issue_date', 'date', comment = SPAN(a_comment, _style='color:orange;')), Field('number', length=24, requires=IS_NOT_EMPTY(), required=True, notnull=True, comment=SPAN('Required', _style='color:orange;')), Field('closed', 'boolean', readable=False, writable=False, default=False), format = '%(number)s' ) db.define_table( 'products', Field('name', length=256, required=True, notnull=True), Field('internal_item_number', length=36), format = '%(name)s' ) db.define_table('purchase_order_products', Field('purchase_order_id', db.purchase_orders), Field('product_id', db.products), Field('quantity', 'decimal(12,3)'), ) in SQL I can make this query: SELECT issue_date, number, name FROM purchase_orders LEFT JOIN purchase_order_products ON purchase_order_products.purchase_order_id = purchase_orders.id LEFT JOIN products ON purchase_order_products.product_id = products.id How do I do this in Web2py?
[web2py] Re: Many to many query with two left joins - how to do in Web2py?
Actually 'name' comes from products. Maybe it would be clearer this way: SELECT purchase_orders.issue_date, purchase_orders.number, products.name FROM purchase_orders LEFT JOIN purchase_order_products ON purchase_order_products.purchase_order_id = purchase_orders.id LEFT JOIN products ON purchase_order_products.product_id = products.id On Oct 6, 12:13 pm, Massimo Di Pierro massimo.dipie...@gmail.com wrote: Why do you left join products if you do not select anything from it? On Oct 6, 7:40 am, Cliff cjk...@gmail.com wrote: Here is the model, simplified: db.define_table('purchase_orders', Field('issue_date', 'date', comment = SPAN(a_comment, _style='color:orange;')), Field('number', length=24, requires=IS_NOT_EMPTY(), required=True, notnull=True, comment=SPAN('Required', _style='color:orange;')), Field('closed', 'boolean', readable=False, writable=False, default=False), format = '%(number)s' ) db.define_table( 'products', Field('name', length=256, required=True, notnull=True), Field('internal_item_number', length=36), format = '%(name)s' ) db.define_table('purchase_order_products', Field('purchase_order_id', db.purchase_orders), Field('product_id', db.products), Field('quantity', 'decimal(12,3)'), ) in SQL I can make this query: SELECT issue_date, number, name FROM purchase_orders LEFT JOIN purchase_order_products ON purchase_order_products.purchase_order_id = purchase_orders.id LEFT JOIN products ON purchase_order_products.product_id = products.id How do I do this in Web2py?
[web2py] Re: Many to many query with two left joins - how to do in Web2py?
Here is your SQL: SELECT purchase_orders.issue_date, purchase_orders.number, products.name FROM purchase_orders LEFT JOIN purchase_order_products ON purchase_order_products.purchase_order_id = purchase_orders.id LEFT JOIN products ON purchase_order_products.product_id = products.id web2py-ese: db().select(db.purchase_orders.issue_date, db.purchase_orders.number, db.products.name, left=[db.purchase_order_products.on(db.purchase_order_products.purchase_order_id == db.purchase_orders.id), db.products.on(db.purchase_order_products.product_id == db.products.id)]) On Oct 6, 1:46 pm, Cliff cjk...@gmail.com wrote: Actually 'name' comes from products. Maybe it would be clearer this way: SELECT purchase_orders.issue_date, purchase_orders.number, products.name FROM purchase_orders LEFT JOIN purchase_order_products ON purchase_order_products.purchase_order_id = purchase_orders.id LEFT JOIN products ON purchase_order_products.product_id = products.id On Oct 6, 12:13 pm, Massimo Di Pierro massimo.dipie...@gmail.com wrote: Why do you left join products if you do not select anything from it? On Oct 6, 7:40 am, Cliff cjk...@gmail.com wrote: Here is the model, simplified: db.define_table('purchase_orders', Field('issue_date', 'date', comment = SPAN(a_comment, _style='color:orange;')), Field('number', length=24, requires=IS_NOT_EMPTY(), required=True, notnull=True, comment=SPAN('Required', _style='color:orange;')), Field('closed', 'boolean', readable=False, writable=False, default=False), format = '%(number)s' ) db.define_table( 'products', Field('name', length=256, required=True, notnull=True), Field('internal_item_number', length=36), format = '%(name)s' ) db.define_table('purchase_order_products', Field('purchase_order_id', db.purchase_orders), Field('product_id', db.products), Field('quantity', 'decimal(12,3)'), ) in SQL I can make this query: SELECT issue_date, number, name FROM purchase_orders LEFT JOIN purchase_order_products ON purchase_order_products.purchase_order_id = purchase_orders.id LEFT JOIN products ON purchase_order_products.product_id = products.id How do I do this in Web2py?
[web2py] Re: Many to Many on GAE
where and how are the pictures stores? will you be adding them to the blobstore or references pictures elsewhere?
[web2py] Re: Many to Many on GAE
Thank you for reply. My problem is, I have problem in choosing one approach over other. 1) Use a mapping table 2) Or use list reference. My tables are something like this: Table :Picture Word Word: Coffee: Picture: Both the picture below represent word coffee. (ie., for one word many pictures) Similarly this picture can also mean black cofee (ie., for one picture many words) Obviously, I never have to render a form as in person- dog example (preson: a textbox, dog:as a list). Any advise on traditional methods v/s list reference?
[web2py] Re: Many to Many on GAE
Arun, this line: persons_and_dogs = db((db.person.id==db.ownership.person) (db.dog.id==db.ownership.dog)) won't run on GAE because of the joins. You can choose to keep the data structure as you have it and run 2 queries and mesh the results. before the advent of list properties i was doing that. it kinda sucked. Here's what i would try with your model: (note that i did not run this code, so it might contain typos...) Models - db.define_table('dog',Field('name')) db.define_table('person', Field('name'), 'dogs', 'list:reference dog') this assumes that a person has many dogs, and not that dogs have many people. if you prefer to think of it as dogs owning people i'd put the person field on the dog table. (you know your cat owns you, so for cats it would have to be that way) given that model, once you create dog records, each dog will show as an option in a multi-select list for the 'dogs' field on person. so use CRUD or create your own controllers with the default form renderings as in: form = SQLFORM(db.person) and put that in your view: {{=form}} to get the default inputs. when you want to see dogs and people together in your view you would select people: rows = db(db.person.id0).select() and in the view: {{for row in rows:}} person: {{=row.person}} dogs: {{for dog in row.dogs:}} {{=dog.dog}} {{pass}} br / {{pass}} note the trick that in web2py the list:reference field is a proper web2py table reference and when you access the properties on the dog reference it automatically runs the query and gives you the result. hope this makes some sense! christian christian
[web2py] Re: many to many inline form
I'm still learning widget stuff, and while I can design a many-to-many widget, I am having trouble visualizing how to produce one with extra fields and buttons for adding/removing. I might strike an epiphany, but I'll likely just wait for you to make something available. On Nov 12, 11:27 am, mdipierro mdipie...@cs.depaul.edu wrote: We do not have a widget for that but it is time to make one. It is not difficult. If I have time I will do this over the week-end. Other users may have already something working. On Nov 12, 10:17 am, Joe Wakefield coffeeburr...@gmail.com wrote: So I'm trying to migrate an old rails app that broke a while back, and I came across a rather difficult situation. I boiled the problem down to this simplified mockup:http://yfrog.com/ghbeanjarsp The real app has nothing to do with jelly beans, but this is the issue at hand. Basically there are tables joined in a many to many relationship, with an extra column for amount (because no bean is unique). Creating/updating jars must be done inline with the rest of the jar's form. I was reading stuff about custom widgets, and that seemed a likely direction. However, I'm hoping for a more direct suggestion on how you guys would achieve this.
[web2py] Re: many to many inline form
We do not have a widget for that but it is time to make one. It is not difficult. If I have time I will do this over the week-end. Other users may have already something working. On Nov 12, 10:17 am, Joe Wakefield coffeeburr...@gmail.com wrote: So I'm trying to migrate an old rails app that broke a while back, and I came across a rather difficult situation. I boiled the problem down to this simplified mockup:http://yfrog.com/ghbeanjarsp The real app has nothing to do with jelly beans, but this is the issue at hand. Basically there are tables joined in a many to many relationship, with an extra column for amount (because no bean is unique). Creating/updating jars must be done inline with the rest of the jar's form. I was reading stuff about custom widgets, and that seemed a likely direction. However, I'm hoping for a more direct suggestion on how you guys would achieve this.
[web2py] Re: many-to-many, use list:reference or separate table?
Speedwise, you should use many2many if you plan to search the referee by the refereed. If you only need the refereed after you already have the referee you can use list:reference. If you are on GAE list:reference is better because native and GAe has no joins. On Nov 9, 2:43 pm, Carlos carlosgali...@gmail.com wrote: Hi all, I am wondering, with respect to many-to-many relationships, is it better to use list:reference or a separate table (with foreign keys)?. I refer to 'simple' many-to-many where no extra attributes need to be stored (otherwise the separate table is the only choice). Thanks, Carlos
[web2py] Re: Many to Many TypeError: int() argument must be a string or a number, not 'list'
Thank you for the suggestions, Massimo. I've tried to update the model here: http://code.google.com/p/publicradioroadtrip/source/browse/models/db_custom.py based on your suggestions, and the example app here: http://www.web2py.com/book/default/chapter/03#A-Wiki However, if I use the line: Field('created_by', db.auth_user, default=auth.user_id, writable=False, readable=False), I receive the error: KeyError: 'auth_user' http://pastie.textmate.org/1188236 Tell me, is the issue with the user table related to the issue of making a many-to-many relationship between stories and roadtrips? Best regards, John T. On Sep 29, 6:42 am, mdipierro mdipie...@cs.depaul.edu wrote: just use auth.user_id is the same created_by should be type=db.auth_user On Sep 28, 10:31 pm, johntynan jgty...@gmail.com wrote: if auth.is_logged_in(): me=auth.user.id else: me=None On Sep 29, 5:19 am, mdipierro mdipie...@cs.depaul.edu wrote: what is me? Do you use auth? why the type of created_by is a string? On Sep 28, 10:01 pm, johntynan jgty...@gmail.com wrote: I would like to create a many-to-many relationship in the following model, where many stories can appear in many different roadtrips (think songs to mixtapes). db.define_table( 'roadtrip', Field('name'), Field('description', 'text'), Field('created_by',default=me,writable=False,readable=False), Field('created_on','datetime',default=request.now,writable=False,readable=F alse) ) db.define_table( 'story', Field('user_id', db.users,readable=False,writable=False), Field('roadtrip_id', db.roadtrip), Field('story_id', 'integer'), Field('title'), Field('latitude'), Field('longitude'), Field('comment', 'text'), Field('created_by',default=me,writable=False,readable=False), Field('created_on','datetime',default=request.now,writable=False,readable=F alse) ) db.story.roadtrip_id.requires=IS_IN_DB(db(db.roadtrip.created_by==me),'road trip.id','% (name)s') I am able to create a multiple-select element on the CRUD form by simply adding multiple=True to this db.story.roadtrip_id.requires=IS_IN_DB(db(db.roadtrip.created_by==me),'road trip.id','% (name)s', multiple=True) However, I am receiving the error: TypeError: int() argument must be a string or a number, not 'list' I posted the traceback here:http://pastie.textmate.org/1187996 (as an aside, is there a better way to post a traceback that is more readable, should I just copy/paste from the html error page, as opposed to the logged file under errors?)
[web2py] Re: Many to Many TypeError: int() argument must be a string or a number, not 'list'
Models are executed alphabetically. Make sure that auth=Auth(...) auth.define_tables() are executed before your table definition. On Sep 29, 1:13 am, johntynan jgty...@gmail.com wrote: Thank you for the suggestions, Massimo. I've tried to update the model here: http://code.google.com/p/publicradioroadtrip/source/browse/models/db_... based on your suggestions, and the example app here: http://www.web2py.com/book/default/chapter/03#A-Wiki However, if I use the line: Field('created_by', db.auth_user, default=auth.user_id, writable=False, readable=False), I receive the error: KeyError: 'auth_user' http://pastie.textmate.org/1188236 Tell me, is the issue with the user table related to the issue of making a many-to-many relationship between stories and roadtrips? Best regards, John T. On Sep 29, 6:42 am, mdipierro mdipie...@cs.depaul.edu wrote: just use auth.user_id is the same created_by should be type=db.auth_user On Sep 28, 10:31 pm, johntynan jgty...@gmail.com wrote: if auth.is_logged_in(): me=auth.user.id else: me=None On Sep 29, 5:19 am, mdipierro mdipie...@cs.depaul.edu wrote: what is me? Do you use auth? why the type of created_by is a string? On Sep 28, 10:01 pm, johntynan jgty...@gmail.com wrote: I would like to create a many-to-many relationship in the following model, where many stories can appear in many different roadtrips (think songs to mixtapes). db.define_table( 'roadtrip', Field('name'), Field('description', 'text'), Field('created_by',default=me,writable=False,readable=False), Field('created_on','datetime',default=request.now,writable=False,readable=F alse) ) db.define_table( 'story', Field('user_id', db.users,readable=False,writable=False), Field('roadtrip_id', db.roadtrip), Field('story_id', 'integer'), Field('title'), Field('latitude'), Field('longitude'), Field('comment', 'text'), Field('created_by',default=me,writable=False,readable=False), Field('created_on','datetime',default=request.now,writable=False,readable=F alse) ) db.story.roadtrip_id.requires=IS_IN_DB(db(db.roadtrip.created_by==me),'road trip.id','% (name)s') I am able to create a multiple-select element on the CRUD form by simply adding multiple=True to this db.story.roadtrip_id.requires=IS_IN_DB(db(db.roadtrip.created_by==me),'road trip.id','% (name)s', multiple=True) However, I am receiving the error: TypeError: int() argument must be a string or a number, not 'list' I posted the traceback here:http://pastie.textmate.org/1187996 (as an aside, is there a better way to post a traceback that is more readable, should I just copy/paste from the html error page, as opposed to the logged file under errors?)
[web2py] Re: Many to Many TypeError: int() argument must be a string or a number, not 'list'
what is me? Do you use auth? why the type of created_by is a string? On Sep 28, 10:01 pm, johntynan jgty...@gmail.com wrote: I would like to create a many-to-many relationship in the following model, where many stories can appear in many different roadtrips (think songs to mixtapes). db.define_table( 'roadtrip', Field('name'), Field('description', 'text'), Field('created_by',default=me,writable=False,readable=False), Field('created_on','datetime',default=request.now,writable=False,readable=False) ) db.define_table( 'story', Field('user_id', db.users,readable=False,writable=False), Field('roadtrip_id', db.roadtrip), Field('story_id', 'integer'), Field('title'), Field('latitude'), Field('longitude'), Field('comment', 'text'), Field('created_by',default=me,writable=False,readable=False), Field('created_on','datetime',default=request.now,writable=False,readable=False) ) db.story.roadtrip_id.requires=IS_IN_DB(db(db.roadtrip.created_by==me),'roadtrip.id','% (name)s') I am able to create a multiple-select element on the CRUD form by simply adding multiple=True to this db.story.roadtrip_id.requires=IS_IN_DB(db(db.roadtrip.created_by==me),'roadtrip.id','% (name)s', multiple=True) However, I am receiving the error: TypeError: int() argument must be a string or a number, not 'list' I posted the traceback here:http://pastie.textmate.org/1187996 (as an aside, is there a better way to post a traceback that is more readable, should I just copy/paste from the html error page, as opposed to the logged file under errors?)
[web2py] Re: Many to Many TypeError: int() argument must be a string or a number, not 'list'
if auth.is_logged_in(): me=auth.user.id else: me=None On Sep 29, 5:19 am, mdipierro mdipie...@cs.depaul.edu wrote: what is me? Do you use auth? why the type of created_by is a string? On Sep 28, 10:01 pm, johntynan jgty...@gmail.com wrote: I would like to create a many-to-many relationship in the following model, where many stories can appear in many different roadtrips (think songs to mixtapes). db.define_table( 'roadtrip', Field('name'), Field('description', 'text'), Field('created_by',default=me,writable=False,readable=False), Field('created_on','datetime',default=request.now,writable=False,readable=F alse) ) db.define_table( 'story', Field('user_id', db.users,readable=False,writable=False), Field('roadtrip_id', db.roadtrip), Field('story_id', 'integer'), Field('title'), Field('latitude'), Field('longitude'), Field('comment', 'text'), Field('created_by',default=me,writable=False,readable=False), Field('created_on','datetime',default=request.now,writable=False,readable=F alse) ) db.story.roadtrip_id.requires=IS_IN_DB(db(db.roadtrip.created_by==me),'road trip.id','% (name)s') I am able to create a multiple-select element on the CRUD form by simply adding multiple=True to this db.story.roadtrip_id.requires=IS_IN_DB(db(db.roadtrip.created_by==me),'road trip.id','% (name)s', multiple=True) However, I am receiving the error: TypeError: int() argument must be a string or a number, not 'list' I posted the traceback here:http://pastie.textmate.org/1187996 (as an aside, is there a better way to post a traceback that is more readable, should I just copy/paste from the html error page, as opposed to the logged file under errors?)
[web2py] Re: Many to Many TypeError: int() argument must be a string or a number, not 'list'
just use auth.user_id is the same created_by should be type=db.auth_user On Sep 28, 10:31 pm, johntynan jgty...@gmail.com wrote: if auth.is_logged_in(): me=auth.user.id else: me=None On Sep 29, 5:19 am, mdipierro mdipie...@cs.depaul.edu wrote: what is me? Do you use auth? why the type of created_by is a string? On Sep 28, 10:01 pm, johntynan jgty...@gmail.com wrote: I would like to create a many-to-many relationship in the following model, where many stories can appear in many different roadtrips (think songs to mixtapes). db.define_table( 'roadtrip', Field('name'), Field('description', 'text'), Field('created_by',default=me,writable=False,readable=False), Field('created_on','datetime',default=request.now,writable=False,readable=F alse) ) db.define_table( 'story', Field('user_id', db.users,readable=False,writable=False), Field('roadtrip_id', db.roadtrip), Field('story_id', 'integer'), Field('title'), Field('latitude'), Field('longitude'), Field('comment', 'text'), Field('created_by',default=me,writable=False,readable=False), Field('created_on','datetime',default=request.now,writable=False,readable=F alse) ) db.story.roadtrip_id.requires=IS_IN_DB(db(db.roadtrip.created_by==me),'road trip.id','% (name)s') I am able to create a multiple-select element on the CRUD form by simply adding multiple=True to this db.story.roadtrip_id.requires=IS_IN_DB(db(db.roadtrip.created_by==me),'road trip.id','% (name)s', multiple=True) However, I am receiving the error: TypeError: int() argument must be a string or a number, not 'list' I posted the traceback here:http://pastie.textmate.org/1187996 (as an aside, is there a better way to post a traceback that is more readable, should I just copy/paste from the html error page, as opposed to the logged file under errors?)
[web2py] Re: Many To Many Select
If I understand: for row in db(db.link_table.table1==r.id) (db.table2.id==db.linked_table.table2).select(db.table2.id,db.table2.other): print row.id, row.other if no double links else sub=db(db.link_table.table1==r.id)._select(db.linked_table.table2) for row in db(db.table2.id.belongs(sub)).select(db.table2.id,db.table2.other): print row.id, row.other linked_view = db(db.table1.id == db.link_table.table1) (db.table2.id == db.linked_table.table2)) for row in linked_view(db.table1.id == r.id).select([fields from table2 I want]) On 29 Giu, 17:54, Al rayalans...@yahoo.com wrote: Given a many-to-many relationship between table1 and table2 and a row in table1, I am trying to figure out the fastest way to select all the rows in table2 that are associated with the row. (I'm still trying to pick up web2py, so thanks for everyone's patience if I've missed the blatently obvious.) From the web2py book documentation, the best I can figure out is something like this: db.define_table('link_table', Field('table1', db.table1), Field('table2', db.table2) ) # Pick the row from table 1 r = db(db.table1.name == Example).select()[0] linked_view = db(db.table1.id == db.link_table.table1) (db.table2.id == db.linked_table.table2)) for row in linked_view(db.table1.id == r.id).select([fields from table2 I want]) # Do something clever for the matching rows I've found pass But I keep thinking there ought to be something cleverer that I can do, especially given that r has a set containing the rows of link_table that contain the entries of table2 that I want. But I can't figure out how to leverage that set into a shorter query. Thanks, Al
[web2py] Re: Many To Many Select
Ah, belongs() + a clever select SQL. Thanks. Al On Jun 29, 7:04 pm, mdipierro mdipie...@cs.depaul.edu wrote: If I understand: for row in db(db.link_table.table1==r.id) (db.table2.id==db.linked_table.table2).select(db.table2.id,db.table2.other): print row.id, row.other if no double links else sub=db(db.link_table.table1==r.id)._select(db.linked_table.table2) for row in db(db.table2.id.belongs(sub)).select(db.table2.id,db.table2.other): print row.id, row.other linked_view = db(db.table1.id == db.link_table.table1) (db.table2.id == db.linked_table.table2)) for row in linked_view(db.table1.id == r.id).select([fields from table2 I want]) On 29 Giu, 17:54, Al rayalans...@yahoo.com wrote: Given a many-to-many relationship between table1 and table2 and a row in table1, I am trying to figure out the fastest way to select all the rows in table2 that are associated with the row. (I'm still trying to pick up web2py, so thanks for everyone's patience if I've missed the blatently obvious.) From the web2py book documentation, the best I can figure out is something like this: db.define_table('link_table', Field('table1', db.table1), Field('table2', db.table2) ) # Pick the row from table 1 r = db(db.table1.name == Example).select()[0] linked_view = db(db.table1.id == db.link_table.table1) (db.table2.id == db.linked_table.table2)) for row in linked_view(db.table1.id == r.id).select([fields from table2 I want]) # Do something clever for the matching rows I've found pass But I keep thinking there ought to be something cleverer that I can do, especially given that r has a set containing the rows of link_table that contain the entries of table2 that I want. But I can't figure out how to leverage that set into a shorter query. Thanks, Al- Hide quoted text - - Show quoted text -