use sql from queryset

[jokiefer]

Is it possible to use the query from a existing queryset instead of hardcoding a sql query on class attribute.

example:

class MetadataRelationView(pg.MaterializedView):
    title = models.CharField(max_length=4096)
    abstract = models.CharField(max_length=4096)
    keywords = models.CharField(max_length=4096)
    search = models.TextField(max_length=20)

    sql = MetadataRelation.objects.for_search().query

    class Meta:
        managed = False
        db_table = 'registry_metadata_relation_view'

But this causes in AppRegistryNotReady cause the get_sql class function is called in front of app registry...

[mikicz]

Hm interesting! Replicating a similar setup I got a different error (AttributeError: 'Query' object has no attribute 'strip'), but then managed to fix it with putting str around the query:

class SystemView(models.Model):
   is_workflow_system = models.BooleanField(default=False)

...

class SystemView(pg.MaterializedView):
    is_workflow_system = models.BooleanField(default=False)

    sql = str(System.objects.filter(is_workflow_system=False).query)

    class Meta:
        managed = False
        db_table = "system_view"

Maybe it depends on order?

[jokiefer]

still not worked for me. If i run the sync_pgviewss or makemigrations command it will always causes the AppRegistryNotReady error.

raise AppRegistryNotReady("Models aren't loaded yet.")
backend-dev  | django.core.exceptions.AppRegistryNotReady: Models aren't loaded yet.

This is the Manager:

class MetadataRelationManager(models.Manager):

    def for_search(self):
        from registry.models.metadata import Keyword

        # there is a filter used.
        # We need to annotate filterable fields.
        # But we only add annotation for needed fields.
        # So if the filter condition does not lookup for hierarchy_level for example, we don't need to add it to the queryset.
        # TODO: implement a default order by created at
        # add order to get reproduceable query results
        qs = self.get_queryset().order_by("pk")

        qs = qs.annotate(
            hierarchy_level=Case(
                When(dataset_metadata__isnull=False,
                     then=Value("dataset")),
                When(service_metadata__isnull=False,
                     then=Value("service")),
                default=Value("dataset")
            ),
            title=Coalesce(
                "dataset_metadata__title",
                "service_metadata__title",
                "layer__title",
                "feature_type__title",
                "wms__title",
                "wfs__title",
                "csw__title"
            ),
            abstract=Coalesce(
                "dataset_metadata__abstract",
                "service_metadata__abstract",
                "layer__abstract",
                "feature_type__abstract",
                "wms__abstract",
                "wfs__abstract",
                "csw__abstract"
            ),
            bounding_geometry=Coalesce(
                "dataset_metadata__bounding_geometry",
                # TODO: "service_metadata__bounding_geometry",
                "layer__bbox_lat_lon",
                "feature_type__bbox_lat_lon",
                # TODO: get from child layers "wms__bbox_lat_lon",
                # TODO: get from child featuretypes "wfs__bbox_lat_lon",
                # TODO: "csw__bbox_lat_lon"
                output_field=MultiPolygonField()
            ),
            modified_at=Coalesce(
                "dataset_metadata__date_stamp",
                "service_metadata__date_stamp",
                "layer__date_stamp",
                "feature_type__date_stamp",
                "wms__date_stamp",
                "wfs__date_stamp",
                "csw__date_stamp"
            ),
            resource_identifier=Concat(
                F("dataset_metadata__dataset_id_code_space"),
                F("dataset_metadata__dataset_id"),
                output_field=CharField()
            ),
            file_identifier=Coalesce(
                "dataset_metadata__file_identifier",
                "service_metadata__file_identifier",
                Cast("layer__id", CharField()),
                Cast("feature_type__id", CharField()),
                Cast("wms__id", CharField()),
                Cast("wfs__id", CharField()),
                Cast("csw__id", CharField()),
                output_field=CharField()
            ),
            keywords=Case(
                When(
                    hierarchy_level=Value("dataset"),
                    then=ArraySubquery(
                        Keyword.objects.filter(
                            Q(datasetmetadatarecord_metadata__pk=OuterRef(
                                "dataset_metadata__pk"))
                            | Q(layer_metadata__pk=OuterRef("layer__pk"))
                            | Q(featuretype_metadata__pk=OuterRef("feature_type__pk"))
                        ).distinct("keyword").values("keyword")
                    )
                ),
                default=ArraySubquery(
                    Keyword.objects.filter(
                        Q(servicemetadatarecord_metadata__pk=OuterRef(
                            "service_metadata__pk"))
                        | Q(webmapservice_metadata__pk=OuterRef("wms__pk"))
                        | Q(webfeatureservice_metadata__pk=OuterRef("wfs__pk"))
                        | Q(catalogueservice_metadata__pk=OuterRef("csw__pk"))
                    ).distinct("keyword").values("keyword")
                )
            ),
            search=Concat(
                "title",
                Value(" "),
                "abstract",
                Value(" "),
                "keywords",
                output_field=CharField()
            )
        )

        return qs

It orders the query by pk but if i drop the order, the same error will result. The MetadataRelation model does not have any ordering it self.

[jokiefer]

I overwrite the get_sql method like:

    @classmethod
    def get_sql(cls):
        return pg.ViewSQL(str(MetadataRelation.objects.for_search().query), None)

Now it principle seems to work but django does not do type castings on my Value() in the raw query :-(

backend-dev  | psycopg2.errors.UndefinedColumn: column "dataset" does not exist
backend-dev  | LINE 1: ...arelation"."dataset_metadata_id" IS NOT NULL THEN dataset WH...
backend-dev  |                                                              ^
backend-dev  | HINT:  Perhaps you meant to reference the column "registry_datasetmetadatarecord.dataset_id".

dataset shall be a string value from the first When clause.

[jokiefer]

Finally i made it!

Django does not interpolate the parameters from the query. See hint from stackoverflow for description.

So the key was to trigger the execution of the query to get the correct evaluated sql statement from it. See django docs for more details.

My solution is the following:

    @classmethod
    def get_sql(cls):
        repr(MetadataRelation.objects.for_search()) # this will trigger the execition
        last_query = connection.queries[-1].get('sql') # get the last query; this should be the execution from the line below
        return pg.ViewSQL(last_query, None)

[jokiefer]

using repr will not create the correct query. repr will always add a limit of 21 to the query. See discussion on stackoverflow

using list instead will create the correct query.