CODEWITHSUNDEEP
djangodjango-models
Joshua
Joshua

Reputation: 197

Django is complex select query possible for this?

I have the following model used to store a bidirectional relationship between two users. The records are always inserted where the smaller user id is user_a while the larger user id is user_b.

Is there a way to retrieve all records belonging to a reference user and the correct value of the status (apply negative transformation to relationship_type if user_a) based on whether the reference user id is larger or smaller than the other user id?

Perhaps two separate queries, one where reference user = user_a and another where reference user = user_b, followed by a join?

class Relationship(models.Model):
    RELATIONSHIP_CHOICES = (
        (0, 'Blocked'),
        (1, 'Allowed'),
        (-2, 'Pending_A'),
        (2, 'Pending_B'),
        (-3, 'Blocked_A'),
        (3, 'Blocked_B'),
    )
    user_a = models.ForeignKey(CustomUser, on_delete=models.SET_NULL, related_name='user_a',null=True)
    user_b = models.ForeignKey(CustomUser, on_delete=models.SET_NULL, related_name='user_b',null=True)
    relationship_type = models.SmallIntegerField(choices=RELATIONSHIP_CHOICES, default=0)

A SQL query of what I'm trying to achieve:

(SELECT user_b as user_select, -relationship_type as type_select WHERE user_a='reference_user') UNION (SELECT user_a as user_select, relationship_type as type_select WHERE user_b='reference_user')

Upvotes: 1

Views: 59

Answers (2)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476699

Given you have the id of the user user_id, you can filter with:

from django.db.models import Q

Relationship.objects.filter(Q(user_a_id=user_id) | Q(user_b_id=user_id))

If you have a CustomUser object user, it is almost the same:

from django.db.models import Q

Relationship.objects.filter(Q(user_a=user) | Q(user_b=user))

If you are looking to obtain Relationships with a given type, we can do the following:

from django.db.models import Q

rel_type = 2  # example rel_type

Relationship.objects.filter(
    Q(user_a=user, relationship_type=rel_type) |
    Q(user_b=user, relationship_type=-rel_type)
)

Here we thus retrieve Relationship objects with user_a the given user and relationship_type=2, or Relationship objects with user_b the given user, and relationship_type=-2.

We could annotate the querysets, and then take the union, like:

qs1 = Relationship.objects.filter(
    user_a=user, relationship_type=rel_type
).annotate(
    user_select=F('user_b'),
    rel_type=F('relationship_type')
)

qs2 = Relationship.objects.filter(
    user_a=user, relationship_type=rel_type
).annotate(
    user_select=F('user_a'),
    rel_type=-F('relationship_type')
)

qs = qs1.union(qs2)

Although I do not know if that is a good idea: the annotations are not "writable" (so you can not update these).

It might be better to implement some sort of "proxy object" that can swap user_a and user_b, and negate the relationship type, and thus is able to act as if it is a real Relationship object.

Upvotes: 2

ruddra
ruddra

Reputation: 51988

As you said, id in user_a is always smaller than user_b. So if you query with user_b=user then you should always get the references where user_id in the reference is always higher than other user_id. So I think you can use following querysets:

user = CustomUser.objects.get(id=1)
user_a_references = Relationship.objects.filter(user_a=user)
user_b_references = Relationship.objects.filter(user_b=user)

all_relation_ships = user_a_reference.union(user_b_references)

Upvotes: 1

Related Questions