How to use values and distinct on different field on Django ORM?

Leonardo Furtado 0
Viewed 41

currently I have terms on my applications and one user can have a lot of terms registered, and my current model is like this

class Term(models.Model):
    id = models.UUIDField("id", default=uuid.uuid4, editable=False, primary_key=True)
    user_id = models.PositiveIntegerField("user id", default=None)
    name = models.CharField()

sometimes I need to do a query to get all the users who have terms registered, so I do the following query:

Term.objects.filter(active=True)
        .order_by("user_id")
        .values("user_id")
        .distinct()

and this is enough to solve my problems, but now I'll change my model and it will look like this:

class Term(models.Model):
    id = models.UUIDField("id", default=uuid.uuid4, editable=False, primary_key=True)
    user_id = models.PositiveIntegerField("user id", default=None)
    name = models.CharField()
    shared_with = ArrayField(models.PositiveIntegerField("id do usuario"), blank=True) # New

How you can see, I've added a new field named shared_with, that basically is a array of user ids which I want to share terms, So now I need to make a query who will return all ids who can have terms registered (shared_with included). So if i register a Term with user_id = 1 and shared_with = [2,3], my query need to return [1,2,3].

I've solved this problem today with the following code, but I think I can do this just using django ORM and one query:

    users = set()
    for user in (
        Term.objects.filter(active=True)
        .order_by("user_id")
        .values("user_id")
        .distinct()
    ):
        users.add(user["user_id"])
    for user in (
        Term.objects.filter(active=True)
        .order_by("user_id")
        .values("shared_with")
    ):
        for user_id in user["shared_with"]:
            users.add(user_id)

    print(users) # {1,2,3}

If someone knows how to do it and can share the knowledge, I will be grateful.

django-orm django-models django python
1 Answers

I don't recommend using the PositiveIntegerField and ArrayField as relations between tables, you can use ForeignKey and ManyToManyField instead, in your case what I understand is a user can have many Terms and a Term can be shared among many users, so the perfect solution is to add ManyToManyField in your User model

class User(AbstarctUser):
    ... (your fields)
    terms = models.ManyToManyField(Term, related_name="users")

and Term model will be like:

class Term(models.Model):
    id = models.UUIDField("id", default=uuid.uuid4, editable=False, primary_key=True)
    name = models.CharField(max_length=200)
    active = models.BooleanField(default=True)
    ... (other fields)

in that case, if you want to extract user ids with active terms, you can get it as following :

    users = User.objects.filter(terms__active=True).distinct().values_list("id", flat=True)
userHOyG0
Related