How to use raw SQL in Django Admin's get_queryset?

问题: I am trying to use Model.objects.raw() in Django Admin but end up with a generic database error: "Something's wrong with your database installation. Make sure the appropria...

问题:

I am trying to use Model.objects.raw() in Django Admin but end up with a generic database error: "Something's wrong with your database installation. Make sure the appropriate database tables have been created, and make sure the database is readable by the appropriate user."

admin.py

class ChapterAdmin(admin.ModelAdmin):
    fieldsets = [
        (None, {'fields':['title','shorthand','description',]})
    ]
    list_display = ('shorthand','title')

    def get_queryset(self, request):
        queryset = super(ChapterAdmin, self).get_queryset(request)
        sql = "SELECT * FROM myapp_chapter"
        queryset = Chapter.objects.raw(sql)
        return queryset

models.py

class Chapter(models.Model):
    title = models.CharField(max_length=200)
    description = models.TextField(max_length=800, blank=True, null=True)
    shorthand = models.SlugField(max_length=3, unique=True, null=True)

    def __str__(self):
        return self.shorthand

Everything functions if queryset is set to Chapter.objects.all() instead of Chapter.objects.raw(sql), so I initially assumed that my raw SQL was incorrect. I also read that errors can occur if Models.objects.raw() fails to return rows. However, running SELECT * FROM myapp_chapter in dbshell returns everything normally. How do I get a queryset using raw SQL in Django Admin?

For context on why I am choosing to use raw SQL, the shorthand contains a mixture of numeric and alphanumeric values that I need sorted naturally:

1
10
11
4
5
7A
7B

I get the results I want in dbshell using SELECT * FROM myapp_chapter ORDER BY shorthand*1, shorthand:

1
4
5
7A
7B
10
11

However, I couldn't find a way to achieve this result without using raw SQL. Is there a better way to achieve this without using raw SQL?


回答1:

Getting the SQL from a query is not all that hard: Getting the SQL from a Django QuerySet It's only rarely the right solution though, besides debugging I wouldn't recommend it.

The better solution would be to either use extra() through the Django ORM or by creating an extra column (which will be loads faster since the latter can use an index): Django QuerySet ordering by expression

  • 发表于 2018-12-28 07:51
  • 阅读 ( 346 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除