hmmm. it could be a bug. What do you get if you do:
print
db(db.Calls.id>0)._select(db.Calls.ALL,db.created_by.name,db.handled_by.name,
left=[created_by.on(created_by.id==db.Calls.CreatedBy),
handled_by.on(handled_by.id==db.Calls.HandledBy)])
On Mar 23, 11:31 am, jonatron <hobson....@googlemail.com> wrote:
> Thanks,
>
> This is close, but it does not quite get the result I'm looking for, I
> basically need the following MySQL query:
>
> SELECT Calls . * , created_by.Name, handled_by.Name
> FROM (
> Calls
> LEFT JOIN Users AS created_by ON Calls.CreatedBy = created_by.ID
> )
> LEFT JOIN Users AS handled_by ON Calls.HandledBy = handled_by.ID;
>
> I've tried adapting the code you posted but I can't seem to get the
> result set I need. For example a call from 'Helen' created by Bob and
> Handled by Sam, I would like to get the results:
>
> CallerName created_by.Name handled_by.Name
> Helen Bob Sam
>
> but I get
>
> Calls.id Calls.CallerName Users.Name Users.Name
> 1 Helen Bob Bob
> 1 Helen Sam Sam
> 1 Helen Charlie Charlie
>
> Any ideas?
>
> On Mar 20, 3:10 pm, mdipierro <mdipie...@cs.depaul.edu> wrote:
>
> > created_by=db.Users.with_alias('created_by')
> > handled_by=db.Users.with_alias('handled_by')
> > rows =
> > db(db.Calls.id>0).select(db.Calls.ALL,db.created_by.name,db.handled_by.name,
> > left=[created_by.on(created_by.id==db.Calls.CreatedBy),
> > handled_by.on(handled_by.id==db.Calls.HandledBy)])
>
> > On Mar 20, 6:58 am, jonatron <hobson....@googlemail.com> wrote:
>
> > > Hi,
>
> > > I am hoping someone can help with this:
>
> > > I have tables setup something like this (this is a simplified
> > > representation):
>
> > > db.define_table('Users',
> > > Field('Name'))
>
> > > db.define_table('Calls',
> > > Field('CallerName'),
> > > Field('CreatedBy', db.Users),
> > > Field('HandledBy', db.Users))
>
> > > I need to produce a query set to send to geraldo reports that joins
> > > both the createdby and handledby fields to db.users.id so that I can
> > > display the users name in the report not the id. I can do one inner
> > > join no problem and use Users.Name to get the name. To do both joins
> > > and get useful data I think I need to do something analogue SQL AS on
> > > the joins and then access the data using an alias. I have played with
> > > with_alias but don't seem to get what I need. Can anyone point me in
> > > the right direction?
--
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To post to this group, send email to web...@googlegroups.com.
To unsubscribe from this group, send email to
web2py+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en.
