python - Django:在最大外键值上选择不同的值

Question

我有以下模型，我正在使用 SQLite3 和 MySQL 进行测试:

# (various model fields extraneous to discussion removed...)

class Run(models.Model):
    runNumber = models.IntegerField()

class Snapshot(models.Model):
    t = models.DateTimeField()

class SnapshotRun(models.Model):
    snapshot = models.ForeignKey(Snapshot)
    run = models.ForeignKey(Run)
    # other fields which make it possible to have multiple distinct Run objects per Snapshot

我想要一个查询，它会给我一组 runNumbers 和快照 ID，其中 Snapshot.id 低于某个指定值。天真地我希望这会起作用:

print SnapshotRun.objects.filter(snapshot__id__lte=ss_id)\
                         .order_by("run__runNumber", "-snapshot__id")\
                         .distinct("run__runNumber", "snapshot__id")\
                         .values("run__runNumber", "snapshot__id")

但这会爆炸

NotImplementedError: DISTINCT ON fields is not supported by this database backend

对于两个数据库后端。不幸的是，Postgres 不是一个选择。

是时候回退到原始 SQL 了吗？

更新:

由于 Django 的 ORM 无法帮助我解决这个问题(感谢@jknupp)，我确实设法让以下原始 SQL 工作:

cursor.execute("""
            SELECT r.runNumber, ssr1.snapshot_id
            FROM livedata_run AS r
            JOIN livedata_snapshotrun AS ssr1
            ON ssr1.id =
            (
                SELECT id
                FROM livedata_snapshotrun AS ssr2
                WHERE ssr2.run_id = r.id
                  AND ssr2.snapshot_id <= %s
                ORDER BY snapshot_id DESC
               LIMIT 1
            );
""", max_ss_id)

livedata 是这些表所在的 Django 应用程序。

Answer 1

笔记in the Django documentation很清楚:

Note:

Any fields used in an order_by() call are included in the SQL SELECT columns. This can sometimes lead to unexpected results when used in conjunction with distinct(). If order by fields from a related model, those fields will be added to the selected columns and they may make otherwise duplicate rows appear to be distinct. Since the extra columns don’t appear in the returned results (they are only there to support ordering), it sometimes looks like non-distinct results are being returned.

Similarly, if you use a values() query to restrict the columns selected, the columns used in any order_by() (or default model ordering) will still be involved and may affect uniqueness of the results.

The moral here is that if you are using distinct() be careful about ordering by related models. Similarly, when using distinct() and values() together, be careful when ordering by fields not in the values() call.

还有，在下面:

This ability to specify field names (with distinct) is only available in PostgreSQL.