内连接子查询Django ORM等效

Question

内连接子查询Django ORM等效

6

我有三个相关表：

模块

+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| module_id   | int(11)     | NO   | PRI | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

事件

+------------------+--------------------------+------+-----+---------+----------------+
| Field            | Type                     | Null | Key | Default | Extra          |
+------------------+--------------------------+------+-----+---------+----------------+
| event_id         | int(11)                  | NO   | PRI | NULL    | auto_increment |
| event_time       | datetime(4)              | NO   |     | NULL    |                |
| module_id        | int(11)                  | NO   | MUL | NULL    |                |
| file_id          | int(11)                  | YES  | MUL | NULL    |                |
+------------------+--------------------------+------+-----+---------+----------------+

文件

+--------------+-----------------------------+------+-----+---------+----------------+
| Field        | Type                        | Null | Key | Default | Extra          |
+--------------+-----------------------------+------+-----+---------+----------------+
| file_id      | int(11)                     | NO   | PRI | NULL    | auto_increment |
| path         | varchar(512)                | NO   | UNI | NULL    |                |
+--------------+-----------------------------+------+-----+---------+----------------+

所以，有模块，事件和文件。（为了简化表格，未使用的字段已被削减。） 目标： 我想要获取每个模块中最新发生的事件及其文件路径。 我尝试过的方法： 因此，首先我在Django上使用子查询创建了一个天真的实现：

last_event_subquery = Event.objects.filter(
        module_id__module_id=OuterRef('module__id')
    ).order_by('-event_time', '-event_id')

modules = Module.objects.all().annotate(
        last_event_path=Subquery(last_event_subquery.values('file_id__path')[:1])
    ).annotate(
        last_event_id=Subquery(last_event_subquery.values('event_id')[:1])
    ).annotate(
        last_event_datetime=Subquery(last_event_subquery.values('event_time')[:1])
    )

然而，我发现在事件表中运行超过1百万条记录非常慢。当然，有几个索引可以优化所有的事情，但即使这样，我也无法找到一组索引，它们的运行时间少于5秒，这对我来说太长了。我看到原因是，相应的SQL查询太愚蠢了：

SELECT `module`.`module_id`,
       (SELECT U2.`path` FROM `events` U0 LEFT OUTER JOIN `files` U2 ON (U0.`file_id` = U2.`file_id`)
       WHERE U0.`module_id` = (`modules`.`module_id`) ORDER BY U0.`event_time` DESC, U0.`event_id` DESC  LIMIT 1)
       AS `last_event_path`,
       (SELECT U0.`event_id` FROM `events` U0
       WHERE U0.`module_id` = (`modules`.`module_id`) ORDER BY U0.`event_time` DESC, U0.`event_id` DESC  LIMIT 1)
       AS `last_event_id`,
       (SELECT U0.`event_time` FROM `events` U0
       WHERE U0.`module_id` = (`modules`.`module_id`) ORDER BY U0.`event_time` DESC, U0.`event_id` DESC  LIMIT 1)
       AS `last_event_time` FROM `events`

正如您所看到的，它在重复子查询三次。

因此，我决定尽力在SQL中实现最好的结果，但我还是很难做到以下内容：

SELECT module.module_id,
       events.event_id,
       events.event_time,
       files.path
       FROM modules INNER JOIN events ON events.event_id =
           (SELECT events.event_id FROM events
            WHERE modules.module_id = events.module_id
                ORDER BY events.event_time DESC, events.event_id DESC LIMIT 1)
       INNER JOIN files ON files.file_id = events.file_id;

这个程序运行时间为0.001秒。问题现在是我不能用Django的ORM语言完成这个任务。当然，我可以直接放置原生SQL查询，但我怎么能忍受这样的耻辱呢？

我已经查看了整个Django文档，在stackoverflow上找到了很多问题，但都没有找到答案。我最接近的一个答案是这个，但问题是我不能限制结果为每个模块只有一个。

我也尝试了FilteredRelation，但没有得到适当的过滤器。我也不能使用select_related()，因为这是一个带ForeignKey的反向关系。我也不能使用distinct()和列字段，因为我正在使用MySQL（更具体地说，是MariaDB 10.3版本）。

您有什么建议来解决这个问题吗？

谢谢！

- Guillermo Rodríguez

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Guillermo Rodríguez · Answer 1

所以，我自己找到了答案 :)

它生成了我想要的完全相同的SQL，除了子句的顺序，这并不影响查询。关键是使用带有关系的.filter()。在filter()中使用子查询（我不知道这是可能的）就解决了问题。我受到this answer的启发。

last_event_subquery = Event.objects.filter(
    module_id__module_id=OuterRef('module_id')
).order_by('-event_time', '-event_id')
modules = Module.objects.filter(
    event__event_id=Subquery(
        last_event_subquery.values('module_id')[:1])
).values('id', 'event__event_id', 'event__event_time', 'event__file__path')

这将产生以下SQL：

SELECT `modules`.`module_id`,
       `events`.`event_id`,
       `events`.`event_time`,
       `files`.`path`
       FROM `modules`
       INNER JOIN `events` ON (`modules`.`module_id` = `events`.`module_id`)
       LEFT OUTER JOIN `files` ON (`events`.`file_id` = `files`.`file_id`)
       WHERE `events`.`event_id` =
            (SELECT U0.`event_id` FROM `events` U0
            WHERE U0.`module_id` = (`modules`.`module_id`)
                 ORDER BY U0.`event_time` DESC, U0.`event_id` DESC  LIMIT 1)

希望这对其他人有用。