[web2py] Re: database query on nested tables
hi, consider this : # e.g. 1 work for row in db(db.product).select(): print row.rack.branch # e.g. 2 not work print db(db.product.rack.branch == 1).select() is it possible to have the example 2 work? thanks and 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: database query on nested tables
On Wednesday, April 10, 2013 7:31:35 AM UTC-7, Niphlod wrote: I did say see it prettydate as it is takes a date and calculates automatically the timedelta to now. You need to have your own prettydate to be able to pass a timedelta of your own. If you've got a datetime.datetime kind of date, which is what the DAL returns, then the usual Python functions should allow you to pull pieces out, or just use strftime(). If you're working with a datetime.timedelta object, then you can also pull out the pieces pretty easily, which should make it easy to emulate prettydate. /dps -- 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: database query on nested tables
John Resig wrote a 'prettydate' that is probably what he wants... http://ejohn.org/blog/javascript-pretty-date/ On Wednesday, April 10, 2013 7:31:35 AM UTC-7, Niphlod wrote: I did say see it prettydate as it is takes a date and calculates automatically the timedelta to now. You need to have your own prettydate to be able to pass a timedelta of your own. -- 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: database query on nested tables
On Tuesday, April 8, 2014 2:44:52 PM UTC+2, 黄祥 wrote: hi, consider this : # e.g. 1 work for row in db(db.product).select(): print row.rack.branch # e.g. 2 not work print db(db.product.rack.branch == 1).select() is it possible to have the example 2 work? no, you can't. db.table.field == something is something that can be passed to the underlying db. db.table.field.linked_table is something that web2py does recursively (yep, pretty bad) to pretty print what the database stores as records in separate tables linked by a FK. in your case, you'd need a simple join. e.g. db( (db.table.field == db.ref_table.id) (db.ref_table.value == 'something') ).select(db.table.ALL) @Derek: that does exactly what web2py's prettyprint does. @all: do yourself a favour and start embracing moment.js ^__^ -- 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: database query on nested tables
now, i know the reason why, thank you so much for your explaination, niphlod. best regards, stifan On Wednesday, April 9, 2014 4:49:26 AM UTC+7, Niphlod wrote: On Tuesday, April 8, 2014 2:44:52 PM UTC+2, 黄祥 wrote: hi, consider this : # e.g. 1 work for row in db(db.product).select(): print row.rack.branch # e.g. 2 not work print db(db.product.rack.branch == 1).select() is it possible to have the example 2 work? no, you can't. db.table.field == something is something that can be passed to the underlying db. db.table.field.linked_table is something that web2py does recursively (yep, pretty bad) to pretty print what the database stores as records in separate tables linked by a FK. in your case, you'd need a simple join. e.g. db( (db.table.field == db.ref_table.id) (db.ref_table.value == 'something') ).select(db.table.ALL) @Derek: that does exactly what web2py's prettyprint does. @all: do yourself a favour and start embracing moment.js ^__^ -- 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: database query on nested tables
sorry the table for check out is db.define_table('check_out', Field('check_in', 'reference check_in'), Field('room'), Field('guest'), Field('description', 'text'), Field('duration'), format='%(guest)s %(room)s') i extract the room and guest value from check_in table and insert it into check_out in my controller. -- --- 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: database query on nested tables
check_in = db.check_in[id] print check_in.room.category.price On Wednesday, 10 April 2013 04:38:08 UTC-5, 黄祥 wrote: hi folks, i want to get the value of table row that have 3 nested values. my goal is to get the price value from table room_category from function check_out in my controller. how to do this in web2py efficient way? e.g. db.define_table('room_category', Field('category'), *Field('price', 'decimal(10,2)'),* format='%(category)s') db.define_table('room', Field('room_no'), Field('category', 'reference room_category'), Field('status'), Field('branch', 'reference branch'), format=lambda r: '%s-%s' % (r.branch.address, r.room_no)) db.define_table('check_in', Field('is_booking', 'boolean'), Field('booking', 'reference booking'), Field('room', 'reference room'), Field('guest', 'reference guest'), Field('description', 'text'), format='%(description)s') db.define_table('check_out', Field('check_in', 'reference check_in'), Field('room', 'reference room'), Field('guest', 'reference guest'), Field('description', 'text'), Field('duration'), format='%(guest)s %(room)s') thank you so much in advance -- --- 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: database query on nested tables
PS. Anyway, I would copy the price into check_in and check_out tables. I know this is redundant but in any hotel you will have to handle discounts for each individual client (to handle complaints, friends, etc.) This will give you flexibility and make it faster. On Wednesday, 10 April 2013 08:16:15 UTC-5, Massimo Di Pierro wrote: check_in = db.check_in[id] print check_in.room.category.price On Wednesday, 10 April 2013 04:38:08 UTC-5, 黄祥 wrote: hi folks, i want to get the value of table row that have 3 nested values. my goal is to get the price value from table room_category from function check_out in my controller. how to do this in web2py efficient way? e.g. db.define_table('room_category', Field('category'), *Field('price', 'decimal(10,2)'),* format='%(category)s') db.define_table('room', Field('room_no'), Field('category', 'reference room_category'), Field('status'), Field('branch', 'reference branch'), format=lambda r: '%s-%s' % (r.branch.address, r.room_no)) db.define_table('check_in', Field('is_booking', 'boolean'), Field('booking', 'reference booking'), Field('room', 'reference room'), Field('guest', 'reference guest'), Field('description', 'text'), format='%(description)s') db.define_table('check_out', Field('check_in', 'reference check_in'), Field('room', 'reference room'), Field('guest', 'reference guest'), Field('description', 'text'), Field('duration'), format='%(guest)s %(room)s') thank you so much in advance -- --- 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: database query on nested tables
brilliant, very simple solution, it's work now. thank you so much for your solution and suggestion, massimo i've followed your hints and make it on my controller : def __onvalidation_check_out(form): if form.vars.check_in: rows = db(db.check_in.id==form.vars.check_in).select() for row in rows: form.vars.room = row.room form.vars.guest = row.guest form.vars.price = row.room.category.price delta = request.now - row.created_on form.vars.duration = delta form.vars.total_price = delta.days * row.room.category.price else: form.errors.check_in = 'enter a value' another question is it possible to make time delta more human readable? for example on my code above the time delta return : 9 days, 0:00:34.024219 is it possible to make it? 9 days, 0:00:34.024219 = 9 days, 34 seconds 9 days, 0:01:34.024219 = 9 days 1 minute 34 seconds 9 days, 0:02:34.024219 = 9 days 2 minutes 34 seconds 9 days, 1:01:34.024219 = 9 days 1 hour 1 minute 34 seconds 9 days, 2:01:34.024219 = 9 days 2 hours 1 minute 34 seconds thanks and best regards -- --- 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: database query on nested tables
check prettydate http://web2py.com/books/default/chapter/29/14?search=prettydate On Wednesday, April 10, 2013 3:45:19 PM UTC+2, 黄祥 wrote: brilliant, very simple solution, it's work now. thank you so much for your solution and suggestion, massimo i've followed your hints and make it on my controller : def __onvalidation_check_out(form): if form.vars.check_in: rows = db(db.check_in.id==form.vars.check_in).select() for row in rows: form.vars.room = row.room form.vars.guest = row.guest form.vars.price = row.room.category.price delta = request.now - row.created_on form.vars.duration = delta form.vars.total_price = delta.days * row.room.category.price else: form.errors.check_in = 'enter a value' another question is it possible to make time delta more human readable? for example on my code above the time delta return : 9 days, 0:00:34.024219 is it possible to make it? 9 days, 0:00:34.024219 = 9 days, 34 seconds 9 days, 0:01:34.024219 = 9 days 1 minute 34 seconds 9 days, 0:02:34.024219 = 9 days 2 minutes 34 seconds 9 days, 1:01:34.024219 = 9 days 1 hour 1 minute 34 seconds 9 days, 2:01:34.024219 = 9 days 2 hours 1 minute 34 seconds thanks and best regards -- --- 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: database query on nested tables
when i tried to using prettydate it return [invalid date] e.g. *from gluon.tools import prettydate* def __onvalidation_check_out(form): if form.vars.check_in: rows = db(db.check_in.id==form.vars.check_in).select() for row in rows: form.vars.room = row.room form.vars.guest = row.guest delta = request.now - row.created_on *form.vars.duration = prettydate(delta,T)* form.vars.price = row.room.category.price form.vars.total_price = delta.days * row.room.category.price else: form.errors.check_in = 'enter a value' any idea how to use prettydate output in sqlform.grid? thank you very much -- --- 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: database query on nested tables
I did say see it prettydate as it is takes a date and calculates automatically the timedelta to now. You need to have your own prettydate to be able to pass a timedelta of your own. -- --- 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.