Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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)
    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.
                @richard_ Why you think is caused by ordering? Anyway, I comment the Meta, did not migrate, and test, it do not work.
– qg_java_17137
                Jun 4, 2018 at 12:39
                The code you posted is difficult to replicate. Please note that including your import statements is part of MCVE (particularly, complete and verifiable). See meta.stackoverflow.com/questions/312045/… for discussion.
– Zev
                Jun 4, 2018 at 16:15
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.
If you can get what you want done using just the ORM's functions, do that!
If need be, fall back on .annotate() to add extra info to the query
If what you need to have done can't be using the above tools, then use .extra()
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
Start a shell by typing python manage.py shell
from django.db import connection
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.
list your databases: SHOW DATABASES;
connect to your database: USE your_database_name_here;
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!
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;
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.
                thanks for your answer, I tried. I get (1054, "Unknown column 'api_switchesport.name' in 'field list'") error. But your answer let me know the issue is caused by the sort. if I do not use the 'length' for sort I will not get the error.
– qg_java_17137
                Jun 5, 2018 at 8:49
                I assumed your app name was api because that's the base route you used. If your app name is something different, it'll be stored differently in your database.  Please see my edit and the code we can use to diagnose this further.
– Zev
                Jun 5, 2018 at 14:24
                I made a significant edit to add advice regarding debugging SQL in Django for this and future query/db issues.
– Zev
                Jun 6, 2018 at 14:25
This is a well know limitation of extra(); it won't properly alias references. I suggest you stay way from it as it should only be used as a last resort.
Instead you should use annotate(Length)
annotate(
    length=Length('name'),
).order_by('length', 'name')
https://docs.djangoproject.com/en/2.0/ref/models/database-functions/#length
Or simply pass the Length to order_by if it's only annotated for ordering purpose
.order_by(Length('name'), name)
https://docs.djangoproject.com/en/2.0/ref/models/querysets/#order-by
                I learned a good bit from your answer! I don't want to delete my answer because when OP original posted the question, the get_queryset(self): code wasn't included so I feel my answer still adds value regarding the debugging process (and I did a good amount of work with OP to figure out where the issue was) but I edited my answer and credited you to reflect the better advice that your answer offers (and of course, upvoted your answer).
– Zev
                Jun 7, 2018 at 14:03
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.