Need help with a query

[Ruud Schroen]

Hi there,

In my app there is the possibility to become friends. This is my model:

db.define_table('friendship',

                Field('source_user', 'reference auth_user'),

                Field('target_user', 'reference auth_user'),

                Field('accepted', 'boolean', default=False)

                )

Now, if I want to fetch all friends of a given user, how would I do this?

The problem is that the given user ID can be in the source_user OR target_user, so I need an easy way to fetch the OTHER id.

[Leonel Câmara]

You sort of provide your own answer (OR). In web2py's DAL you OR queries using the binary OR operator.

For instance, this is the solution for the logged in user:

my_friends = db((db.friendship.accepted == True) & ((db.friendship.source_user == auth.user_id) | (db.friendship.target_user == auth.user_id))).select()

[Ruud Schroen]

Thanks for your answer.

I know about the OR operators. The "problem" is that it now fetches the entire friendships records.

I need a list with ID's other then the user's own ID, like for a "My friends" page. When I fetch a friendship record, the ID of the friend can either be the source_user or the target_user. 

Is it possible that if the source_user matches the auth.user_id, take the target_user instead? And vice versa.

I would go for a for loop, but maybe there is an easier and faster way.

[Leonel Câmara]

my_friends = db((db.friendship.accepted == True) & ((db.friendship.source_user == auth.user_id) | (db.friendship.target_user == auth.user_id))).select()

my_friends_ids = [friendship.source_user if friendship.source_user != auth.user_id else friendship.target_user for friendship in my_friends]

I would cache this denormalized result, probably in the user table.

[Ruud Schroen]

That's exactly what I was looking for. Thanks!

Op 5-jun.-2014 15:42 schreef "Leonel Câmara" <leonel...@gmail.com>:

my_friends = db((db.friendship.accepted == True) & ((db.friendship.source_user == auth.user_id) | (db.friendship.target_user == auth.user_id))).select()

my_friends_ids = [friendship.source_user if friendship.source_user != auth.user_id else friendship.target_user for friendship in my_friends]

I would cache this denormalized result, probably in the user table.

--
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 a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/mtW2NvGlKYY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.