I'm having a hard time figuring out how to model Postgres database view (I'm using Postgres 12) to Django ORM (Django 3.1 and Python 3.8.5). I tried this answered here in stackoverflow but still I got the same error.
To visualized my database schema
Database Objects
Tables:
|_items
|_uom
|_location
|
Views:
|_vItems
the vItems contains the SQL command joining the three tables items, uom, location, when running the SQL command below to the pgAdmin 4. I can fetch all the items data without getting any errors.
SELECT * FROM vItems
Here is my Django models.py code:
class Items(models.Model):
id = models.AutoField(primary_key = True)
item_code = models.CharField(max_length = 100)
sku = models.CharField(max_length = 100)
name = models.CharField(max_length = 100)
description = models.CharField(max_length = 100)
uom_id = models.IntegerField()
location_id = models.IntegerField()
created_date= models.DateTimeField(default=timezone.now)
created_by = models.IntegerField()
updated_date= models.DateTimeField(auto_now = True)
updated_by = models.IntegerField()
class Meta:
db_table = "items"
def __str__(self):
return self.name
def get_absolute_url(self):
return reverse('item_detail', kwargs = {'pk': self.id})
class UOM(models.Model):
id = models.AutoField(primary_key = True)
uom = models.CharField(max_length = 50)
created_date= models.DateTimeField(default=timezone.now)
created_by = models.IntegerField()
updated_date= models.DateTimeField(auto_now = True)
updated_by = models.IntegerField()
class Meta:
db_table = "uom"
def __str__(self):
return self.uom
class Location(models.Model):
id = models.AutoField(primary_key = True)
location = models.CharField(max_length = 50)
created_date= models.DateTimeField(default=timezone.now)
created_by = models.IntegerField()
updated_date= models.DateTimeField(auto_now = True)
updated_by = models.IntegerField()
class Meta:
db_table = "location"
def __str__(self):
return self.location
class vItems(models.Model):
id = models.AutoField(primary_key = True)
item_code = models.CharField(max_length = 100)
sku = models.CharField(max_length = 100)
name = models.CharField(max_length = 100)
description = models.CharField(max_length = 100)
uom_id = models.IntegerField()
location_id = models.IntegerField()
uom = models.CharField(max_length = 50)
location = models.CharField(max_length = 50)
created_date= models.DateTimeField(default=timezone.now)
created_by = models.IntegerField()
updated_date= models.DateTimeField(auto_now = True)
updated_by = models.IntegerField()
class Meta:
managed = False
db_table = "vItems"
Here is my error to Django
Environment:
Request Method: GET
Request URL: http://localhost:8000/items/
Django Version: 3.1
Python Version: 3.8.5
Installed Applications:
['django.contrib.admin',
'django.contrib.auth',
'django.contrib.contenttypes',
'django.contrib.sessions',
'django.contrib.messages',
'django.contrib.staticfiles',
'items.apps.ItemsConfig',
'users.apps.UsersConfig',
'dashboard.apps.DashboardConfig',
'crispy_forms']
Installed Middleware:
['django.middleware.security.SecurityMiddleware',
'django.contrib.sessions.middleware.SessionMiddleware',
'django.middleware.common.CommonMiddleware',
'django.middleware.csrf.CsrfViewMiddleware',
'django.contrib.auth.middleware.AuthenticationMiddleware',
'django.contrib.messages.middleware.MessageMiddleware',
'django.middleware.clickjacking.XFrameOptionsMiddleware']
Template error:
In template D:\DELETE THIS\PiTON\web\fnd\src\templates\index.html, error at line 35
relation "vItems" does not exist
LINE 1: ...Items"."updated_date", "vItems"."updated_by" FROM "vItems" O...
*some python stack tracing here...*
Exception Type: ProgrammingError at /items/
Exception Value: relation "vItems" does not exist
LINE 1: ...Items"."updated_date", "vItems"."updated_by" FROM "vItems" O...
^
EDIT: Additional Information
Here is the 0003_vItems.py file under migrations:
The commented operations = [...] block of code was the original makemigrations items for my vItems model. And the uncommented code below is the solution I've tried but still I'm getting the same error.
# Generated by Django 3.1 on 2020-09-19 09:30
from django.db import migrations, models
import django.utils.timezone
class Migration(migrations.Migration):
dependencies = [
('items', '0002_location_uom'),
]
# operations = [
# migrations.CreateModel(
# name='vItems',
# fields=[
# ('id', models.AutoField(primary_key=True, serialize=False)),
# ('item_code', models.CharField(max_length=100)),
# ('sku', models.CharField(max_length=100)),
# ('name', models.CharField(max_length=100)),
# ('description', models.CharField(max_length=100)),
# ('uom_id', models.IntegerField()),
# ('location_id', models.IntegerField()),
# ('uom', models.CharField(max_length=50)),
# ('location', models.CharField(max_length=50)),
# ('created_date', models.DateTimeField(default=django.utils.timezone.now)),
# ('created_by', models.IntegerField()),
# ('updated_date', models.DateTimeField(auto_now=True)),
# ('updated_by', models.IntegerField()),
# ],
# options={
# 'db_table': 'vItems',
# 'managed': False,
# },
# ),
# ]
sql = """
CREATE VIEW vItems
AS
SELECT it.id,
it.item_code,
it.sku,
it.name,
it.description,
it.uom_id,
it.location_id,
it.created_date,
it.created_by,
it.updated_date,
it.updated_by,
uom.uom,
loc.location
FROM
items it
JOIN uom uom ON uom.id = it.uom_id
JOIN location loc ON loc.id = it.location_id;
"""
operations = [
migrations.RunSQL('DROP VIEW IF EXISTS vItems;'),
migrations.RunSQL(sql)
]
And here is the views.py that calls the vItems from the models.py
from django.shortcuts import render
from django.utils import timezone
from django.contrib.auth.mixins import (
LoginRequiredMixin, # CLASS BASED REQUIRED LOGIN TO GAIN ACCESS
UserPassesTestMixin # UPDATE ONLY IF DATA IS FROM THE CURRENT USER [CAN BE USE SUPERVISOR UPDATE ONLY]
)
from django.views.generic import (
ListView,
DetailView,
CreateView,
UpdateView,
DeleteView
)
from .models import (
Items,
UOM,
Location,
vItems
)
class ItemListView(LoginRequiredMixin, ListView):
model = vItems
template_name = 'items/list.html' # <app>/<model>_<viewtype>.html
context_object_name = 'items'
ordering = ['name']
**some class based views code here...**
To summarize this question base on my own understanding, I'm getting an error from Django as if there is something wrong in my models.py (class vItems) that cannot be translated by Django to native SQL command, something that is related to "relation" stated above on error, I'm guessing the the INNER JOIN statement? But it doesn't make sense to me because the SQL command is perfectly working on pgAdmin 4
What am I missing here to get this error?
Thank you in advance for the answers.
