Take for example, a slightly complex SQL statement. Really, just joining an aggregate count from a couple of tables to another few tables: in short, it requires a simple subselect. There's no way the fairly pathetic Django ORM will do this. So there are some choices to be made.
- Use a database view
- Django however provides no support for creating views with syncdb
- South, the essential third party tool for DB migration because apparently Django devs don't think it's essential enough to include, also has no support for views
- One has to create a "dummy" (non-managed) model just to run the ORM against the view
- Use a "raw" SQL query
- One loses most of the ORM's conveniences
- If one needs to make the SQL dynamic (such as for sorting or filtering), one finds oneself basically rebuilding a mini-ORM or at least SQL generator for oneself, and eventually question why bother with django's ORM at all... since it's mostly only good for CRUD, which is not a difficult problem.
- Then there's the issue of where to do the raw query. Embedded raw SQL in the view? Create Manager object and patch it into the model just to do one query? Apparently this latter is the suggested method, for the sake of code separation. It's pretty ugly no matter how you do it.
- Have to explicitely name tables in query -- potential refactoring headaches.
- Abuse the .extra() method to hack in a subselect
- This used to be possible, but no longer is possible due to all the "select" elements being quoted.
- One can monkeypatch the quote routine not to do quotes, but... monkeypatch!
- Do the join in software
- Now you require extra database hits for extra queries
- Possible complications if the dataset is large and needs filtering
- The data join can not be done at the template level (pass in data and dictionary to join) because django templates do not support looking up dynamic values (ie. a dynamic dictionary key held in another variable)
- This problem can be solved by creating a custom filter which takes the variable as an argument and does the lookup for you; but now you have to manually include your registered filter in every template you use it in. Really?? This basic functionality of a basic python lookup isn't included?
- Unholy hack of ORM generated SQL (in order to take advantage of programatic filter/sort), with the subselect crudely hacked in, and then run through as a "raw" query to get the results.
- Actually augment the ORM with some hacks (or sane extensions) that allow a little more flexibility in the SQL generation. The django-orm-extensions should be applauded for attempting a few specific cases of this, but in my quick look at the docs I find the integration provided (SQLExpression, etc), again, extremely awkward.
- Use Aldjemy to integrate Alchemy for robust SQL query building purposes. Aldjemy looks like a really convenient hack to get SQLAlchemy support into Django... but do I really want more dependencies and two different ORM syntaxes in one application? (There is also an older django-sabridge project along these lines.)
It's all kind of disheartening and crazy to have to even think about this stuff. It makes one believe that most people don't do anything serious with django beyond glorified key-value lookups. Or djangos are masochistic contortionists. But I may be in a bit of a negative frame of mind just now.
No comments:
Post a Comment