[web2py] Re: DAL - join on ROW_NUMBER()
On Sunday, September 27, 2015 at 2:49:33 PM UTC-7, lfestevao wrote: > > Hy there, I have a query ready and works alright with executesql. > I'm trying to change queries to use the dal but there's a problem in this > case. > What's your goal in switching from executesql() to using the dal? > > I have 2 tables related only by entry order, not by id's or some other key. > It's a legacy database and these info are in separate tables because these > info are filled by 2 different workers. > > For example: > In process 001205 (CodProtocolo) we have 3 documents. > One worker will give them some classification code, like 2,5 and 10 > (meaningful for some purpose) in one program for the process 001205. > Then he identifies them (physically) as 1,2,3 (always in code's ascending > order) > Another person will scan them in the specific order 1,2,3. > This other program fills the table only inserting all into the process > 001205 with some data, the image blob and give one code for each. > > I know they are in order so I can use: > > ROW_NUMBER() OVER(ORDER BY CodImagem) as ImgIndex > > and > > ROW_NUMBER() OVER(ORDER BY CodDocumento) as DocIndex > > and JOIN them > > Is there a way to accomplish it using the dal? > > I'm not sure I'm keeping track of what's going on here. Perhaps you could show your table defs and some sample rows? /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: DAL Join
Looks like your query simply isn't returning any results. 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 web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
Re: [web2py] Re: DAL Join
Thank you, it turned out to be a problem in my loop. one query returned null. As you said On Thu, Feb 20, 2014 at 4:35 AM, Anthony abasta...@gmail.com wrote: Looks like your query simply isn't returning any results. 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 a topic in the Google Groups web2py-users group. To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/sZa1Jhruzao/unsubscribe. To unsubscribe from this group and all its topics, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out. -- 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/groups/opt_out.
[web2py] Re: DAL Join
Should be rows = db(db.weets.posted_by==user).select(join=db.auth_user.on(db.weets.posted_by== db.auth_user.id)) row = rows.first() print row.auth_user.name Does it work? Also try (should be the same): rows = db(db.weets.posted_by==user)(db.weets.posted_by==db.auth_user.id). select() row = rows.first() print row.auth_user.name On Wednesday, 19 February 2014 23:18:33 UTC-6, contact.ur...@gmail.com wrote: Hi, Maybe a stupid question but here goes. I am trying to do the following join. row = db(db.weets.posted_by==user).select(join=db.auth_user.on(db.weets.posted_by== db.auth_user.id)) its seems to work and row does return. The example in the book then shows you can use attributes but when i try: row.auth_user.name or other field it says no attribute auth_user. i am not sure why this is? -- 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/groups/opt_out.
[web2py] Re: DAL Join
A select always returns a Rows object, not a Row object, even if there is always one record. So, instead, do: row = db(db.weets.posted_by == user).select( join=db.auth_user.on(db.weets.posted_by == db.auth_user.id)).first() Anthony On Thursday, February 20, 2014 12:18:33 AM UTC-5, contact.ur...@gmail.com wrote: Hi, Maybe a stupid question but here goes. I am trying to do the following join. row = db(db.weets.posted_by==user).select(join=db.auth_user.on(db.weets.posted_by== db.auth_user.id)) its seems to work and row does return. The example in the book then shows you can use attributes but when i try: row.auth_user.name or other field it says no attribute auth_user. i am not sure why this is? -- 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/groups/opt_out.
[web2py] Re: DAL Join
I tried both suggestions by yourself and the suggestion by Anthony. They all return a dictionary as before but my i am still getting the attribute error. may be it is something i am doing ? Here is the error output if it helps Traceback (most recent call last): File /home/www-data/web2py/gluon/restricted.py, line 217, in restricted exec ccode in environment File /home/www-data/web2py/applications/init/controllers/api.py https://urban-gatherer.ca/admin/default/edit/init/controllers/api.py, line 124, in module File /home/www-data/web2py/gluon/globals.py, line 372, in lambda self._caller = lambda f: f() File /home/www-data/web2py/applications/init/controllers/api.py https://urban-gatherer.ca/admin/default/edit/init/controllers/api.py, line 8, in call return service() File /home/www-data/web2py/gluon/tools.py, line 4915, in __call__ return self.serve_json(request.args[1:]) File /home/www-data/web2py/gluon/tools.py, line 4603, in serve_json s = universal_caller(self.json_procedures[args[0]], *args[1:], **d) File /home/www-data/web2py/gluon/tools.py, line 4274, in universal_caller return f(**arg_dict) File /home/www-data/web2py/applications/init/controllers/api.py https://urban-gatherer.ca/admin/default/edit/init/controllers/api.py, line 117, in news company = row.auth_user.company_name AttributeError: 'NoneType' object has no attribute 'auth_user' On Wednesday, February 19, 2014 10:15:16 PM UTC-8, Massimo Di Pierro wrote: Should be rows = db(db.weets.posted_by==user).select(join=db.auth_user.on(db.weets.posted_by== db.auth_user.id)) row = rows.first() print row.auth_user.name Does it work? Also try (should be the same): rows = db(db.weets.posted_by==user)(db.weets.posted_by==db.auth_user.id). select() row = rows.first() print row.auth_user.name On Wednesday, 19 February 2014 23:18:33 UTC-6, contact.ur...@gmail.comwrote: Hi, Maybe a stupid question but here goes. I am trying to do the following join. row = db(db.weets.posted_by==user).select(join=db.auth_user.on(db.weets.posted_by== db.auth_user.id)) its seems to work and row does return. The example in the book then shows you can use attributes but when i try: row.auth_user.name or other field it says no attribute auth_user. i am not sure why this is? -- 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/groups/opt_out.
