What is the best way to select data from multiple models?
user = models.ForeignKey(User, null=True, on_delete=models.SET_NULL, related_name="image")
user = models.ForeignKey(User, null=True, on_delete=models.SET_NULL, related_name="phone_number")
user = models.ForeignKey(User, null=True, on_delete=models.SET_NULL, related_name="address")
If you have 30 tables with foreign key to User table, it will be hard to write 30 select_related queries as User is not the model that holds the foreign key. Reason being it’s a one-to-many relationship, 1 user can have multiple feature each.
Even writing raw SQL query is hard with so many models.
What is the cleanest method to use? And what is the most performant method? Taking in consideration of calling them from template.
My first reaction to seeing a structure like this is to recommend restructuring / renormalizing the database. (There’s not enough detail provided about all these different models, or the scaling issues, or the quantity / frequency of uses of these tables, or the degree of overlap, or a handful of other issues to make that a firm suggestion.)
This isn’t the way to achieve those desired results.
Don’t do this in the templates. Let the views organize / retrieve the data, allowing the templates to focus solely on how it’s to be displayed.
This use case is specifically if user goes to their account/profile page, by right this DetailView should call the data from all 30 tables. But I think it maybe database heavy if user has multiple images, phone numbers, addresses etc.
Each model has no data redundancy, the only common thing will be the foreignkey. That’s the reason I have so many models in the 1st place
For this point, what I have in mind is User's DetailViewget_context_data method. I can do something like this
I think I’d still be looking at rearchitecting the data structures.
I disagree. You’re looking at them all being different in terms of usage. I’m looking at redundancy in terms of data types.
At the database layer, most of that data (aside from the images) is Character / Text data. The only differences among them is how that data is logically interpreted at the application layer.
Again, there’s not nearly enough detail presented here to get really specific with recommendations, but I’d probably be looking at some smaller set of tables, possibly incorporating some mix of proxy models and multi-table inheritance.
(Of course, the overall size of the tables are part of the decisions involved here. If you’ve only got 1000 users, it probably doesn’t matter how you handle this.)