My first complicated sql statement....

[Jason Brower]

I have a big fat book on SQL syntax. But I am hoping on using Web2Py's
python syntax.
I want to make it so my users that have tags that are the same can be
listed with the tag is the same between them. The user I am looking up
is the logged in user, I am using the auth feature.
Soo I have tag "One" and Billy has tag "One" so I want to be able to
see that user "billy" has the tag "One" that I do.
Hope that explains it.
I have the following relevent tables...
------
db.define_table('users',
SQLField('first_name', 'string', length=15),
SQLField('last_name', 'string', length=15),
SQLField('phone_number', 'string', length=15),
SQLField('email', 'string'),
SQLField('password', 'password'),
SQLField('university_affiliation', 'string', length=25),
SQLField('created', 'datetime', default=now, readable=False,
writable=False),
SQLField('registration_key', length=128, writable=False,
readable=False, default=''),
SQLField('avatar', 'upload'),
SQLField('short_description','text'))

db.define_table('tag',
SQLField('name', 'string'),
SQLField('description', 'text'),
SQLField('logo', 'upload'),
SQLField('created', 'date', default=now, writable=False),
SQLField('creator', 'string', writable=False))

db.define_table('user_tags',
SQLField('tag_id',db.tag),
SQLField('user_id',db.users))

db.user_tags.tag_id.requires = IS_IN_DB(db,'tag.id')
db.user_tags.user_id.requires = IS_IN_DB(db,'users.id')

db.users.first_name.requires = IS_NOT_EMPTY()
db.users.last_name.requires = IS_NOT_EMPTY()
db.users.password.requires = CRYPT()
db.users.email.requires = [IS_EMAIL(), IS_NOT_IN_DB(db,'users.email')]
db.users.created.requires = IS_NOT_EMPTY()
----

[mdipierro]

This should do it.

current_user_id=...
db((db.users.id==db.user_tags.user_id)&(db.user_tags.tag_id==db.tag.id)
&(db.user_tags.tag_id.belongs(db
(db.user_tags.user_id==current_user_id)._select
(db.user_tags.tag_id)))).select
(db.users.ALL,db.tag.ALL,groupby=db.users.id)

Massimo

[Jason (spot) Brower]

I don't think it did.
I am looking for something like this...
Name1:
- Tag1
- Tag2
- Tag3
- Tag4
- Tag5
Name2:
- Tag3
- Tag4
- Tag5
- Tag6
- Tag7
Name3:
- Tag5
- Tag7
- Tag8
- Tag9
- Tag0
Or vice versa...
I kind of have it with this method...
-----
@auth.requires_login()
def status():
response.title = "Your Profile Page"
current_user_id=auth.user.id
tags = db((db.users.id==db.user_tags.user_id)
&(db.user_tags.tag_id==db.tag.id)
&(db.user_tags.tag_id.belongs(
db(db.user_tags.user_id==current_user_id)._select(
db.user_tags.tag_id)))).select(
db.users.ALL,db.tag.ALL,groupby=db.users.id)
allTags = db().select(db.tag.ALL,orderby=db.tag.name)
relatedTags = {}
tagID = {}
for tag in tags:
if not tag.users.id == auth.user.id:
if relatedTags.has_key(tag.tag.id):

relatedTags[tag.tag.id].append(db(db.users.id==tag.users.id).select()[0])
else:
relatedTags[tag.tag.id] =
[db(db.users.id==tag.users.id).select()[0]]
tagID[tag.tag.id] = db(db.tag.id==tag.tag.id).select()[0]
return dict(tags= tags, user = auth.user, allTags = allTags,
relatedTags = relatedTags, tagID = tagID)
-----
This creates tags with User names listed inside, but I think the idea
is very similar.

[Jason (spot) Brower]

Oh yes, and my view... sorry,
{{if len(tags) > 0:}}
<div id="cloud">
<ul clas<s="nav">
{{count = 1}}
{{for tag in relatedTags.keys():}}
{{size=random.randint(1, 4)}}
<li>
<a class="size{{=size}}"
href="view_tag?id={{=tag}}" id="j{{=count}}">{{=tagID[tag].name}}</a>
<ul style="display: block;" class="hovertip"
target="j{{=count}}">
{{count2 = 1}}
{{for user in relatedTags[tag]:}}
<li><a class="size-sub{{=count2-1}}"
href="view_user?id={{=user.id}}">{{=user.first_name}}</a></li>
{{if count < 2:count2 += 1}}
{{pass}}
{{else:}}
{{count = 0}}
{{pass}}
{{pass}}
</ul>
</li>
{{count +=1}}
{{pass}}
</ul>
</div>
{{pass}}