psycopg2.errors.WrongObjectType: referenced relation "..." is not a table

[nicolasroy11]

This might be more of a feature request than a bug, but I would love to see it work. So I have a model inheriting DbView and a regular Django model that ForeignKey references the view. Looks like this:

DbView:

from django.db import models
from django_db_views.db_view import DBView

class ResourceView(DBView):
    id = models.UUIDField(primary_key=True)
    name = models.CharField(max_length=50)
    table_name = models.CharField(max_length=50)

    view_definition = """
        SELECT id, name, 'grids' as table_name FROM grids
        UNION
        SELECT id, name, 'grid_regions' as table_name FROM grid_regions
        UNION
        SELECT id, name, 'grid_nodes' as table_name FROM grid_nodes
        UNION
        SELECT id, name, 'nat_gas_hubs' as table_name FROM nat_gas_hubs
        UNION
        SELECT id, name, 'weather_nodes' as table_name FROM weather_nodes
    """

    class Meta:
        managed = False
        db_table = 'resource_view'

Django model using the view by foreign key relation:

from django.db import models
from venergy.iso_service_app.models.resource_db_view import ResourceView

class LongTermCacheResourceAction(models.Model):
    resource = models.ForeignKey(ResourceView, on_delete=models.CASCADE, related_name="resource")
    measure = models.CharField(max_length=100)

MakeMigration throws no problem, but Migrate does. This is where I get

Exception has occurred: ProgrammingError
referenced relation "resource_view" is not a table
psycopg2.errors.WrongObjectType: referenced relation "resource_view" is not a table


The above exception was the direct cause of the following exception:

  File "/Users/nroy/Desktop/veritone-energy/mains/services/iso_service/manage.py", line 20, in main
    execute_from_command_line(sys.argv)
  File "/Users/nroy/Desktop/veritone-energy/mains/services/iso_service/manage.py", line 24, in <module>
    main()
django.db.utils.ProgrammingError: referenced relation "resource_view" is not a table

It'd be awesome to be able to use it that way. Is there some workaround I can finagle?

[BezBartek]

@nicolasroy11
Hello, thanks for reporting issue and sharing your thoughts

First thing:
On the database level, you are unable to make reference by ID to view, because view records does not exist until they are evaluated. They are evaluated on the fly, when you invoke a select statement from that view, and even then, they are not persistent, they are calculated and returned as the result of your query. Any known by me database will not allow making references to them, using foreign keys.

Therefore, It can't be done that way as you're asking for.

Second thing:
I am thinking of your case/problem, that I suppose you are attempting to solve.
two things comes to my mind:

1. Are you sure that you are not dealing with the case for Generic foreign keys? https://docs.djangoproject.com/en/4.2/ref/contrib/contenttypes/#generic-relations
2. If not Generic FK, check my second lib: https://github.com/BezBartek/django-dynamic-from-clause
   This is an attempt to extend Django ORM, to be able to define and use model interface up of any query statement. Maybe you will find it useful.

I hope my answer will help you find sufficient solution