Django Annotate - Concat from last created ManyToMany Object Fields

Question

class User(models.Model):
    name=CharField()


class Address(models.Model):
    user= Foreignkey(user, related_name="Addresses")
    buildingname=CharField()
    subbuildingname=CharField()
    town=CharField()
    ...

I have a models like top and want to extract data as list below.

{
    [
        ['USERNAME1', 'A XYZBUILDING 14 Drain St. '],
        ['USERNAME2', 'C XXXBUILDING 13 Drain St. '],
        ['USERNAME3', 'B ZZZBUILDING 12 Drain St. '],
        ...
    ]
    
}

Also as a workaround, this is a closest thing to the answer but i could not make it finish

without looping every user or

without executing raw sql in extra method's select field.

I want to achive this with Django ORM.

address_subquery = Address.objects.filter(user__id=F('pk')).order_by('created')
User.objects.filter(**my_custom_filters).annotate(
    subbuildingname_str=Subquery(address_subquery.values('subbuildingname')[:1], output_field=CharField()),
    street_str=Subquery(address_subquery.values('street')[:1], output_field=CharField()),
    buildingname_str=Subquery(address_subquery.values('buildingname')[:1], output_field=CharField()),
    buildingnnumber_str=Subquery(address_subquery.values('buildingnnumber')[:1], output_field=CharField()),
).annotate(
    address_text_str=Concat(
        'subbuildingname_str', Value(' '),
        'buildingname', Value(' '),
        'buildingnumber', Value(' '),
        'street_str',
        output_field=CharField(),
        default=""
    )
).values_list('name', 'address_text_str')

Answer 1

Actually you are doing wrong in subquery, you have to write subquery like this :-

from django.db.models import OuterRef

address_subquery = Address.objects.filter(user=OuterRef('pk')).order_by('created')

You can refer from here https://docs.djangoproject.com/en/3.2/ref/models/expressions/#subquery-expressions