Django rename column when multiple tables are joined

Question

I have this class in models.py:

class Customer(models.Model):
    code = models.CharField(unique=True, max_length=10)
    user = models.OneToOneField(User, on_delete=models.PROTECT)
    phone = models.CharField(max_length=20)
    address = models.TextField(blank=True)
    balance = models.PositiveIntegerField(default=20000)

The Customer has one to one relationship with User class. The User class is the default User class in Django:

from django.contrib.auth.models import User

I want to return result like this:

    "customers": [
        {
            "code": a1,
            "username": "arashb91",            
            "email": "arash@example.com",
            "phone": "111111",
            "address": "somewhere",
            "balance": 20000
        },

but when I use this code:

result = Customer.objects.all().values("code", 'user__username', 'user__email', "phone", "address", "balance")

my result will be like this:

    "customers":
        {
            "code": a1,
            "user__username": "arashb91",
            "user__email": "arash@example.com",
            "phone": "111111",
            "address": "somewhere",
            "balance": 20000
        },

I can rename fields by F method:

result = Customer.objects.all().values("code", username=F('user__username'),
                                       email=F('user__email'), "phone", "address", "balance")

But I get this error on "phone", "address","balance":

Positional argument after keyword argument

"code" is ok because it is before F methods. Order in results are important for me.

I can't use this code either (F for other fields):

result = Customer.objects.all().values("code", username=F('user__username'),
                                       email=F('user__email'), phone=F("phone"))

because I get this error:

The annotation 'phone' conflicts with a field on the model.

I don't want my result be like this: othertable__field

for solving my problem I used a python code for renaming dictionary keys, But that is not a good option. what should I do? Is there any simple solution for that?

use extras https://docs.djangoproject.com/en/1.8/ref/models/querysets/#django.db.models.query.QuerySet.extra — Yeganeh Salami, Apr 08 '20 at 08:06
lookups = Customer.objects.all().annotate(username=F("user__username")).values("code", 'username', "balance")) : balance came after code in the result, not after username — arash, Apr 08 '20 at 08:23

Yeganeh Salami · Answer 1 · 2020-04-08T09:08:29.090

0

maybe you can use extras

address = Customer.objects.extra(select={'code': 'code', 'user__username': 'username', 'user__email': 'email'})

edited Apr 08 '20 at 09:08

answered Apr 08 '20 at 08:10

Yeganeh Salami

575
7
29

maybe I can use it. but please correct syntax. I did something like your code but I got this error: "no such column: user__username" – arash Apr 08 '20 at 08:43
that is up to you and your name of model fields – Yeganeh Salami Apr 08 '20 at 09:06

score 0 · Answer 2 · answered Apr 08 '20 at 08:20

0

First of all, you could skip .all() if you're using .values() Second, here is solution:

result = Customer.objects.values("code", "phone").annotate(
    username=F('user__username'), 
    email=F('user__email')
)

answered Apr 08 '20 at 08:20

Alex Antonov

14,134
7
65
142

it changes the order in the result: "phone" comes after "code" in the result not after username and email. what do you suggest for it? because I have this problem in my project not only for Customer model. also for other models that I created. and I want to return result in Json. so Order is important in the answer. – arash Apr 08 '20 at 08:31

Django rename column when multiple tables are joined

2 Answers2