Django annotate several same objects in QuerySet by different related object

Question

I got:

# models

class Building(models.Model):
    ...


class Flat(models.Model):
    building = models.ForeignKey(Building)


class Profile(models.Model):
    flats = models.ManyToManyField(Flat)

# logic

building = Building.objects.create()
flat_1 = Flat.objects.create(building=building)
flat_2 = Flat.objects.create(building=building)

profile = Profile.objects.create()
profile.flats.add(flat_1)
profile.flats.add(flat_2)

profiles = Profile.objects.filter(flats__building=building)

I got in profiles 2 same profiles. How i can annotate each of them by different flat like this: profiles.first().flat == flat_1 and profiles.last().flat == flat_2?

Maybe Subquery() but how?

UPD I need this in some DRF list view. Output in JSON must be something like:

[
  {
    "profile_id": 1,
    "flat_id": 2
  },
  {
    "profile_id": 1,
    "flat_id": 3
  }
]

Not totally sure what you are trying to achieve but stuff like this becomes easier if you add a through-model (docs.djangoproject.com/en/2.1/ref/models/fields/…) to your ManyToManyField, then you can do queries on that relation as well... — Bernhard Vallant
– Bernhard Vallant, Commented Jan 30, 2019 at 13:54

valex · Accepted Answer · 2019-01-28 16:07:23Z

2

+25

To obtain that output, you could do:

data = Profile.objects.all().values('flats', 'id')
return Response(data=data)

in your DRF view.

edited Jan 28, 2019 at 16:07

valex

5,8972 gold badges40 silver badges41 bronze badges

answered Jan 28, 2019 at 12:43

Fran

8994 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

valex Over a year ago

:). I have view class at 350 lines of code with filters, ordering, etc. and i can't do so simple as .values(). There are a lot of intermediate business logic. I really need something like .annotate() to add field for every object in queryset with correct flat or something like this.

Nathan Do Over a year ago

I think this is a correct answer. No matter how complicated your queryset is you can always put .values('flats', 'id') at the end of that. Order of filter and values are interchangeable

Raydel Miranda Over a year ago

I think in order to get a json like the OP is asking for, you should filter on flats, not getting all profiles objects since the relation is many2many. A more clean/elegant/direct approach would require remodeling. And perhaps that's the OP should be thinking about ...

Raydel Miranda · Accepted Answer · 2019-02-01 17:38:34Z

You don't have to profile instances ...

I wrote the code for your exact needs at the end, but first wrote a couple of things that might be of interest.

In your code sample, you've created only one profile, I'm sure you are not getting 2 instances of Profile that are equals but only one.

The thing is if you have a QuerySet with only one entry, then:

profiles.first() == profiles.last()  # True

since profile.first() and profiles.last() are the same instance.

You should try creating 2 Profile instances:

building = Building.objects.create()

flat_1 = Flat.objects.create(building=building)
flat_2 = Flat.objects.create(building=building)

profile_1 = Profile.objects.create()  # You coud/should use bulk_create here.
profile_2 = Profile.objects.create()

profile_1.flats.add(flat_1)
profile_2.flats.add(flat_2)

Then

profiles = Profile.objects.filter(flats__building=building)

will return two different profile objects.

On the other hand, obtaining the JSON like you want ...

Following the example, you posted, filter flats by profile and get the values (this also works if you have more that one profile).

Flat.objects.filter(profile=profile_1).values('profile__id', 'id')

This will return something like ("id" stands for flats ids):

[
  {
    "profile__id": 1,
    "id": 1
  },
  {
    "profile__id": 1,
    "id": 3
  }
]

If you do not filter by profile (and you have more than one) you could get something like:

[
  {
    "profile__id": 1,
    "id": 1
  },
  {
    "profile__id": 2,
    "id": 3
  },
  {
    "profile__id": 2,
    "id": 4
  },
  ...
]

Annotating to get the EXACT json you want:

Filter as shown previously annotate, and get desired values:

Flat.objects.filter(profile=profile_1).annotate(
    flat_id=F('id')
).annotate(
    profile_id=F('profile__id')
).values(
    'profile_id', 'flat_id'
)

will give exactly what you want:

[
  {
    "profile_id": 1,
    "flat_id": 2
  },
  {
    "profile_id": 1,
    "flat_id": 3
  }
]

Thomas Gak-Deluen · Accepted Answer · 2019-02-03 12:50:42Z

0

You can do that with the right serializer and the right annotation:

The serializer:

class FlatSerializer(serializers.ModelSerializer):
    class Meta:
        model = Flat
        fields = ('flat_id', 'building_id')

    flat_id = serializers.CharField(read_only=True)

Then I would simply query Flats rather than profiles and serialize:

flats = Flat.objects \
    .annotate(flat_id=F('id')) \
    .filter(building=building)

serialized = FlatSerializer(flats, many=True)
print(serialized.data) # [ { flat_id: 1, building_id: 1 }, { flat_id: 2, building_id: 1 } ]

Let me know if that works for you

answered Feb 3, 2019 at 12:50

Thomas Gak-Deluen

3,0012 gold badges33 silver badges40 bronze badges

1 Comment

valex Over a year ago

tgdn thank for answer. My problem is to make doubles in profiles queryset and annotate each of this doubles by different flat from m2m between profile and flat. Your code is about something else :).

Collectives™ on Stack Overflow

Django annotate several same objects in QuerySet by different related object

3 Answers 3

3 Comments

You don't have to profile instances ...

On the other hand, obtaining the JSON like you want ...

Annotating to get the EXACT json you want:

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

You don't have to profile instances ...

On the other hand, obtaining the JSON like you want ...

Annotating to get the EXACT json you want:

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related