django.db.utils.OperationalError: (1052, "Column 'name' in field list is ambiguous")

Question

In my Django project I get below error when I query my data:

django.db.utils.OperationalError: (1052, "Column 'name' in field list is ambiguous")

using:

http://localhost:8000/api/physicalserver/list/?switchesport__bandwidth=10

but if I use:

http://localhost:8000/api/physicalserver/list/?switches__id=xxx

It will work fine.

my ListAPIView code:

class PhysicalServerListAPIView(ListAPIView):
    serializer_class = PhysicalServerListSerializer
    permission_classes = [AllowAny]
    pagination_class = CommonPagination
    def get_queryset(self):
        query_params = self.request.query_params
        filters = {'{}__contains'.format(key): value
               for key, value in query_params.items()
               }
        qs = PhysicalServer.objects.filter(**filters)
        return qs.extra(select={'length':'Length(name)'}).order_by('length', 'name')

my serializer code:

class PhysicalServerListSerializer(ModelSerializer):
    bandwidth = serializers.SerializerMethodField()

    class Meta:
        model = PhysicalServer
        fields = "__all__"
        depth = 1

    def get_bandwidth(self, obj):
        return obj.switchesport.bandwidth

my model of PhysicalServer:

class PhysicalServer(models.Model):         
    name = models.CharField(max_length=32)

    switches = models.ForeignKey(to=Switches, on_delete=models.DO_NOTHING)
    physical_server_model = models.ForeignKey(to=PhysicalServerModel, null=True, on_delete=models.DO_NOTHING)
    switchesport = models.OneToOneField(to=SwitchesPort, related_name="physical_server", on_delete=models.DO_NOTHING)
    ...

---

EDIT-1

My Switches Model:

class Switches(models.Model):

    name = models.CharField(max_length=32)
    desc = models.CharField(max_length=256)
    routerdevice = models.ForeignKey(to=RouterDevice, related_name="switches")

    gatewaydevice = models.ForeignKey(to=GatewayDevice,  related_name="switches")

    ctime = models.DateTimeField(auto_now_add=True)
    uptime = models.DateTimeField(auto_now=True)

    class Meta:
        ordering = ['-name']

    def __str__(self):
        return self.name
    def __unicode__(self):
        return self.name

and my SwitchesPort model code:

class SwitchesPort(models.Model):

    name = models.CharField(max_length=32, unique=True)  
    desc = models.CharField(max_length=256, null=True, blank=True)
    switches = models.ForeignKey(to=Switches, on_delete=models.CASCADE,related_name="switchesports")
    vlanedipv4networkgroup = models.ForeignKey(
        to=VlanedIPv4NetworkGroup,  
        null=True,
        blank=True,
        on_delete=models.SET_NULL,
        related_name="switchesports")

    bandwidth = models.IntegerField(default=10)

    ctime = models.DateTimeField(auto_now_add=True)
    uptime = models.DateTimeField(auto_now=True)

    class Meta:

        ordering = ['name']

    def __str__(self):
        return self.name
    def __unicode__(self):
        return self.name

Edit-2

My PhysicalServerModel, (it should be PhysicalServerType):

class PhysicalServerModel(models.Model):

    name = models.CharField(max_length=32)
    desc = models.CharField(max_length=256)

    cpu = models.CharField(null=True, blank=True, max_length=64)  
    ram = models.CharField(null=True, blank=True, max_length=64)  
    disk = models.CharField(null=True, blank=True, max_length=64)
    bandwidth = models.CharField(null=True, blank=True, max_length=64, default=10)
    price = models.DecimalField(null=True, blank=True, max_digits=8, decimal_places=2, max_length=16)

    ctime = models.DateTimeField(auto_now_add=True)
    uptime = models.DateTimeField(auto_now=True)

    class Meta:
        ordering = ['-id']

    def __str__(self):
        return self.name
    def __unicode__(self):
        return self.name

my djangorestframework version is 3.7.1, django version is 1.11.1. and I use MySQL as my database.

---

EDIT-3

So far, we found the issue is caused because the name field is ambiguous when I try to sort by its length in PhysicalServerListAPIView:

 return qs.extra(select={'length':'Length(name)'}).order_by('length', 'name')

if I return the qs directly, I will not have this issue.

Answer 1

The issue is with your get_queryset code.

You have

return qs.extra(select={'length':'Length(name)'}).order_by('length', 'name')

Update: @Simon Charette pointed that .extra() is not needed here since this behavior can be done without falling back on it.

As Simon suggests your best bet is to do things making as much use of Django's ORM as possible and only fall back on .extra() if all else fails. His suggest of doing .order_by(Length('name'), name) is probably the best solution for what you are trying to achieve.

Researching a bit more, here's when you should use .extra(), .annotate() or just the ORM's basic functions like .order_by(). Here's a short discussion of it on Reddit that's easy to digest.

1. If you can get what you want done using just the ORM's functions, do that!
2. If need be, fall back on .annotate() to add extra info to the query
3. If what you need to have done can't be using the above tools, then use .extra()
4. If even that fails, fall back on manual SQL queries using .raw()

Of course, everything can be done using manual SQL queries but the whole point of an abstraction layer is to make as much use of it as possible.

If you want to power through and use extra anyway, here's how you'd have to do it:

return qs.extra(select={'length':'Length(APP_NAME_MODEL_NAME.name)'}).order_by('length', 'api_MODELNAME.name')

Of course, replace APP_NAME and MODEL_Name with your values. For me, it was api_switchesport. See my advice below about checking how Django has actually named your tables in the "Debugging by directly connecting to your database" section.

Again, following Simon's suggestion, I don't think you really even need the get_queryset function in your view and can just do the following in your urls.py:

from django.db.models.functions import Length

urlpatterns = [
    url(r'^physicalserver/list/$', 
    PhysicalServerListAPIView.as_view (queryset=
        PhysicalServer.objects.all().order_by(
            Length('name'), 'name'
        )
    ), name='physicalserver-list'),
]

---

Debugging SQL

The main issue here is/was with a SQL query that wasn't working. Perhaps for you, but also for those who may find this, let me go over debugging SQL in Django.

Logging all queries

See this question regarding logging all queries (and an interesting tool for seeing what queries occurred)

Showing one query

To just show one problem query you can do the following (I included your example but replace qs.extra with another query you may need to debug):

See here for more details: django orm, how to view (or log) the executed query?

from django.db import connection
result = qs.extra(select={'length':'Length({}_{}.name)'.format(appname, field)}).order_by('length', '{}_{}.name'.format(appname, field))
print(connection.queries)
return result

Debugging in the Shell

I didn't use this method much here but here's how you get started

1. Start a shell by typing python manage.py shell
2. from django.db import connection
3. Test out ORM python commands. See playing with the api

Debugging by directly connecting to your database

This one is the most interesting here because Django makes so many decisions for us, we may be unaware of basics like column names in our database.

To connect to the MySQL database (SQLite is the default database but qg_java_17137 used MySQL) I typed sudo mysql but various other questions answer how to connect to different types of databases. For SQLite, this would be sqlite3 example.db or a similar command.

This gave me a prompt mysql> to enter commands.

1. list your databases: SHOW DATABASES;
2. connect to your database: USE your_database_name_here;
3. show your tables: SHOW TABLES;

This got me this listing:

+----------------------------+
| Tables_in_sandbox          |
+----------------------------+
| api_gatewaydevice          |
| api_physicalserver         |
| api_physicalservermodel    |
| api_routerdevice           |
| api_switches               |
| api_switchesport           |
| api_vlanedipv4networkgroup |
| auth_group                 |
| auth_group_permissions     |
| auth_permission            |
| auth_user                  |
| auth_user_groups           |
| auth_user_user_permissions |
| django_admin_log           |
| django_content_type        |
| django_migrations          |
| django_session             |
+----------------------------+

This tells us an interesting thing. Django has prepended my app name ("api") to all of the tables in the database. This makes sense because different apps often have tables with the same name but because Django made this decision for us, we may be unaware of the actual name of our table!

4. Test out the query which you got from the "debugging one query" step.

   SELECT (Length(api_switchesport.name)) AS length, api_physicalserver.id, api_physicalserver.name, api_physicalserver.switches_id, api_physicalserver.physical_server_model_id, api_physicalserver.switchesport_id
   FROM api_physicalserver
   INNER JOIN api_switchesport ON
   (api_physicalserver.switchesport_id = api_switchesport.id)
   WHERE api_switchesport.bandwidth LIKE '%10%' ORDER BY length ASC;

5. For me, this query successfully executes but if it throws errors for you, try modifying things. Particularly the field name api_switchesport.name. Originally that came up just as name for me and since nearly all your tables have a name field, your database wasn't sure which table is referred to.

Learning more about SQL

Between Django, Django Rest Framework and Django's ORM (object relationship mapper) we can do a lot without this kind of digging into SQL. However, when you hit issues, it helps to know some SQL conventions (again, not assuming you don't, but some reading this answer may be new to it).

Here, the main one is that while we can refer to fields only by name, once we are dealing with multiple tables with the same name, we need to use dot notation. table_name.field_name to avoid errors like you received.

Try W3School's interactive queriable database. They also have a tutorial that goes with it.