Btukfyl

If i add filter in the manager, sql query to the database will be too long, because my filter will be at start of sql-query.

I need to run this filter at the end, after all the other filters and changes have been applied.

Current queryset:

links = Link.objects.all().filter_deleted()

links = links.filter(linkname='linkname', left_uuid__in=all_uuids)

links = links.filter(left_type='type')

links = links.values_list('left_uuid', 'right_uuid', 'right_type')

SQL query after compiling the queruset:

SELECT "baseobj_link"."left_uuid", "baseobj_link"."right_uuid", "baseobj_link"."right_type_id"

FROM "baseobj_link"

WHERE (

    "baseobj_link"."id" IN (

        SELECT DISTINCT ON (U0."linkname", U0."left_uuid", U0."right_uuid") U0."id" AS Col1

        FROM "baseobj_link" U0

        WHERE U0."config_id" IN (2848)

        ORDER BY U0."linkname" ASC, U0."left_uuid" ASC, U0."right_uuid" ASC, U0."domain_level" ASC, U0."config_id" DESC, U0."is_deleted" DESC

    ) AND

    "baseobj_link"."is_deleted" = false AND

    "baseobj_link"."linkname" = 'linkname' AND

    "baseobj_link"."left_uuid" IN (

        SELECT V0."uuid" AS Col1

        FROM "structure_cgw" V0

        WHERE (

            V0."id" IN (

                SELECT DISTINCT ON (U0."uuid") U0."id" AS Col1

                FROM "structure_cgw" U0

                WHERE U0."config_id" IN (2848)

                ORDER BY U0."uuid" ASC, U0."domain_level" DESC, U0."config_id" DESC

            ) AND

            V0."is_deleted" = false

        )

    ) AND

    "baseobj_link"."left_type_id" = 6

);

args=(2848, False, 'linkname', 2848, False, 6)

The part that is generated by filter_deleted():

"baseobj_link"."id" IN (

    SELECT DISTINCT ON (U0."linkname", U0."left_uuid", U0."right_uuid") U0."id" AS Col1

    FROM "baseobj_link" U0

    WHERE U0."config_id" IN (2848)

    ORDER BY U0."linkname" ASC, U0."left_uuid" ASC, U0."right_uuid" ASC, U0."domain_level" ASC, U0."config_id" DESC, U0."is_deleted" DESC

)

It's necessary to add this code at the end of the all queries for this model, so it's not a simple task.

How can this be solved most efficiently?

Sounds like you are trying to run a filter on a subquery. So put all the code you wish to run first inside the subquery. Then run the filters you wish to apply after that query has finished in the last filter.

Would look something like this:

SELECT *

FROM    (

        SELECT "baseobj_link"."left_uuid", "baseobj_link"."right_uuid", "baseobj_link"."right_type_id"

        FROM "baseobj_link"

        WHERE

            "baseobj_link"."is_deleted" = false AND

            "baseobj_link"."linkname" = 'cgw_vhost' AND

            "baseobj_link"."left_uuid" IN (

                SELECT V0."uuid" AS Col1

                FROM "structure_cgw" V0

                WHERE (

                    V0."id" IN (

                        SELECT DISTINCT ON (U0."uuid") U0."id" AS Col1

                        FROM "structure_cgw" U0

                        WHERE U0."config_id" IN (2848)

                        ORDER BY U0."uuid" ASC, U0."domain_level" DESC, U0."config_id" DESC

                    ) AND

                    V0."is_deleted" = false

                )

            AND "baseobj_link"."left_type_id" = 6

        ) AS t1

WHERE t1.id IN (

    SELECT DISTINCT ON (U0."linkname", U0."left_uuid", U0."right_uuid") U0."id" AS Col1

    FROM "baseobj_link" U0

    WHERE U0."config_id" IN (2848)

    ORDER BY U0."linkname" ASC, U0."left_uuid" ASC, U0."right_uuid" ASC, U0."domain_level" ASC, U0."config_id" DESC, U0."is_deleted" DESC

)

From the MYSQL documentation on derived tables:

A derived table is an expression that generates a table within the scope of a query FROM clause. For example, a subquery in a SELECT statement FROM clause is a derived table:

SELECT ... FROM (subquery) [AS] tbl_name ...

Let me know how this works out.

В своей модели queryset'а дополнительно храним queryset без filter_deleted и переопределяем метод _filter_or_exclude, чтобы тот вызывал _filter_or_exclude у хранимого queryset'а, а потом уже filter_deleted. Также храним флаг фильтрации, чтобы знать когда нужно вызывать базовый _filter_or_exclude, а когда filter_deleted.