[web2py] Re: Hierarchical Database Selection
Excellent! This works just as advertised for obtaining the companies and the solution works in my application. I very much appreciate the solution. I was wondering if this was analogous to the following syntax in Django: train.objects.filter(company__name='Amtrak') Please, no offense, because I absolutely love web2py, but the Django syntax in this case is much more understandable. Also, if there are more relationships to track such as: blog.objects.filter(entry__author__name='Dave') you are able to arbitrarily track any relationship with this syntax. If this isn't possible, or if I have misinterpreted the syntax and functionality, please let me know. Thank you very much for (a) creating such a great framework and (b) for being so committed to helping here on the forums! Dave On Friday, January 11, 2013 7:13:36 PM UTC-5, Massimo Di Pierro wrote: > I think you want (I am changing some field names because user and date are > resrved keywords): > > db.define_table('company', > Field('name'), > Field('symbol')) > > db.define_table('train', > Field('user_id', 'reference auth_user', default=auth.user_id), > Field('company', db.company), > Field('date_event', 'date'), > Field('num_cars')) > > > companies = > db(db.company.id.belongs(db(db.train.user_id==id)._select(db.train.company,distinct=True)).select(orderby= > db.company.name) > > > > > On Friday, 11 January 2013 17:05:02 UTC-6, Dave Cenker wrote: >> >> I am putting together a website for my son so that he can keep track of >> trains that he has seen in our area. Here are the applicable models: >> >> db.define_table('company', >> Field('name'), >> Field('symbol')) >> >> db.define_table('train', >> Field('user', 'reference auth_user', default=auth.user_id), >> Field('company', db.company), >> Field('date', 'date'), >> Field('numCars')) >> >> I would like to be able to select all the trains that a particular user >> has seen. I believe I can accomplish this with the following: >> >> currUserQuery = db.train.user == auth.user_id >> allTrainsSeenByUser = db(currUserQuery).select(orderby='date desc') >> >> What I would like to do is create an index page for each user that splits >> out the trains of each company that have been seen. For example, assume >> that there are three companies in the database: Amtrak, CSX, and BNSF. Of >> these three companies the user has seen trains from Amtrak and CSX. On >> their index page, they would see all the Amtrak trains that they have seen >> grouped together and all the CSX trains that they have seen grouped >> together. Therefore, I would like to perform a database selection that >> finds all the trains for a particular user and a particular company. >> >> I have found a way to determine which companies a particular user has >> seen using the following: >> >> companies = db(currUserQuery).select('train.company', >> distinct=True).as_list() >> >> However, this returns a list of company IDs instead of names and when I >> try to do something like this: >> >> db(train.company.id == id) it doesn't work. >> >> What I'd really like to do is get a list of the names of the companies >> that a user has seen and then make a database selection based upon those >> names in a manner like this: >> >> db(currUserQuery & (db.train.company.name == companyNameText)).select() >> >> However, the db.train.company.name doesn't work. Is there a way to >> progress down the hierarchy (if you will) to access the company name on a >> particular train row given the database structure above. With Django (which >> I have used in the past), this would be accomplished similar to this >> (train__company__name=companyNameText), but I can't figure out the >> corresponding way to do so in web2py. >> >> Thanks for any help you can provide! >> >> Dave >> >> --
[web2py] Hierarchical Database Selection
I am putting together a website for my son so that he can keep track of trains that he has seen in our area. Here are the applicable models: db.define_table('company', Field('name'), Field('symbol')) db.define_table('train', Field('user', 'reference auth_user', default=auth.user_id), Field('company', db.company), Field('date', 'date'), Field('numCars')) I would like to be able to select all the trains that a particular user has seen. I believe I can accomplish this with the following: currUserQuery = db.train.user == auth.user_id allTrainsSeenByUser = db(currUserQuery).select(orderby='date desc') What I would like to do is create an index page for each user that splits out the trains of each company that have been seen. For example, assume that there are three companies in the database: Amtrak, CSX, and BNSF. Of these three companies the user has seen trains from Amtrak and CSX. On their index page, they would see all the Amtrak trains that they have seen grouped together and all the CSX trains that they have seen grouped together. Therefore, I would like to perform a database selection that finds all the trains for a particular user and a particular company. I have found a way to determine which companies a particular user has seen using the following: companies = db(currUserQuery).select('train.company', distinct=True).as_list() However, this returns a list of company IDs instead of names and when I try to do something like this: db(train.company.id == id) it doesn't work. What I'd really like to do is get a list of the names of the companies that a user has seen and then make a database selection based upon those names in a manner like this: db(currUserQuery & (db.train.company.name == companyNameText)).select() However, the db.train.company.name doesn't work. Is there a way to progress down the hierarchy (if you will) to access the company name on a particular train row given the database structure above. With Django (which I have used in the past), this would be accomplished similar to this (train__company__name=companyNameText), but I can't figure out the corresponding way to do so in web2py. Thanks for any help you can provide! Dave --
[web2py] Re: Associate a db record with a logged in user
Thank you very much! I knew it had to be simple. I just didn't infer that information from the way the book explained it. Your help is much appreciated! On Monday, January 7, 2013 3:31:33 PM UTC-5, Dave Cenker wrote: > This seems so elementary, but I can't seem to find out how to do it in the > book or googling the group ... > > I have several users defined and when that user is logged in and is > creating a db record that is specific to that user, I want it to be > automatically assigned to him/her. > > For example, given a model that defines a checking account, > > db.define_table('account', > Field('type'), > Field('balance'), > Field('user', db.auth_user)) > > How can I use the crud.create forms to automatically assign the value of > the user field on the account model to the currently logged in user? > > Thanks, > Dave > > --
[web2py] Associate a db record with a logged in user
This seems so elementary, but I can't seem to find out how to do it in the book or googling the group ... I have several users defined and when that user is logged in and is creating a db record that is specific to that user, I want it to be automatically assigned to him/her. For example, given a model that defines a checking account, db.define_table('account', Field('type'), Field('balance'), Field('user', db.auth_user)) How can I use the crud.create forms to automatically assign the value of the user field on the account model to the currently logged in user? Thanks, Dave --
[web2py] Customizing select options in crud.create forms
I have a set of models defined as follows: db.define_table('company', Field('name'), Field('symbol'), Field('approved', 'boolean')) db.define_table('locomotive', Field('manufacturer'), Field('model'), Field('approved', 'boolean'), db.define_table('engine', Field('company', db.company), Field('locomotive', db.locomotive)) The intent of the 'approved' fields are to limit what is displayed to the user in form select lists until they have been approved by someone with an admin role. I am attempting to limit the options in my engine controller create.crud function to only make available the companies and locomotives that are approved. I can obviously get this information easily using db(db.company.approved == True).select() etc. However, I am trying how to figure out how to get this filtered list of options into the select list dropdown in the crud.create(db.engine) form. Any suggestions? I tried to create a custom form to do the same thing, but it began to get really 'clunky'. Thanks in advance for any help! Dave --
[web2py] Processing user entries prior to database commit
I first must say that I have developed 2 different web applications using Django in the past and I am very impressed and encouraged by what web2py can offer to the web framework community. However, I am having trouble carrying out a seemingly simple task without much ado after reviewing all the documentation and traditional "googling" methods. Suppose I have a database table with a field named 'fullName'. When the user submits a crud.create form with the data 'jOhN DoE', I would like for this entry to be saved into the database as 'John Doe' (i.e. - title case). In addition, I would like to be able to use the IS_IN_DB requirement validator to disallow any variant of this entry to be entered in the future. For example, if a user enters 'john doe', 'JOHN DOE', or 'JoHn DoE', they should all be recognized as an entry that is already in the database (i.e. - I would like a case insensitive check). I have tried to use the represent attribute on the field, however that doesn't seem to affect the actual data that is saved in the database. I have tried to use a custom onvalidation function, but it doesn't seem to work well either. The only way I have seen that really seems to work is having a separate database field (i.e. - two fields for the same database object). Has anyone run into this situation and found a more eloquent solution? Thanks in advance for any help you can provide! Dave --