Return to Answer

Expanded upon where ordering and limiting to n most recent articles occurs as requested by joshp

edited Aug 14, 2021 at 21:05

#models#mymodels.py class ErroneousViewException(Exception): """Custom Error that is raised when the view V_ALL_ARTICLES turns out to contain incorrect information""" def __init__(self, faulty_record_id: int, table: str, message: str) -> None: self.faulty_record_id = faulty_record_id, self.table = table self.message = message super().__init__(message) class AllArticlesView(models.Model): table_name = models.CharField(max_length=100, help_text="The name of the table that the record belongs to") record_id = models.PositiveIntegerField(help_text="The id of a record in the table with the given name") update_datetime = models.DateTimeField(db_index=True, help_text="The time the referenced record was last updated") guid = models.CharField(max_length=100, primary_key=True) class Meta: managed = False unique_together = ('table_name', 'record_id') db_table = "v_all_articles" def get_record_model(self) -> models.Model: (app_name, model_name) = self.table_name.split("_") return apps.get_model(app_name, model_name) def get_article_object(self) -> models.Model: article_model: models.Model = self.get_record_model() article_objects = article_model.objects.filter(pk=self.record_id)[0] if len(article_objects) == 0: raise ErroneousViewException( self.record_id, self.table_name, message=f"The view V_ALL_ARTICLES stored the record_id {self.record_id} of table {self.table_name} but no such record exists!" ) if len(article_objects) > 1: raise ErroneousViewException( self.record_id, self.table_name, message=f"The view V_ALL_ARTICLES stored the record_id {self.record_id} of table {self.table_name}, which should be unique. It fetches more than 1 record though!" ) return article_objects[0] # views.py <-- Here we determine which subset of articles to query from the database class RecentUpdatesPagination(APIView): queryset = mymodels.AllArticlesView.objects.all().order_by("-update_datetime") pagination_class = PageNumberPagination def get(self, request, page_number=0): page_size = settings.REST_FRAMEWORK["PAGE_SIZE"] page_start_index: int = page_size * page_number page_end_index = page_start_index + page_size article_references_on_page: QuerySet = self.queryset[page_start_index : page_end_index] articles: List[models.Model] = [article_reference.get_article_object() for article_reference in article_references_on_page] serialized_articles = [self.serialize_article(article) for article in articles] return Response(serialized_articles) def serialize_article(article): #A lot of logic for serialization # create_all_articles_view.sql create view v_all_articles as select 'wikientries_character' AS table_name, id AS record_id, update_datetime, 'wikientries_character' || id AS guid FROM wikientries_character UNION ALL select 'wikientries_creature' AS table_name, id AS record_id, update_datetime, 'wikientries_creature' || id AS guid FROM wikientries_creature UNION ALL select 'wikientries_diaryentry' AS table_name, id AS record_id, update_datetime, 'wikientries_diaryentry' || id AS guid FROM wikientries_diaryentry UNION ALL select 'wikientries_encounter' AS table_name, id AS record_id, update_datetime, 'wikientries_encounter' || id AS guid FROM wikientries_encounter UNION ALL select 'wikientries_item' AS table_name, id AS record_id, update_datetime, 'wikientries_item' || id AS guid FROM wikientries_item UNION ALL select 'wikientries_location' AS table_name, id AS record_id, update_datetime, 'wikientries_location' || id AS guid FROM wikientries_location UNION ALL select 'map_map' AS table_name, id AS record_id, update_datetime, 'map_map' || id AS guid FROM map_map UNION ALL select 'wikientries_organization' AS table_name, id AS record_id, update_datetime, 'wikientries_organization' || id AS guid FROM wikientries_organization UNION ALL select 'wikientries_quest' AS table_name, id AS record_id, update_datetime, 'wikientries_quest' || id AS guid FROM wikientries_quest UNION ALL select 'wikientries_rules' AS table_name, id AS record_id, update_datetime, 'wikientries_rules' || id AS guid FROM wikientries_rules UNION ALL select 'fileserver_sessionaudio' AS table_name, id AS record_id, update_datetime, 'fileserver_sessionaudio' || id AS guid FROM fileserver_sessionaudio UNION ALL select 'wikientries_spell' AS table_name, id AS record_id, update_datetime, 'wikientries_spell' || id AS guid FROM wikientries_spell;

#models.py class ErroneousViewException(Exception): """Custom Error that is raised when the view V_ALL_ARTICLES turns out to contain incorrect information""" def __init__(self, faulty_record_id: int, table: str, message: str) -> None: self.faulty_record_id = faulty_record_id, self.table = table self.message = message super().__init__(message) class AllArticlesView(models.Model): table_name = models.CharField(max_length=100, help_text="The name of the table that the record belongs to") record_id = models.PositiveIntegerField(help_text="The id of a record in the table with the given name") update_datetime = models.DateTimeField(db_index=True, help_text="The time the referenced record was last updated") guid = models.CharField(max_length=100, primary_key=True) class Meta: managed = False unique_together = ('table_name', 'record_id') db_table = "v_all_articles" def get_record_model(self) -> models.Model: (app_name, model_name) = self.table_name.split("_") return apps.get_model(app_name, model_name) def get_article_object(self) -> models.Model: article_model: models.Model = self.get_record_model() article_objects = article_model.objects.filter(pk=self.record_id)[0] if len(article_objects) == 0: raise ErroneousViewException( self.record_id, self.table_name, message=f"The view V_ALL_ARTICLES stored the record_id {self.record_id} of table {self.table_name} but no such record exists!" ) if len(article_objects) > 1: raise ErroneousViewException( self.record_id, self.table_name, message=f"The view V_ALL_ARTICLES stored the record_id {self.record_id} of table {self.table_name}, which should be unique. It fetches more than 1 record though!" ) return article_objects[0] # create_all_articles_view.sql create view v_all_articles as select 'wikientries_character' AS table_name, id AS record_id, update_datetime, 'wikientries_character' || id AS guid FROM wikientries_character UNION select 'wikientries_creature' AS table_name, id AS record_id, update_datetime, 'wikientries_creature' || id AS guid FROM wikientries_creature UNION select 'wikientries_diaryentry' AS table_name, id AS record_id, update_datetime, 'wikientries_diaryentry' || id AS guid FROM wikientries_diaryentry UNION select 'wikientries_encounter' AS table_name, id AS record_id, update_datetime, 'wikientries_encounter' || id AS guid FROM wikientries_encounter UNION select 'wikientries_item' AS table_name, id AS record_id, update_datetime, 'wikientries_item' || id AS guid FROM wikientries_item UNION select 'wikientries_location' AS table_name, id AS record_id, update_datetime, 'wikientries_location' || id AS guid FROM wikientries_location UNION select 'map_map' AS table_name, id AS record_id, update_datetime, 'map_map' || id AS guid FROM map_map UNION select 'wikientries_organization' AS table_name, id AS record_id, update_datetime, 'wikientries_organization' || id AS guid FROM wikientries_organization UNION select 'wikientries_quest' AS table_name, id AS record_id, update_datetime, 'wikientries_quest' || id AS guid FROM wikientries_quest UNION select 'wikientries_rules' AS table_name, id AS record_id, update_datetime, 'wikientries_rules' || id AS guid FROM wikientries_rules UNION select 'fileserver_sessionaudio' AS table_name, id AS record_id, update_datetime, 'fileserver_sessionaudio' || id AS guid FROM fileserver_sessionaudio UNION select 'wikientries_spell' AS table_name, id AS record_id, update_datetime, 'wikientries_spell' || id AS guid FROM wikientries_spell;

#mymodels.py class ErroneousViewException(Exception): """Custom Error that is raised when the view V_ALL_ARTICLES turns out to contain incorrect information""" def __init__(self, faulty_record_id: int, table: str, message: str) -> None: self.faulty_record_id = faulty_record_id, self.table = table self.message = message super().__init__(message) class AllArticlesView(models.Model): table_name = models.CharField(max_length=100, help_text="The name of the table that the record belongs to") record_id = models.PositiveIntegerField(help_text="The id of a record in the table with the given name") update_datetime = models.DateTimeField(db_index=True, help_text="The time the referenced record was last updated") guid = models.CharField(max_length=100, primary_key=True) class Meta: managed = False unique_together = ('table_name', 'record_id') db_table = "v_all_articles" def get_record_model(self) -> models.Model: (app_name, model_name) = self.table_name.split("_") return apps.get_model(app_name, model_name) def get_article_object(self) -> models.Model: article_model: models.Model = self.get_record_model() article_objects = article_model.objects.filter(pk=self.record_id)[0] if len(article_objects) == 0: raise ErroneousViewException( self.record_id, self.table_name, message=f"The view V_ALL_ARTICLES stored the record_id {self.record_id} of table {self.table_name} but no such record exists!" ) if len(article_objects) > 1: raise ErroneousViewException( self.record_id, self.table_name, message=f"The view V_ALL_ARTICLES stored the record_id {self.record_id} of table {self.table_name}, which should be unique. It fetches more than 1 record though!" ) return article_objects[0] # views.py <-- Here we determine which subset of articles to query from the database class RecentUpdatesPagination(APIView): queryset = mymodels.AllArticlesView.objects.all().order_by("-update_datetime") pagination_class = PageNumberPagination def get(self, request, page_number=0): page_size = settings.REST_FRAMEWORK["PAGE_SIZE"] page_start_index: int = page_size * page_number page_end_index = page_start_index + page_size article_references_on_page: QuerySet = self.queryset[page_start_index : page_end_index] articles: List[models.Model] = [article_reference.get_article_object() for article_reference in article_references_on_page] serialized_articles = [self.serialize_article(article) for article in articles] return Response(serialized_articles) def serialize_article(article): #A lot of logic for serialization # create_all_articles_view.sql create view v_all_articles as select 'wikientries_character' AS table_name, id AS record_id, update_datetime, 'wikientries_character' || id AS guid FROM wikientries_character UNION ALL select 'wikientries_creature' AS table_name, id AS record_id, update_datetime, 'wikientries_creature' || id AS guid FROM wikientries_creature UNION ALL select 'wikientries_diaryentry' AS table_name, id AS record_id, update_datetime, 'wikientries_diaryentry' || id AS guid FROM wikientries_diaryentry UNION ALL select 'wikientries_encounter' AS table_name, id AS record_id, update_datetime, 'wikientries_encounter' || id AS guid FROM wikientries_encounter UNION ALL select 'wikientries_item' AS table_name, id AS record_id, update_datetime, 'wikientries_item' || id AS guid FROM wikientries_item UNION ALL select 'wikientries_location' AS table_name, id AS record_id, update_datetime, 'wikientries_location' || id AS guid FROM wikientries_location UNION ALL select 'map_map' AS table_name, id AS record_id, update_datetime, 'map_map' || id AS guid FROM map_map UNION ALL select 'wikientries_organization' AS table_name, id AS record_id, update_datetime, 'wikientries_organization' || id AS guid FROM wikientries_organization UNION ALL select 'wikientries_quest' AS table_name, id AS record_id, update_datetime, 'wikientries_quest' || id AS guid FROM wikientries_quest UNION ALL select 'wikientries_rules' AS table_name, id AS record_id, update_datetime, 'wikientries_rules' || id AS guid FROM wikientries_rules UNION ALL select 'fileserver_sessionaudio' AS table_name, id AS record_id, update_datetime, 'fileserver_sessionaudio' || id AS guid FROM fileserver_sessionaudio UNION ALL select 'wikientries_spell' AS table_name, id AS record_id, update_datetime, 'wikientries_spell' || id AS guid FROM wikientries_spell;

Source Link

answered Aug 14, 2021 at 17:49

Philipp Doerner

The answer provided by @Ewan is the one I have accepted, as at a larger scale it seems to me that it is the correct one. It models the data in the database in the way you'd also think about it, even though that makes handling it somewhat more complicated.

However, @Hans-Martin Mosner's approach also is very valid and in my case a lot less work to implement. As such, I went with that, created a view, put a Django model on top of it and created some helper functions that allowed me to fetch the original records via the ORM fairly easily.

Here my excerpt from Django on how I implemented this for future readers:

#models.py class ErroneousViewException(Exception): """Custom Error that is raised when the view V_ALL_ARTICLES turns out to contain incorrect information""" def __init__(self, faulty_record_id: int, table: str, message: str) -> None: self.faulty_record_id = faulty_record_id, self.table = table self.message = message super().__init__(message) class AllArticlesView(models.Model): table_name = models.CharField(max_length=100, help_text="The name of the table that the record belongs to") record_id = models.PositiveIntegerField(help_text="The id of a record in the table with the given name") update_datetime = models.DateTimeField(db_index=True, help_text="The time the referenced record was last updated") guid = models.CharField(max_length=100, primary_key=True) class Meta: managed = False unique_together = ('table_name', 'record_id') db_table = "v_all_articles" def get_record_model(self) -> models.Model: (app_name, model_name) = self.table_name.split("_") return apps.get_model(app_name, model_name) def get_article_object(self) -> models.Model: article_model: models.Model = self.get_record_model() article_objects = article_model.objects.filter(pk=self.record_id)[0] if len(article_objects) == 0: raise ErroneousViewException( self.record_id, self.table_name, message=f"The view V_ALL_ARTICLES stored the record_id {self.record_id} of table {self.table_name} but no such record exists!" ) if len(article_objects) > 1: raise ErroneousViewException( self.record_id, self.table_name, message=f"The view V_ALL_ARTICLES stored the record_id {self.record_id} of table {self.table_name}, which should be unique. It fetches more than 1 record though!" ) return article_objects[0] # create_all_articles_view.sql create view v_all_articles as select 'wikientries_character' AS table_name, id AS record_id, update_datetime, 'wikientries_character' || id AS guid FROM wikientries_character UNION select 'wikientries_creature' AS table_name, id AS record_id, update_datetime, 'wikientries_creature' || id AS guid FROM wikientries_creature UNION select 'wikientries_diaryentry' AS table_name, id AS record_id, update_datetime, 'wikientries_diaryentry' || id AS guid FROM wikientries_diaryentry UNION select 'wikientries_encounter' AS table_name, id AS record_id, update_datetime, 'wikientries_encounter' || id AS guid FROM wikientries_encounter UNION select 'wikientries_item' AS table_name, id AS record_id, update_datetime, 'wikientries_item' || id AS guid FROM wikientries_item UNION select 'wikientries_location' AS table_name, id AS record_id, update_datetime, 'wikientries_location' || id AS guid FROM wikientries_location UNION select 'map_map' AS table_name, id AS record_id, update_datetime, 'map_map' || id AS guid FROM map_map UNION select 'wikientries_organization' AS table_name, id AS record_id, update_datetime, 'wikientries_organization' || id AS guid FROM wikientries_organization UNION select 'wikientries_quest' AS table_name, id AS record_id, update_datetime, 'wikientries_quest' || id AS guid FROM wikientries_quest UNION select 'wikientries_rules' AS table_name, id AS record_id, update_datetime, 'wikientries_rules' || id AS guid FROM wikientries_rules UNION select 'fileserver_sessionaudio' AS table_name, id AS record_id, update_datetime, 'fileserver_sessionaudio' || id AS guid FROM fileserver_sessionaudio UNION select 'wikientries_spell' AS table_name, id AS record_id, update_datetime, 'wikientries_spell' || id AS guid FROM wikientries_spell;