one to many relationship

[Yebach]

Hello

How to solve the problem of one to many relationship:

Lets say I have a worker that has multiple skills. How to set up SQLFORM to add as many skills to the worker as possible.

So skills in one table workers in another.I guess a third table will be needed

Thank you

best regards

[Lisandro]

Hi there.

Web2py has some cool field types called "list:string", "list:integer" and "list:reference table". Check them here:

http://www.web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Field-types

In your case, you could avoid the creation of the third table with list:reference table, for example, in your db.py:

db.define_table('skill', Field('name'))

db.define_table('worker',\
    Field('name'),\
    Field('skills','list:reference skill'))

Then you can do things like:

skill_1 = db.skill.insert(name='coding in python')
skill_2 = db.skill.insert(name='making a cake')
skill_3 = db.skill.insert(name='writing a song')

db.worker.insert(name='John', skills=[skill_1, skill_2])
db.worker.insert(name='Paul', skills=[skill_3])
db.worker.insert(name='Richard', skills=[skill_2, skill_3])

# search which workers have skill_2:
have_skill_3 = db(db.worker.skills.contains(skill_2)).select()

The list:reference field will be rendered as a select with multiple=True.

You can always write your own widget to use other stuff, like checkboxes.

Look for "list:reference" in this group, you will lots of posts about it. 

Hope it helps!

[Vid Ogris]

Thank you for detailed answer

I am trying to implement this in SQLFORM? Looks like multiple option is not avaliable, the field is presented as text field in the form

BTW - why web2py does not add new field to a table. If I set migrate to True I get an error "table already exists" if it is set to false new fields were not created so I had to create them manually ??

Thank you

--
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/HjhQdAZWFWY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Lep pozdrav 

Vid Ogris

[Dave S]

On Monday, August 10, 2015 at 7:32:31 PM UTC-7, Yebach wrote:

Thank you for detailed answer

I am trying to implement this in SQLFORM? Looks like multiple option is not avaliable, the field is presented as text field in the form

BTW - why web2py does not add new field to a table. If I set migrate to True I get an error "table already exists" if it is set to false new fields were not created so I had to create them manually ??

Thank you

Which database are you interfacing to?

/dps
 

[Vid Ogris]

Postgres. The thimg is i have no table files. They somehow got deleted

[Yebach]

To create one to many form i tried to follow the post from this guy http://blog.jotbe-fx.de/articles/2522/web2py-Normalized-many-to-many-model-with-multiselect-drop-down

Also to create the dropdown etc.

The thing worked kind of but the problem was that my grid was not getting populated once you wanted to edit the new record etc.

I got stuck here with these code.

If anybody has some extra time to go trough and help me optimize it I would appreciate it otherwise I will go with normal list: reference field

Thank you

@auth.requires_login()

def workers():

#za nekatere polja (w_user in W_organizacija) rabmo default vrednosti, ki jih ne more nastavljat uporabnik

user = auth.user_id

org = db(db.auth_user.id == user).select(db.auth_user.organization)[0]["organization"]

db.workers.w_user.default = user

db.workers.w_organisation.default = org

#Load workers

#workers = db((db.workers.w_organisation == 10) & (db.workers.w_status== db.status.id)).select(db.workers.id,db.workers.w_status, db.workers.w_organisation, db.workers.w_first_name, db.workers.w_last_name,\

# db.workers.w_nick_name,db.workers.w_email,db.status.s_code,db.workers.w_note)

#print workers

#####NAredimo grrid za šifrant delavcev

#Define the query object. Here we are pulling all contacts having date of birth less than 18 Nov 1990

query = ((db.workers.w_organisation == org) & ((db.workers.w_status == 1) or (db.workers.w_status == 90)))

query_inactive = db((db.workers.w_organisation == org) & (db.workers.w_status == 100)).select().as_list()

#print query_inactive

 

#Define the fields to show on grid. Note: (you need to specify id field in fields section in 1.99.2

fields = (#db.workers.id,

db.workers.w_first_name,

db.workers.w_last_name,

#db.status.s_code,

db.workers.w_nick_name,

db.workers.w_email,

db.workers_skills.skill,

db.workers.w_note)

#Let's specify a default sort order on date_of_birth column in grid

default_sort_order=[db.workers.w_last_name]

db.workers.w_organisation.readable = db.workers.w_user.readable = False

db.workers.w_organisation.writable = db.workers.w_user.writable = False

db.workers.w_organisation.editable = db.workers.w_user.editable = False

#Nardiš polje bl text like :) WIU WIU

db.workers.w_first_name.widget = SQLFORM.widgets.string.widget

db.workers.w_last_name.widget = SQLFORM.widgets.string.widget

db.workers.w_nick_name.widget = SQLFORM.widgets.string.widget

db.workers.w_email.widget = SQLFORM.widgets.string.widget

#VAlidatorji

#db.workers.w_status.requires = IS_IN_DB(db,db.status.s_code) #tega sm rešu v db.py

db.workers.w_nick_name.requires = [IS_NOT_EMPTY(error_message=T('Missing nick name'))]

db.workers.w_first_name.requires = [IS_NOT_EMPTY(error_message=T('Missing first name'))]

db.workers.w_email.requires = IS_EMAIL(error_message=T('Incorrect e-mail address'))

#form = SQLFORM.smartgrid(db.workers,linked_tables=['status'])

#Creating the grid object

if (request.args) and (request.args[0] in ['viev', 'edit', 'new']):

skills = [(r.id, r.sk_name) for r in db(db.skills).select()]

grid_workers = SQLFORM.factory(

            db.workers,

           

            Field('w_status', type='integer', label= T('Status'), widget = SQLFORM.widgets.options.widget, default = 1),

    Field('w_first_name',type='text', label= T('First name'),represent=repr),

      Field('w_last_name',type='text', label= T('Last name'),represent=repr),

      Field('w_nick_name',type='text', label= T('Nick name'),represent=repr),

    Field('w_email',type='text', label= T('e-mail'),represent=repr),

#    Field('w_skills','list:reference skills',requires = IS_IN_DB(db,db.skills.id,'%(sk_name)s',multiple=True),label= T('Skills')),

#    Field('w_groups','list:reference groups',requires = IS_IN_DB(db,db.groups.id,'%(gr_name)s',multiple=True),label= T('Groups')),

            Field('skills',requires=IS_IN_SET(skills, multiple=True)),

Field('w_note',type='text', label= T('Comment'),represent=repr))

# (3) Validate form data

       if grid_workers.process().accepted:

# (4) Insert package

           

worker_insert = db.workers.insert(

               **db.workers._filter_fields(grid_workers.vars))

if worker_insert and grid_workers.vars.skills:

# (5) Insert component package associations

worker = db(db.workers)

for skills in grid_workers.vars.skills:

existing_skill = db.skills(id)

db.workers_skills.insert(

                            skill=skills,

                            worker=worker_insert

                        )

response.flash = "New record created"

else:

grid_workers= SQLFORM.grid(query=query, 

left=db.status.on(db.workers.w_status == db.status.id),

fields=fields,  searchable=False, orderby=[db.workers.w_nick_name],create=True,

deletable=False, editable=True, paginate=50, buttons_placement = 'right',

showbuttontext = False,

#oncreate=myfunction,

ui = dict(widget='',

         header='',

         content='',

         default='',

         cornerall='',

         cornertop='',

         cornerbottom='',

         button='button btn btn-default',

         buttontext='buttontext button',

         buttonadd='icon plus icon-plus glyphicon glyphicon-plus',

         buttonback='icon leftarrow icon-arrow-left glyphicon glyphicon-arrow-left',

         buttonexport='icon downarrow icon-download glyphicon glyphicon-download',

         buttondelete='icon trash icon-trash glyphicon glyphicon-trash',

         buttonedit='icon pen icon-pencil glyphicon glyphicon-pencil',

         buttontable='icon rightarrow icon-arrow-right glyphicon glyphicon-arrow-right',

         buttonview='icon magnifier icon-zoom-in glyphicon glyphicon-eye-open',

         ),

exportclasses  = dict(csv_with_hidden_cols=False, html = False, tsv = False, tsv_with_hidden_cols=False, json = False))

# if (request.args) and (request.args[0] in ['view', 'edit', 'new']):

# grid_workers.element('[title=Back]').parent['_href'] = URL('settings','workers')

# # (1) Get available components

#table.import_from_csv_file(file)

    #db.person.import_from_csv_file(open('test.csv', 'r')) 

    

formImport =  SQLFORM.factory(Field('csvfile','upload',uploadfield=False))

formImport.process()

if formImport.accepted:

# import csv

# fle =  csv.reader(request.vars.csvfile.file.read().splitlines())

# line_count = sum(1 for row in fle)

# print line_count

# if line_count <= 100:

try:

db.workers.import_from_csv_file(request.vars.csvfile.file, delimiter=";")

except: 

formImport.errors.csvfile = T('Invalid file format.')

# else:

# formImport.errors.csvfile = T('File is too big. Max 100 rows')

return dict(grid_workers = grid_workers, inactive_workers = query_inactive, formImport=formImport)