I have following query that I ran to create a database view inside my SQLite database:
CREATE VIEW customerview AS
SELECT
a.id
, name
, email
, vat
, street
, number
, postal
, city
, country
, geo_lat
, geo_lon
, customer_id
, is_primary
FROM customerbin_address a
, customerbin_customer b
WHERE b.id = a.customer_id
AND a.is_primary = 1
In models.py I added the model:
class Customerview(models.Model):
name = models.CharField(max_length=100, db_column='name')
email = models.EmailField(unique=True, db_column='email')
vat = VATNumberField(countries=['NL', 'BE', 'FR', 'DE', 'UK'], blank=True, null=True, db_column='vat')
street = models.CharField(max_length=100, db_column='street')
number = models.IntegerField(null=True, db_column='number')
postal = models.IntegerField(null=True, db_column='postal')
city = models.CharField(max_length=100, db_column='city')
country = CountryField(db_column='country')
is_primary = models.BooleanField(null=False, db_column='is_primary')
geo_lat = models.DecimalField(max_digits=9, decimal_places=6, blank=True, null=True, db_column='geo_lat')
geo_lon = models.DecimalField(max_digits=9, decimal_places=6, blank=True, null=True, db_column='geo_lon')
class Meta:
managed = False
db_table = 'customerview'
and in admin.py I altered the list:
@admin.register(models.Customerview)
class CustomerviewAdmin(admin.ModelAdmin):
list_display = ('name', 'email', 'vat', 'street', 'number', 'postal', 'city', 'country', 'is_primary', 'geo_lat', 'geo_lon')
readonly_fields = ('name', 'email', 'vat', 'street', 'number', 'postal', 'city', 'country', 'is_primary', 'geo_lat', 'geo_lon',)
How do I programatically add the database view with the query above in my application?
Django's migrations framework lets you execute raw SQL - https://docs.djangoproject.com/en/3.1/ref/migration-operations/#runsql
So, you could create an empty migration (manage.py makemigrations <appname> --empty
) and then edit it to execute your view-creating SQL via a migrations.RunSQL()
call.