I have an Angular + Django web application in which Django functions as my backend.
In this web application, I have 12 different tables about articles, each table representing a different kind of article, which means they contain vastly different columns at times. They all have the fields
update_datetime but are otherwise very dissimilar.
//SQLite DDL for character table CREATE TABLE "wikientries_character" ( "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "player_character" bool NOT NULL, "alive" bool NOT NULL, "name" varchar(200) NOT NULL UNIQUE, "gender" varchar(10) NOT NULL, "race" varchar(50) NOT NULL, "title" varchar(200) NULL, "description" text NULL, "is_secret" bool NOT NULL, "current_location_id" integer NULL REFERENCES "wikientries_location" ("id") DEFERRABLE INITIALLY DEFERRED, "organization_id" integer NULL REFERENCES "wikientries_organization" ("id") DEFERRABLE INITIALLY DEFERRED, "creation_datetime" datetime NOT NULL, "update_datetime" datetime NOT NULL ) //SQLite DDL for location Table CREATE TABLE "wikientries_location" ( "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "name" varchar(200) NOT NULL, "description" text NULL, "is_secret" bool NOT NULL, "creation_datetime" datetime NOT NULL, "update_datetime" datetime NOT NULL, "parent_location_id" integer NULL REFERENCES "wikientries_location" ("id") DEFERRABLE INITIALLY DEFERRED )
I want to fetch the X most recently updated articles across all 12 tables, so the top X entries if ordered by
The naive approach to doing this is fetching all entries across all 12 tables (which is at least 12 queries) into your application server, putting them all in a massive list/array, sorting by
update_datetime and then taking the top X entries. This is what I have implemented currently (I have a small database at the moment), but I don't think that is viable for larger datasets because it requires fetching a lot of data from the database multiple times.
The only other approach I can think of, is the following:
- Create new table
Recently Updated Articlesthat references an article across the 12 tables. To do so it would have the columns "table_name" (varchar of the table the record is in) and "pk" (the primary key of the specific record on the table). It shall also have the column "update_date" which is the value
Recently Updated Articlesshall never be manipulated directly by any view, only read from
- Implement triggers on my application server (Django Signals) that update
Recently Updated Articleswhenever a Create, Delete or Update operation happens on one of my 12 tables.
I could then get references to the X most recently updated articles by just querying
Recently Updated Articles and then fetching content of those referenced articles from my 12 tables.
What's stopping me from doing this is that it seems... ugly. But I'm also not quite seeing a better way of doing this. Is there a better solution for this kind of problem?