There is a little problem with the original solution: If all categories are
already assigned to specific item, it is not possible to edit any of the
assignment.
because the drop down list is empty. This is not a problem if you do not
have any other fields (ex. comment) attached to the assignment, you can
just delete it and reasigne the same category if you wish. It is solved by
building on top of Antony's idea by including the current category in the
available categories in case of edit:
This might be a cosmetic "improvement" or a demonstration of the power of
web2py. The next challenge will be to remove the "Add new record" button
from the grid in case all categories are assigned. This can probably done
by counting the available categories and counting the assigned categories
just before showing the grid. If they are equal then there is no need to
show the add button (create =False)
def show_grid():
if 'item_category.item' in request.args:
item_id = int(request.args[request.args.index('item_category.item')
+ 1])
used_categories = db(db.item_category.item ==
item_id)._select(db.item_category.category)
if 'edit' in request.args:
item_category_id =
int(request.args[request.args.index('item_category') + 1])
current_category=db(db.item_category.id==
item_category_id)._select(db.item_category.category)
available_categories =
db(~db.category.id.belongs(used_categories) |
db.category.id.belongs(current_category))
else:
available_categories =
db(~db.category.id.belongs(used_categories))
db.item_category.category.requires = IS_IN_DB(available_categories,
'category.id', '%(name)s')
grid = SQLFORM.smartgrid(db.item)
return dict(grid=grid)
On Thursday, December 15, 2016 at 8:18:18 PM UTC+1, icodk wrote:
>
> Found explanation in :
>
> http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer?search=._select%28%29
>
> On Thursday, December 15, 2016 at 6:16:46 PM UTC+1, Anthony wrote:
>>
>> Looks like your current validator will not allow a category to be used
>> twice in the entire item_category table, which is not consistent with a
>> many-to-many relationship. Is that your intention? Or do you instead want
>> to ensure that there are no duplicate item/category *combinations* in
>> the item_category table. If the latter, you can do something like the
>> following, which will not only provide the proper validation, but will also
>> limit the list of categories in the select widget to those that have not
>> already been paired with the current item:
>>
>> def show_grid():
>> if 'item_category.item' in request.args:
>> item_id = int(request.args[request.args.index(
>> 'item_category.item') + 1])
>> used_categories = db(db.item_category.item == item_id)._select(db
>> .item_category.category)
>> available_categories = db(~db.category.id.belongs(used_categories
>> ))
>> db.item_category.category.requires = IS_IN_DB(
>> available_categories, 'category.id', '%(name)s')
>> grid = SQLFORM.smartgrid(db.item)
>> return dict(grid=grid)
>>
>> In smartgrid, when you are viewing item-categories for a given item,
>> request.args will include "item_category.item", with the immediately
>> following arg being the id of the item being viewed. So, in the above code,
>> item_id is the id of the specific item in question. use_categories is a
>> query that determines which categories have already been paired with this
>> item in the item_categories table. It uses ._select so it can be passed
>> to .belongs as a subquery. available_categories is a Set object that
>> identifies categories that have *not* been paired with the current item
>> in item_categories. When you pass a Set object to IS_IN_DB, it restricts
>> values to records that are in the Set, so the validator will only allow
>> (and display in the form) categories not already assigned to the item.
>>
>> Anthony
>>
>> On Wednesday, December 14, 2016 at 5:31:11 PM UTC-5, icodk wrote:
>>>
>>> I have an item ,category and item_category tables facilitating many to
>>> many relations between items and categories, which enables an item to
>>> belongs to several categories. I use SQLSmartgrid to show the
>>> item_category table and to add categories to specific item. This all works
>>> well. I can even managed to get an error if I select an already selected
>>> category by using the following:
>>>
>>> db.item_category.category.requires = IS_IN_DB(db, 'category', '%(name)s',
>>> _and=IS_NOT_IN_DB(db, 'item_category.category', error_message=T('This
>>> category already assigned')), zero=None)
>>>
>>>
>>> So what more could I wish ?
>>>
>>> It would be nice if the category dropdown list I get when adding or editing
>>> category to an item would contains only the categories not yet assigned to
>>> the item I am working on
>>>
>>> At the moment I get a list with all categories, however selecting an
>>> existing category correctly give me an error as described above.
>>>
>>>
>>>
--
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 [email protected].
For more options, visit https://groups.google.com/d/optout.
