@hvdklauw Amazing! I knew that my usage of _meta.get_fields() was a bit optimistic, thanks for getting your hands dirty and doing the legwork on relational fields. Much appreciated ✨
(I'll take a closer look at the code in the next few days)
@bmispelon This is so clever! 🎉And a great write up! 👏
I appreciate your "attempt 1", "attempt 2", style write ups in general. It makes me feel like I'm suffering through searching for the solution along with you. 😆
As someone who has also spent his time in the mines translating raw SQL to ORM this would have come in handy so much in the past! Totally yanking this pattern if I ever have the need to do it again.
@bmispelon oh, this is awesome, for years one of the things I do is write a forum and try to optimize the hell out of getting the related information. Django has gotten better and better but this trick might be the final bit needed to do things in a single query without weird triggers/signals to update related tables when needed.
@bmispelon 🤩 this functionality is something that belongs in the ORM ideally. I think a third party library is a good starting point, but I'd vote for it to go straight to main. People frequently want to fetch a list of discussion threads and the latest post of each thread. Your idea is perfect for that scenario!
@CodenameTim Yes, this technique should work well for the "latest comment in thread" scenario. That might even have been a better example to use 😅
To be honest I was surprised how far I was able to get, I kept thinking I would hit some fundamental limitation of the ORM at some point, but I never did.
I don't have any particular insight into the performance implications I'm afraid - we'd need to do some profiling.
I'd love it if we could find a way to avoid needing the JSONObject intermediary though! I never worked on the SubQuery code, so I don't know what's possible there.
@bmispelon@lilyf@CodenameTim OK, missing what you mean there. I need to play, since I don't have all that in RAM 🤔
Thought was exactly as you have it in the post, but with ArrayAgg at the JSONObject step. PG can return an array of fields, as native values, for a single output field. (Like a composite type, but here from a subquery)
I mention the deferring bit at the end of the post. Basically the idea is that if the jsonobject doesn't contain all the model fields, you can still instanciate the model and the missing fields will be deferred (same as if you'd used Django's .defer()/.only()).
@bmispelon maybe 🤔 I shall potter on the project. (Much easier than me half hacking it together myself.) There must be some incantation to get PG to return it like an ad hoc composite type.
Add comment