How to SELECT a field from a LEFT JOIN'ed table/model with Django's ORM (1.11)

240

January 02, 2018, at 6:37 PM

Context: Using Django Rest Framework, I've created a ModelViewSet.

With these models:

class Claim(models.Model):
    permalink = models.SlugField(max_length=255, blank=True, unique=True)
    author = models.ForeignKey(get_user_model(), db_index=True, on_delete=models.SET_NULL, null=True, blank=True)
    deleted = models.BooleanField(db_index=True, default=False)
    collaborators = models.ManyToManyField(get_user_model(), through='ClaimCollaborator', through_fields=('claim', 'user'), related_name='claims')
    # ...other fields
class ClaimCollaborator(models.Model):
    claim = models.ForeignKey(Claim, db_index=True, on_delete=models.CASCADE)
    user = models.ForeignKey(get_user_model(), db_index=True, on_delete=models.CASCADE)
    access_project_only = models.BooleanField(default=True)

I'm trying to query Claim to LEFT JOIN ClaimCollaborator and bring back the access_project_only field. ClaimCollaborator is actually an intermediary model handling the ManyToMany relationship between claims and users (collaborators of the claim).

So far I have the following view (cut down for brevity):

class ClaimViewSet(viewsets.ModelViewSet):
    permission_classes = (IsAuthenticated, )
    serializer_class = serializers.ClaimSerializer
    lookup_field = 'permalink'
    def get_queryset(self):
        return models.Claim.objects.filter(Q(author=self.request.user) | Q(claimcollaborator__user=self.request.user))

Serializer:

class ClaimSerializer(serializers.HyperlinkedModelSerializer):
    author = serializers.PrimaryKeyRelatedField(allow_null=True, queryset=get_user_model().objects.all())
    class Meta:
        model = Claim
        fields = ('url', 'permalink', 'author', 'deleted')
        lookup_field = 'permalink'
        extra_kwargs = {
            'url': {'lookup_field': 'permalink'},
        }

Listing produces this SQL:

SELECT "api_claim"."permalink", "api_claim"."author_id", "api_claim"."deleted" 
LEFT OUTER JOIN "api_claimcollaborator" ON ("api_claim"."id" = "api_claimcollaborator"."claim_id") 
WHERE ("api_claim"."author_id" = 39 OR "api_claimcollaborator"."user_id" = 39)

So I'm getting the LEFT JOIN on "api_claimcollaborator" (ClaimCollaborator) just fine, but none of the fields. I've tried .only(<claim fields>, 'claimcollaborator__access_project_only') and .selected_related('claimcollaborator') on the query but this just produces errors (I can be more specific about my attempts if that's helpful - just let me know).

I'm guessing this isn't so straightforward because the table in question is used as a ManyToMany within the ORM? Any help would be greatly appreciated.

Answer 1

You can use SlugRelatedField on the serializer to indicate a field on a related model identified by a specific attribute.

class ClaimSerializer(serializers.HyperlinkedModelSerializer):
    author = serializers.PrimaryKeyRelatedField(allow_null=True, queryset=get_user_model().objects.all())
    claimcollaborator_set = serializers.SlugRelatedField(slug_field='access_project_only', read_only=True, many=True)
    class Meta:
        model = Claim
        fields = ('url', 'permalink', 'claimcollaborator_set', 'author', 'deleted')