Django复杂MySQL查询从PHP与多个连接到Django ORM

I have this code from Kohana (it's easily understandable) and I want to convert this to Django ORM given the ff. models:

我有来自Kohana的这个代码（这很容易理解）我希望在给定ff的情况下将其转换为Django ORM。楷模：

class Item(models.Model):
    glasses = models.ManyToManyField(Glass, through="ItemGlass")
    collection = models.ForeignKey(Collection)

class ItemGlass(models.Model):
    glass = models.ForeignKey(Glass)
    item = models.ForeignKey(Item)

class Collection(models.Model):
    name = models.CharField(max_length=100)

class Code(models.Model):
    code = models.CharField(max_length=30)
    description = models.TextField()

class Glass(models.Model):
    collection = models.ForeignKey(Collection)
    code = models.ForeignKey(Code)

and the php query (uses Kohana's Database Lib)

和php查询（使用Kohana的数据库库）

$this->select(
                array('cd.id', 'id'), 
                array('cd.description','description'),
                array('COUNT(DISTINCT("items.id"))', 'count')
            )
                    ->from('items')
            ->join(array('collections', 'c'))
            ->on('c.id', '=', 'items.collection_id')
            ->join(array('glasses', 'g'))
            ->on('g.collection_id', '=', 'c.id')
            ->join(array('code', 'cd'))
            ->on('cd.id', '=', 'g.code_id')
            ->where('items.discontinued', '=', FALSE)
            ->group_by('cd.id');

NOTE: the "array" clause you see is translated as

注意：您看到的“数组”子句被翻译为

"SELECT cd.id AS id, cd.description AS description, COUNT(DISTINCT(items.id) AS count"

The thing is how do I do it? I can't successfully use select_related to join multiple tables in this case, and I can't find a good "filter trick" for the query. Any ideas?

问题是我该怎么做？在这种情况下，我无法成功使用select_related连接多个表，我找不到查询的好“过滤技巧”。有任何想法吗？

EDIT: I am considering doing it in plain SQL, but I would prefer to avoid it if a Django ORM query can be done :)

编辑：我正在考虑在纯SQL中执行它，但我宁愿避免它，如果可以完成Django ORM查询:)

2 个解决方案

#1

I have come up with this after an hour of "head banging":

经过一个小时的“敲头撞击”后，我想出了这个：

glasses = Code.objects.filter(glass__collection__item__discontinued=False)\
        .values('id', 'description')\
        .annotate(count=Count('glass__collection__item__id', distinct=True))

#2

Definitely don't use SQL, it's a simple query which should be no problem with the ORM. Use something like:

绝对不要使用SQL，这是一个简单的查询，对ORM应该没问题。使用类似的东西：

Code.objects.filter(glass__item__discontinued=False) \
    .annotate(count=models.Count('glass__item__id'))

You can add a .values(...) to the end of it if you only want to retrieve specific columns, as in your php example.

如果您只想检索特定列，则可以在其末尾添加.values（...），如php示例中所示。

#1