[web2py] Convert Mysql query with subquery to DAL.
Hi guys, Trying to convert this select t.id, t.gp_pro_id, t.gp_historicdate from course t where t.gp_historicdate = (select MAX(a.gp_historicdate) from course a where a.id = t.id) Any help is appreciated. Thanks! -- 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: Help with SQL Query without using executesql preferably :)
Thanks Cliff. But i don´t think it is that simple, or perhaps it is, but i can't manage to build the right query. If i use group by, it groups by the id and the having=max is ignored. all ids appear. Probably it needs joins. Will try to do the raw query and see if anyone can translate it. Thanks El lunes, 10 de marzo de 2014 18:21:51 UTC-3, Cliff Kachinske escribió: The DAL chapter of the online Web2py manual explains how to fetch the max value, same as a SQL GROUP BY. On Monday, March 10, 2014 3:54:59 PM UTC-4, brahama von wrote: This is the result i get from a simple select like this: legacy_db(legacy_db.cursodado.gp_pro_id==course).select() cursodado.gp_id cursodado.gp_pro_id cursodado.gp_historicdate cursodado.gp_curso_id 10003600042014-01-27 16:02:10None 10003600042014-01-27 17:18:31None 10003600042014-01-27 17:21:02None 10005250042014-01-27 16:02:10None 10005250042014-01-27 17:18:31None 10005250042014-01-27 17:21:02None Here i get the id of the person and the id of the course but I want only to show the first I once, that correspond to the max(cursodado.gp_historicdate) To see it like this: cursodado.gp_id cursodado.gp_pro_id cursodado.gp_historicdate cursodado.gp_curso_id 10003600042014-01-27 17:21:02None 10005250042014-01-27 17:21:02None tried this in the select but it gives a timeout: select(groupby=gp_id,having=max(historicdate field)) Thanks! -- 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] Help with SQL Query without using executesql preferably :)
This is the result i get from a simple select like this: legacy_db(legacy_db.cursodado.gp_pro_id==course).select() cursodado.gp_id cursodado.gp_pro_id cursodado.gp_historicdate cursodado.gp_curso_id 10003600042014-01-27 16:02:10None 10003600042014-01-27 17:18:31None 10003600042014-01-27 17:21:02None 10005250042014-01-27 16:02:10None 10005250042014-01-27 17:18:31None 10005250042014-01-27 17:21:02None Here i get the id of the person and the id of the course but I want only to show the first I once, that correspond to the max(cursodado.gp_historicdate) To see it like this: cursodado.gp_id cursodado.gp_pro_id cursodado.gp_historicdate cursodado.gp_curso_id 10003600042014-01-27 17:21:02None 10005250042014-01-27 17:21:02None tried this in the select but it gives a timeout: select(groupby=gp_id,having=max(historicdate field)) Thanks! -- 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] Foreign Key Constraint Failed in dbadmin
Hi guys!
Been looking for this in the mail list but i can't understand why this is
happening.
Here is the model. Very simple, just learning and practicing. Its a version
with a few changes of the image blog :)
db.define_table('uploads',
Field('up_name','string',requires=IS_NOT_EMPTY()),
Field('mainfile','upload'),
Field('thumb','upload',writable=False,readable=False),
Field('up_author',db.auth_user,requires=IS_NOT_EMPTY()),
Field('up_post','reference post'),
format='%(up_name)s')
db.uploads.up_author.writable = db.uploads.up_author.readable = False
db.define_table('post',
Field('category',requires=IS_IN_SET([T('Cat1'),T('Cat2'),T('Cat3'),T('Other')])),
Field('country',requires=IS_IN_SET([T('Argentina'),T('Spain'),T('US'),T('Other')])),
Field('place',requires=IS_NOT_EMPTY()),
Field('summary','string',requires=IS_NOT_EMPTY()),
Field('author',db.auth_user,requires=IS_NOT_EMPTY()),
Field('body','text'),
Field('link1', requires=IS_EMPTY_OR(IS_URL())),
Field('link2',requires=IS_EMPTY_OR(IS_URL())),
Field('link3',requires=IS_EMPTY_OR(IS_URL())),
Field('rel_post','reference post'),
format='%(summary)s')
db.post.rel_post.requires = IS_EMPTY_OR(IS_IN_DB(db,db.post,'%(summary)s'))
db.post.author.writable = db.post.author.readable = False
#db.uploads.up_post.requires = IS_IN_DB(db,db.post,'%(summary)s')
db.uploads.up_post.requires = IS_IN_DB(db(db.post.author == auth.user_id),
db.post,'%(summary)s')
db.define_table('comentarios',
Field('com_post_id','reference post'),
Field('com_comentario','text'),
Field('com_fecha','datetime')
)
I added the table commentaries recently. The image and post tables were
populated ok from a FORM view.
Now i tried to add a comment to an existing post through the dbadmin new
record. The form brought the post ids OK. But when i submit the form i get
the mentioned exception.
I read about disabling FK validation but i dont want to do that.
Thanks for the help as always!
--
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: Show referenced records in form that only belong to author
Woww.. like a charm! hehe... Thanks Anthony!
I am finding it a bit difficult to catch and understand some things. New to
programming in objects and frameworks.
Thanks again for the hand
El miércoles, 26 de febrero de 2014 19:46:36 UTC-3, Anthony escribió:
db.uploads.up_post.requires = IS_IN_DB(db(db.post.author == auth.user_id),
db.post,'%(summary)s')
The IS_IN_DB validator takes a Set object as the first argument, which can
be used to filter the allowable records.
Anthony
On Wednesday, February 26, 2014 5:23:41 PM UTC-5, brahama von wrote:
Hi guys!
How is the approach to do this?
Here's the model
-
db.define_table('uploads',
Field('up_name','string',requires=IS_NOT_EMPTY()),
Field('mainfile','upload'),
Field('thumb','upload',writable=False,readable=False),
Field('up_author',db.auth_user,requires=IS_NOT_EMPTY()),
Field('up_post','reference post'),
format='%(up_name)s')
db.uploads.up_author.writable = db.uploads.up_author.readable = False
db.define_table('post',
Field('summary','string',requires=IS_NOT_EMPTY()),
Field('author',db.auth_user,requires=IS_NOT_EMPTY()),
Field('body','text'),
format='%(summary)s')
db.post.author.writable = db.post.author.readable = False
db.uploads.up_post.requires = IS_IN_DB(db,db.post,'%(summary)s')
So when i want the user to upload an image so it can be related to the
post i only want to show the posts in the form that the user owns.
controller
--
@auth.requires_login()
def newimage():
dbtable = db.uploads #uploads table name
if auth.is_logged_in:
dbtable.up_author.default = auth.user.id
form = SQLFORM(dbtable)
return dict(form=form)
It has a few more lines but are for testing and practice as i am really
new to web2py :)
That is all. Thanks for any advice.
Cheers!
--
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] Show referenced records in form that only belong to author
Hi guys!
How is the approach to do this?
Here's the model
-
db.define_table('uploads',
Field('up_name','string',requires=IS_NOT_EMPTY()),
Field('mainfile','upload'),
Field('thumb','upload',writable=False,readable=False),
Field('up_author',db.auth_user,requires=IS_NOT_EMPTY()),
Field('up_post','reference post'),
format='%(up_name)s')
db.uploads.up_author.writable = db.uploads.up_author.readable = False
db.define_table('post',
Field('summary','string',requires=IS_NOT_EMPTY()),
Field('author',db.auth_user,requires=IS_NOT_EMPTY()),
Field('body','text'),
format='%(summary)s')
db.post.author.writable = db.post.author.readable = False
db.uploads.up_post.requires = IS_IN_DB(db,db.post,'%(summary)s')
So when i want the user to upload an image so it can be related to the post
i only want to show the posts in the form that the user owns.
controller
--
@auth.requires_login()
def newimage():
dbtable = db.uploads #uploads table name
if auth.is_logged_in:
dbtable.up_author.default = auth.user.id
form = SQLFORM(dbtable)
return dict(form=form)
It has a few more lines but are for testing and practice as i am really new
to web2py :)
That is all. Thanks for any advice.
Cheers!
--
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: Allow NULL in form validator with reference Field
Great! It worked perfect! The only thing i am not able to do is to show the
name field, instead of the id.. :S
Thanks a lot mate!
It is like this now.
Field('awc_pro_id',legacy_db.project,
requires=IS_EMPTY_OR(IS_IN_DB(legacy_db, legacy_db.project,
'%(pro_name)s'))),
El sábado, 22 de febrero de 2014 21:31:45 UTC-3, brahama von escribió:
Thanks Niphlod! Will try that. Yes. Mysql with INNODB. I am loading the
courses manually but wanted to do it via the web2py form...
El martes, 18 de febrero de 2014 17:24:25 UTC-3, Niphlod escribió:
does your backend support a foreign key with a null value ? not all of
them can.
As an alternative, you can
Field('awc_pro_id', 'integer', requires=IS_EMPTY_OR(IS_IN_DB()))
On Monday, February 17, 2014 7:21:14 PM UTC+1, brahama von wrote:
Hi,
dunno why my previous post didn't make it. Here it goes.
part of my model
legacy_db.define_table('courses',
Field('awc_name','string'),
Field('awc_version','string'),
Field('awc_link','string'),
Field('awc_pro_id',legacy_db.project, required=False, default=None),
Field('awc_cli_id',legacy_db.client),
format='%(awc_name)s',
migrate=False)
While creating a form for insert new courses i get the validator error;
value not in database
While this is true, i allow that value to be NULL in DB. How can i allow
that in the form and insert the record?
controller
form=SQLFORM(legacy_db.courses)
if form.process().accepted:
response.flash = 'Course was added Correctly'
return dict(form=form,message=T('Edition for Courses'))
Thanks!
--
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: Allow NULL in form validator with reference Field
Thanks Niphlod! Will try that. Yes. Mysql with INNODB. I am loading the
courses manually but wanted to do it via the web2py form...
El martes, 18 de febrero de 2014 17:24:25 UTC-3, Niphlod escribió:
does your backend support a foreign key with a null value ? not all of
them can.
As an alternative, you can
Field('awc_pro_id', 'integer', requires=IS_EMPTY_OR(IS_IN_DB()))
On Monday, February 17, 2014 7:21:14 PM UTC+1, brahama von wrote:
Hi,
dunno why my previous post didn't make it. Here it goes.
part of my model
legacy_db.define_table('courses',
Field('awc_name','string'),
Field('awc_version','string'),
Field('awc_link','string'),
Field('awc_pro_id',legacy_db.project, required=False, default=None),
Field('awc_cli_id',legacy_db.client),
format='%(awc_name)s',
migrate=False)
While creating a form for insert new courses i get the validator error;
value not in database
While this is true, i allow that value to be NULL in DB. How can i allow
that in the form and insert the record?
controller
form=SQLFORM(legacy_db.courses)
if form.process().accepted:
response.flash = 'Course was added Correctly'
return dict(form=form,message=T('Edition for Courses'))
Thanks!
--
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] Allow NULL in form validator with reference Field
Hi,
dunno why my previous post didn't make it. Here it goes.
part of my model
legacy_db.define_table('courses',
Field('awc_name','string'),
Field('awc_version','string'),
Field('awc_link','string'),
Field('awc_pro_id',legacy_db.project, required=False, default=None),
Field('awc_cli_id',legacy_db.client),
format='%(awc_name)s',
migrate=False)
While creating a form for insert new courses i get the validator error;
value not in database
While this is true, i allow that value to be NULL in DB. How can i allow
that in the form and insert the record?
controller
form=SQLFORM(legacy_db.courses)
if form.process().accepted:
response.flash = 'Course was added Correctly'
return dict(form=form,message=T('Edition for Courses'))
Thanks!
--
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] Form with related model can't validate NULL value
Hi,
First time i write to the list. Been googling around and couldn't find a
solution that i understand. I am really new to python and web2py.
I have this model
legacy_db.define_table('courses',
Field('name','string'),
Field('version','string'),
Field('link','string'),
Field('pro_id',legacy_db.project),
Field('cli_id',legacy_db.client),
format='%(name)s',
migrate=False)
NOTE: The pro_id field in the DATABASE (mysql) references the project table
via FK. It allows NULL values as the only mandatory field should be client.
So i added the courses through phpmyadmin correctly with the pro_id set to
NULL to various courses.
I created a controller to insert/edit that table so if someone wants to add
a course can do it without having access to admin or to DB.
def edit_courses():
form=SQLFORM(legacy_db.courses)
if form.process().accepted:
response.flash = 'Course was added Correctly'
return dict(form=form,message=T('Edition for Courses'))
View
{{extend 'layout.html'}}
h1{{=message}}/h1
{{=form}}
So the issue comes when i insert a course with no pro_id field. Validators
assume that one of the referenced values whould be used. And don't know how
to put NULL which should be the case here.
Thanks for the help.
--
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.
