[web2py] Re: Self-referencing tables and queries
> Actually, the extensions are internal to the company, either phone numbers or mobile numbers, so your scenario won't ever apply. Fine, I disagree with this assumption but I'm not your client. > Conclusion: Just the Supervisor extensions Well this is very easy, the Supervisor is just a Person like everyone else. So all you need is # Assuming you're getting the id of the supervisor you want the extensions for in request.args(0) supervisor = db.auth_user[request.args(0)] supervisor_extensions = db(db.Extension.Person == supervisor.id).select() -- 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.
Re: [web2py] Re: Self-referencing tables and queries
+1 *There's no need for an extension to have a supervisor, because the supervisor is already stored in Person. !!!* 2015-09-17 11:10 GMT+01:00 Carla Raquel : > *So what? You should future proof the app. Some day there might be > extensions in India or something.* > > Actually, the extensions are internal to the company, either phone numbers > or mobile numbers, so your scenario won't ever apply. > > *Do you mean the extensions of the persons he supervises?* > > That is what he already does, which is what I don't want it to do. I'll > give an example,so that you can visualize it better: > > Supervisor X has two extensions: 5589,5590 > Supervisor X supervises two people: A and B > A has two extensions: 4572,4573 > B has one extension: 4321 > > My current query returns: 5589,5590,4572,4573,4321 > What I want it to returns: 5589,5590 > > Conclusion: Just the Supervisor extensions > > > quarta-feira, 16 de Setembro de 2015 às 18:15:53 UTC+1, Leonel Câmara > escreveu: >> >> > I think in this case they won't be strings since I read the numbers >> from a XML and they don't have the '+' character >> >> So what? You should future proof the app. Some day there might be >> extensions in India or something. >> >> > Not all the supervisors.I want to get the extensions of a particular >> supervisor >> >> Do you mean the extensions of the persons he supervises? If so: >> >> the_supervised = db(db.auth_user.Supervisor == >> ParticularSupervisor.id)._select(db.auth_user.id) >> extensions_of_the_supervised = >> db(db.Extension.Person.belongs(the_supervised)).select() >> >> This will give you the extensions of everyone that is supervised by this >> person. >> >> >> -- > 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. > -- 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: Self-referencing tables and queries
*So what? You should future proof the app. Some day there might be extensions in India or something.* Actually, the extensions are internal to the company, either phone numbers or mobile numbers, so your scenario won't ever apply. *Do you mean the extensions of the persons he supervises?* That is what he already does, which is what I don't want it to do. I'll give an example,so that you can visualize it better: Supervisor X has two extensions: 5589,5590 Supervisor X supervises two people: A and B A has two extensions: 4572,4573 B has one extension: 4321 My current query returns: 5589,5590,4572,4573,4321 What I want it to returns: 5589,5590 Conclusion: Just the Supervisor extensions quarta-feira, 16 de Setembro de 2015 às 18:15:53 UTC+1, Leonel Câmara escreveu: > > > I think in this case they won't be strings since I read the numbers from > a XML and they don't have the '+' character > > So what? You should future proof the app. Some day there might be > extensions in India or something. > > > Not all the supervisors.I want to get the extensions of a particular > supervisor > > Do you mean the extensions of the persons he supervises? If so: > > the_supervised = db(db.auth_user.Supervisor == > ParticularSupervisor.id)._select(db.auth_user.id) > extensions_of_the_supervised = > db(db.Extension.Person.belongs(the_supervised)).select() > > This will give you the extensions of everyone that is supervised by this > person. > > > -- 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: Self-referencing tables and queries
On Wednesday, September 16, 2015 at 10:15:53 AM UTC-7, Leonel Câmara wrote:
>
> > I think in this case they won't be strings since I read the numbers from
> a XML and they don't have the '+' character
>
> So what? You should future proof the app. Some day there might be
> extensions in India or something.
>
> > Not all the supervisors.I want to get the extensions of a particular
> supervisor
>
> Do you mean the extensions of the persons he supervises?
>
Didn't Carla say that was what she was already getting? I understand her
to be wanting to get either 1 or 2 extensions: 1 if the person logging in
*is* a supervisor, and 2 if the person logging in* has* a supervisor (PLI's
extension, + PLI's super's extension).
> If so:
>
> the_supervised = db(db.auth_user.Supervisor ==
> ParticularSupervisor.id)._select(db.auth_user.id)
> extensions_of_the_supervised =
> db(db.Extension.Person.belongs(the_supervised)).select()
>
> This will give you the extensions of everyone that is supervised by this
> person.
>
Rough idea of what I think Carla wants:
auth.users = {("Joe", "5678", "no", "Julie"), ("Julie", "5432", "yes", "")}
stuff = db(db.auth.users.name == "Joe").select()
results = stuff.name + "has logged in at " + now() + " working at ext " +
stuff.ext
if stuff.issuper == "no":
morestuff = db(db.auth.users.name == stuff.supername).select()
results = results + BR() + "Supervisor: " + morestuff.name + " at ext " +
morestuff.ext
return results
and I think Carla is trying to do a join to get everything in 1 query.
/dps
--
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: Self-referencing tables and queries
> I think in this case they won't be strings since I read the numbers from a XML and they don't have the '+' character So what? You should future proof the app. Some day there might be extensions in India or something. > Not all the supervisors.I want to get the extensions of a particular supervisor Do you mean the extensions of the persons he supervises? If so: the_supervised = db(db.auth_user.Supervisor == ParticularSupervisor.id)._select(db.auth_user.id) extensions_of_the_supervised = db(db.Extension.Person.belongs(the_supervised)).select() This will give you the extensions of everyone that is supervised by this person. -- 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: Self-referencing tables and queries
*They should be strings (otherwise how do you store the '+' character)*.
I think in this case they won't be strings since I read the numbers from a
XML and they don't have the '+' character.
*Then you want to get the extensions of all the supervisors? *
Not all the supervisors.I want to get the extensions of a particular
supervisor (hence I had the first and last name in the query). Something
like this: when a user logs in the system checks who has logged in and
feeds a table with info(calls details,extensions,etc.) about the specific
user, who can be a supervisor or a regular person.
quarta-feira, 16 de Setembro de 2015 às 17:09:48 UTC+1, Leonel Câmara
escreveu:
>
> Ohh ok I had no idea extensions were phone numbers, phone numbers should
> not be integers, they should be strings (otherwise how do you store the '+'
> character). Then extensions should be just this:
>
> db.define_table('Extension',
> Field('Person' ,db.auth_user, requires = IS_IN_DB(db,'
> auth_user.id')),
> Field('Extension_number','string',required = True))
>
> There's no need for an extension to have a supervisor, because the
> supervisor is already stored in Person.
>
> Then you want to get the extensions of all the supervisors? (did I get
> that right)
>
> The easiest way would then be to use belongs:
>
> supervisors = db(db.auth_user.id >
> 0)._select(db.auth_user.Supervisor, distinct=True)
> supervisor_extensions =
> db(db.Extension.Person.belongs(supervisors)).select()
>
> This way you will have the extensions of all the supervisors.
>
--
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: Self-referencing tables and queries
Ohh ok I had no idea extensions were phone numbers, phone numbers should
not be integers, they should be strings (otherwise how do you store the '+'
character). Then extensions should be just this:
db.define_table('Extension',
Field('Person' ,db.auth_user, requires = IS_IN_DB(db,'
auth_user.id')),
Field('Extension_number','string',required = True))
There's no need for an extension to have a supervisor, because the
supervisor is already stored in Person.
Then you want to get the extensions of all the supervisors? (did I get that
right)
The easiest way would then be to use belongs:
supervisors = db(db.answer.id > 0)._select(db.auth_user.Supervisor,
distinct=True)
supervisor_extensions =
db(db.Extension.Person.belongs(supervisors)).select()
This way you will have the extensions of all the supervisors.
--
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: Self-referencing tables and queries
I guess my code is more messed up than I thought. I'll try and clarify
your points:
*What am I trying to achieve?*
In the db there are people (given by the auth_user table). From those
people there are ones who are hierarchically above them and supervise
them.They are called Supervisors. All people have mobile and phone
extensions(numbers) . As far as my query goes, I'm getting the Supervisor'
numbers and the people's numbers associated with the Supervisor. What I'm
trying to accomplish: returning only the Supervisor' numbers.
*Would it make more sense to have it like this?*
I'm curious,why would a second table(extension_member) be needed?I think I
will also need the extension_number in the 'extension' table since that
table stores all the phone and mobile extensions.
*Why would you check the first name and last name when you already compared
the ID with person?*
Now I am the one a bit confused. The comparison between the ID and the
person was to connect both tables and then I would filter by the user I
wanted. But now I'm not sure If I've been doing this correctly...?
*Why would you use "like" for an integer field?*
It's the only way I know to check if a number begins as 9 or ~9 . Is there
a simpler/optimised way?Also, as an aside, is there a way to use regular
expressions in queries?
Thank you for all the help!
quarta-feira, 16 de Setembro de 2015 às 15:18:31 UTC+1, Leonel Câmara
escreveu:
>
> This is the answer to the first problem:
>
> db.auth_user.Supervisor.requires=IS_EMPTY_OR(IS_IN_DB(db,'auth_user.id',
> db.auth_user._format))
>
> You can just remove it as it will use the format for auth_user anyway as
> that's the default. So it becomes only:
>
> db.auth_user.Supervisor.requires=IS_EMPTY_OR(IS_IN_DB(db,'auth_user.id')
>
> As for the Extensions I'm very confused with what you're trying to achieve
> and your table structure doesn't seem very good. Would it make more sense
> to have it like this?
>
> db.define_table('extension',,
> Field('Supervisor' , 'reference auth_user', requires =
> IS_EMPTY_OR(IS_IN_DB(db,'auth_user.id','%(first_name)s %(last_name)s'))),
> Field('Extension_number','integer',required = True))
>
> db.define_table('extension_member',
> Field('extension' , 'reference extension'),
> Field('member' , 'reference auth_user'),
>
>
> I ask this because your query is utterly confusing, why would you check
> the first name and last name when you already compared the ID with person,
> why would you use "like" for an integer field. So many questions.
>
> In your questions you need to tells us *what* you're trying to achieve
> not just *how*.
>
--
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: Self-referencing tables and queries
This is the answer to the first problem:
db.auth_user.Supervisor.requires=IS_EMPTY_OR(IS_IN_DB(db,'auth_user.id',
db.auth_user._format))
You can just remove it as it will use the format for auth_user anyway as
that's the default. So it becomes only:
db.auth_user.Supervisor.requires=IS_EMPTY_OR(IS_IN_DB(db,'auth_user.id')
As for the Extensions I'm very confused with what you're trying to achieve
and your table structure doesn't seem very good. Would it make more sense
to have it like this?
db.define_table('extension',,
Field('Supervisor' , 'reference auth_user', requires =
IS_EMPTY_OR(IS_IN_DB(db,'auth_user.id','%(first_name)s %(last_name)s'))),
Field('Extension_number','integer',required = True))
db.define_table('extension_member',
Field('extension' , 'reference extension'),
Field('member' , 'reference auth_user'),
I ask this because your query is utterly confusing, why would you check the
first name and last name when you already compared the ID with person, why
would you use "like" for an integer field. So many questions.
In your questions you need to tells us *what* you're trying to achieve not
just *how*.
--
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.
