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
>
>
--
