Thank you very much for the clear answer Anthony - I am just working my way though it!
On Tuesday, 17 November 2015 16:45:42 UTC, Anthony wrote: > > def index(): >> form = SQLFORM(db.tr_items) >> if form.process().accepted: >> item_in_out = form.vars.item >> amount_in_out = form.vars.amount >> row = db.products_summary(product=item_in_out) >> if row==None: >> db.products_summary.insert(product=item_in_out, >> cumulative_amount=amount_in_out) >> else: >> db.products_summary.update_or_insert(db.products_summary. >> product==item_in_out, product=item_in_out, cumulative_amount=db(db. >> products_summary.product==item_in_out).select(db.products_summary. >> cumulative_amount)[0]["cumulative_amount"]+form.vars.amount) >> > > The point of the update_or_insert method is that you don't have to > explicitly check for the record and then conditionally either do an insert > or an update. Yet in your code, you first attempt to retrieve the record, > and then you conditionally do an insert or an update_or_insert. Of course, > the update_or_insert part of the code will never do an insert, because that > case is already handled by the previous condition -- so it might as well > just be an update. > > Also, within your update_or_insert, you are doing a query to once again > retrieve the db.products_summary.product==item_in_out record, even though > you have already retrieved that same record (stored in the "row" variable). > > In short, you should either do everything explicitly (i.e., attempt to > retrieve the record and then conditionally insert or update yourself), or > just do an update_or_insert and let that method handle everything. Of > course, if the update_or_insert approach requires you to explicitly select > the record anyway (as you are doing to increment the value), you lose the > benefit of using this method and should instead just handle everything > explicitly. > > In this case, you can simplify somewhat to: > > if form.process().accepted: > row = db.products_summary(product=form.vars.item) > if row: > row.update_record(cumulative_amount=row.cumulative_amount + > form.vars.amount) > else: > db.products_summary.insert(product=form.vars.item, > cumulative_amount=form.vars.amount) > > As an aside, when doing a regular update (not and update_or_insert), you *can > *increment a value without first selecting the record: > > db(db.mytable).update(myfield=db.mytable.myfield + some_value) > > In an update, if you set the value of a field to an expression that > includes the field object itself, the field object gets converted to its > string representation (which is just "tablename.fieldname") in the > generated SQL. So, the above would generate SQL like (assuming some_value > is equal to 1): > > UPDATE mytable SET myfield=(mytable.myfield + 1) WHERE (mytable.id IS NOT > NULL); > > You can't do this with an update_or_insert, though, because in the case of > an insert, the mytable.myfield + 1 SQL expression would fail. > > 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
