CODEWITHSUNDEEP
pythondjangopython-3.xdjango-modelsdjango-queryset
Maxim Koro
Maxim Koro

Reputation: 63

How to do complex join in Django

I have Custom user model and two models, both with ForeinKey to two users at once:

class Feature1(models.Model):
    user1 = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE, related_name='u1')
    user2 = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE, related_name='u2')

    some field....
    percentage = models.FloatField()

class Feature2(models.Model):
    user1 = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE, related_name='us1')
    user2 = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE, related_name='us2')

    some fields...
    property = models.PositiveIntegerField()

and then I retrieve all pairs for particular user with queryset

queryset = Feature1.objects.filter(u1=self.request.user).all().order_by('-percentage')

but what I need, to have in this queryset data from Feature2 model too (for each particular pair of users, if exists) and to be able order queryset by 'property' from Feature2

How to do it? I've researched django docs, but without result.

Upvotes: 0

Views: 1119

Answers (1)

Matt Cremeens
Matt Cremeens

Reputation: 5151

One try would be to first get all of the user1s and user2s from the first relation

u1 = Feature1.objects.all().values_list('user1', flat=True)
u2 = Feature1.objects.all().values_list('user2', flat=True)

and then get the Feature2 objects from the second relation that match those users

queryset = Feature2.objects.filter(user1__in=u1, user2__in=u2).order_by('-percentage')

I'd like to also offer an alternative to your database design that I feel will help you query your models more efficiently. Why not change Feature2 so that it has a ForeignKey to Feature1 like so

class Feature2(models.Model):
    feature1 = models.ForeignKey(Feature1, verbose_name="Feature 1")

    ...

Then you could join the two this way

queryset = Feature2.objects.filter(feature1__in=Feature1.objects.filter(u1=self.request.user)).order_by('-percentage')

Upvotes: 1

Related Questions