CODEWITHSUNDEEP
sqlitepython-2.7web2py
A Magoon
A Magoon

Reputation: 1210

Reference another table in SQLite using Web2py/Python

I'm new to Web2py and SQL Databases in general, so forgive me if this is a dumb question.

What I'm trying to do is create a form that pulls relevant data (type, label, IS_NOT_EMPTY(), etc.) from an SQL table's field to populate and configure input elements on the frontend. So far I've successfully been able to do this using the Web2py Manual as an excellent reference.

I've gotten to the point where I'd like to use multiple tables to help categorize sets of data more effectively. Best case scenario would be to have one master table that can be called in my controller file (default.py), that is linked with other relevant (but not always needed) tables in my db.

In the below code you can see that I define the 'category_t' table first, then define the 'new_product' table. I then attempt to reference 'category_t' table within the 'main_category' field on new_product. The 'main_category' field ends up being a multiple select element on the form, which is exactly what I want. I just need to use the field labels from 'category_t' as the multi-select options.

I was using this Links to referencing records instructions from the Web2py Manual, but I believe I'm misinterpreting what it's actually capable of. I desperately need someone to school me on the laws of Web2py and SQLite. 

db.define_table(
    'category_t',
    Field('category1', 'boolean', label='Category 1'),
    Field('category2', 'boolean', label='Category 2'),
    Field('category3', 'boolean', label='Category 3'),
)

db.define_table(
    'new_product',
    Field('name', requires=[IS_NOT_EMPTY(), IS_ALPHANUMERIC()], label='Product Name'),
    Field('sku', requires=[IS_NOT_EMPTY(), IS_ALPHANUMERIC()], label='SKU'),
    Field('build', requires=IS_IN_SET(['Build1','Build2','Build3']), label='Product Type'),
    Field('main_category', 'list:reference category_t', label='Category'),
)


Update

I have discovered that it's as easy as adding the ancillary table within your main table to get it to display on the front end. I have not yet checked whether it's able to update without error. 

db.define_table(
    'new_product',
    Field('name', requires=[IS_NOT_EMPTY(), IS_ALPHANUMERIC()], label='Product Name'),
    Field('sku', requires=[IS_NOT_EMPTY(), IS_ALPHANUMERIC()], label='SKU'),
    Field('build', requires=IS_IN_SET(['Build1','Build2','Build3']), label='Product Type'),
    db.category_t
)


I have not yet figured out how to display it as a multi-select element. As of right now, it displays as a series of check boxes.

Upvotes: 1

Views: 1367

Answers (1)

Anthony
Anthony

Reputation: 25536

The code in your update is not doing what you think it is doing. By adding db.category_t to the new_product table definition, you are simply adding a completely separate set of category fields to the new_product table. The data for those fields will be stored in the new_product table, and nothing will be stored in the category_t table. If you just want a small fixed set of boolean category fields, then the category_t table is unnecessary -- you should just define those fields in new_product. However, each category will be a separate input, so you will not have a single multi-select.

Probably what you want instead is a list:string or list:reference field. The simplest approach is a list:string field:

db.define_table('new_product', ...,
    Field('main_categories', 'list:string', label='Categories',
          requires=IS_IN_SET(['Category 1', 'Category 2', 'Category 3'],
                             multiple=True)))

The above will store a list of category names in the main_categories field, and the IS_IN_SET validator will restrict the options to a particular set of categories, which will be displayed in a mult-select box (due to the multiple=True argument). No need for a separate category table in this case.

If you want to maintain a separate category table for some reason (e.g., there is some metadata associated with each category, or you expect to be editing the category names frequently), you can instead use a list:reference field. However, the category table should not be defined as in your example, but should have a separate record for each category:

db.define_table('category',
    Field('name'),
    [other category metadata fields if needed],
    format='%(name)s')

db.define_table('new_product', ...,
    Field('main_categories', 'list:reference category', label='Categories')

In this case, the main_categories field will get a default IS_IN_DB validator as well as a multi-select widget showing the names of the categories (this is enabled by virtue of the "format" argument in the category table definition, which is set to the "name" field).

A final option is to set up a full many-to-many schema, which requires a third table. However, this will complicate setting up form entry, as there is no built-in method to create a new_product entry form with a multi-select for the many-to-many relationship.

Upvotes: 2

Related Questions