[web2py] Re: joins, geraldo and possible alias
I tried it with sqlexecute but I would need to do quite a lot of
processing on the results...
Is there any work around you can think of? Is there any way to use
the DAL with a select statement in the format generated from _select?
Thanks,
J
On Mar 23, 9:35 pm, jonatron hobson@googlemail.com wrote:
I get the following:
SELECT Calls.id, Calls.CallerName, Calls.CreatedBy, Calls.HandledBy,
Users.Name, Users.Name FROM Users, Calls LEFT JOIN Users AS created_by
ON created_by.id=Calls.CreatedBy LEFT JOIN Users AS handled_by ON
handled_by.id=Calls.HandledBy WHERE Calls.id0;
On Mar 23, 6:06 pm, mdipierro mdipie...@cs.depaul.edu wrote:
hmmm. it could be a bug. What do you get if you do:
print
db(db.Calls.id0)._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,jonatronhobson@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.id0).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,jonatronhobson@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.
[web2py] Re: joins, geraldo and possible alias
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.idCalls.CallerNameUsers.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.id0).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.
[web2py] Re: joins, geraldo and possible alias
I get the following:
SELECT Calls.id, Calls.CallerName, Calls.CreatedBy, Calls.HandledBy,
Users.Name, Users.Name FROM Users, Calls LEFT JOIN Users AS created_by
ON created_by.id=Calls.CreatedBy LEFT JOIN Users AS handled_by ON
handled_by.id=Calls.HandledBy WHERE Calls.id0;
On Mar 23, 6:06 pm, mdipierro mdipie...@cs.depaul.edu wrote:
hmmm. it could be a bug. What do you get if you do:
print
db(db.Calls.id0)._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.id0).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.
