python Django : Join on the same table

Question

我正在尝试使用 PostgreSQL 中的 ltree 扩展来构建全文地址搜索引擎。

我的模型看起来像这样(略有简化):

from django.db import models


class Addresses(models.Model):
    name = models.CharField(max_length=255)
    path = models.CharField(max_length=255)

因此，此表中的数据将如下所示:

id  |   name       |  path
----------------------------
 1  |  USA         | 1
 2  |  California  | 1.2
 3  |  Los Angeles | 1.2.3

我想对每个实体的聚合名称进行全文搜索。基本上我需要将表中的每一行转换为下一种格式来进行搜索:

    id  |           full_name            |  path
-------------------------------------------------
  1     |  USA                           |   1
  2     |  California USA                |   1.2
  3     |  Los Angeles California USA    |   1.2.3

我这样做是为了让用户可以执行类似“los ang cali”或类似的查询。使用 raw PostgreSQL 查询我没有问题:

SELECT *, ts_rank_cd(to_tsvector('english', full_address), query) AS rank 
FROM (SELECT s.id, s.path, array_to_string(array_agg(a.name ORDER BY a.path DESC), ' ') AS full_address
        FROM "Addresses" AS s INNER JOIN "Addresses" AS a
        ON (a.path @> s.path) GROUP BY s.id, s.path, s.name
) AS subquery, to_tsquery('english', %s) as query WHERE   to_tsvector('english', full_address) @@ query
ORDER BY rank DESC;

这很好用，但是在使用 RawQuerySet 时，我不能使用像 .filter()、.group_by() 这样的东西，分页等

在 Django 中复制它的主要约束是这个JOIN:

JOIN "Addresses" AS a ON (a.path @> s.path)

它用于连接每个元素的所有祖先，然后使用array_agg()、array_to_string函数聚合它们，所以这些函数的输出可以在< strong>全文搜索。

如果有人对如何使用 Django ORM 实现此类事情有更好的想法，请提出建议。

Answer 1

总结

您需要一个由 VIEW 支持的非托管模型。

非托管模型。

创建非托管模型是通过设置 managed 来实现的模型的元选项为 false。

If False, no database table creation or deletion operations will be performed for this model. This is useful if the model represents an existing table or a database view that has been created by some other means. This is the only difference when managed=False. All other aspects of model handling are exactly the same as normal. This includes

强调我的。

因此，如果您创建一个非托管模型，它可以由数据库上的 View 表示，并且您可以访问其上的 .filter()、.group_by() .

View 。

View 就是您的查询。

CREATE OR REPLACE view full_address_tree AS

SELECT a.*, s.id, s.path, array_to_string(array_agg(a.name ORDER BY a.path DESC), ' ') AS full_address
        FROM "Addresses" AS s INNER JOIN "Addresses" AS a
        ON (a.path @> s.path) GROUP BY s.id, s.path, s.name

创建模型

class FullAddressTree(models.Model):
    # copy paste the fields from your Addresses model here
    sid = models.IntegerField()
    sid = models.CharField()

    class Meta:
        # this is the most important part
        managed = False
        db_table = 'full_address_tree' # the name of the view

因此，现在您拥有了一个可用于进行全文搜索而无需求助于原始查询的模型。因此，您可以随意使用 Django ORM 的全部功能。

迁移。

如果您想要迁移，您会发​​现 ./manage.py makemigrations 会导致虚拟迁移。 ./manage.py sqlmigrate 将显示没有为此迁移执行任何 sql 查询。

要修复它并自动创建 View ，请将 RunSQL 调用添加到该迁移中的 operations 列表。

migrations.RunSQL(''' COPY PASTE SQL QUERY FROM ABOVE ''')

注意事项

您创建的非托管模型是只读的。尝试创建、替换、更新或删除将失败。如果您需要此功能，您将需要一个 INSTEAD 触发器。