[web2py] Re: Self-referencing tables and queries

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

Re: [web2py] Re: Self-referencing tables and queries

[António Ramos]

+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

[Leonel Câmara]

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

[web2py] Re: Self-referencing tables and queries

[Leonel Câmara]

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

[Carla Raquel]

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

[Carla Raquel]

*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

[Leonel Câmara]

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

[Dave S]

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

[Leonel Câmara]

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