Django equivalent to a SQL CASE statement?
jrs_66
Any pointers as to where I could find any info about performing a SQL
CASE statement using Django? Even more fundamental... is there really
no way of doing a LEFT OUTER JOIN in Django (other than terribly
sloppy role your own hacks)? Does anyone know of any more complete
model/queryset docs out there, other than the 3 pages Django offers?
Also, am I alone in being a bit wary of the django ORM after
reading...
'By default, a QuerySet will not eliminate duplicate rows. In
practice, this is rarely a problem, because simple queries such as
Blog.objects.all() don't introduce the possibility of duplicate result
rows. However, if your query spans multiple tables...'
Is someone out there really serious in assuming that using DISTINCT is
'rarely a problem' due to most queries not needing to go beyond
'Blog.objects.all()'? Maybe I'm odd, but rarely do I have much use
for a single table query... not the other way around...
Not trying to be negative, but I'm struggling with making the ORM
useful beyond quite simple (almost trivial) queries. The ORM seems to
inevitably produce some seriously sloppy SQL under the covers. Since
these queries are so abstracted and hidden, I could see many nasty
scaling issues hit people in the face. Does SQL Alchemy allow going
beyond the basics? I'm hoping I'm missing some key piece of info, but
losing faith that I am... and yes, I've read the docs MANY times.
Thanks for pointing me towards any useful resources which I may be
missing.
Malcolm Tredinnick
> Hi,
>
> Any pointers as to where I could find any info about performing a SQL
> CASE statement using Django? Even more fundamental... is there really
> no way of doing a LEFT OUTER JOIN in Django (other than terribly
> sloppy role your own hacks)? Does anyone know of any more complete
> model/queryset docs out there, other than the 3 pages Django offers?
Your point of view is a little skewed here -- it's not particularly
useful to think in terms of "how do I turn this SQL into Python".
Django's ORM provides a way to map particular pieces of Python
functionality onto the persistent storage layer, which happens to be (by
default at the moment) SQL databases. The goal isn't to provide every
piece of SQL in an equivalent form. Rather, the goal is to provide a way
to do various things that are useful at the Python level.
So what's the model-related situation where a case-statement is going to
be necessary? I know some exist, but I'm interested in which particular
problem you're trying to solve so that we can help with a solution that
fits well with Django. Right now, there is no situation where Django
creates a "CASE" clause. Maybe in the future, if there's a common-enough
functional situation that requires it, it could be added. Providing some
context here will be useful.
If you know precisely the SQL you want to write, then use SQL to conduct
the query. It's a perfectly fine language for that and it would be
redundant to to duplicate that in Django.
As for left outer joins, Django uses them when it's necessary for the
query and uses inner joins at other times. This is a case where the ORM
does the right thing to implement the Python functionality and the
caller doesn't have to worry about it.
So what is the specific problem you are trying to solve that requires
explicitly specifying an outer join? Once again, details will help us to
help you.
>
> Also, am I alone in being a bit wary of the django ORM after
> reading...
Yes, you are.
>
> 'By default, a QuerySet will not eliminate duplicate rows. In
> practice, this is rarely a problem, because simple queries such as
> Blog.objects.all() don't introduce the possibility of duplicate result
> rows. However, if your query spans multiple tables...'
>
> Is someone out there really serious in assuming that using DISTINCT is
> 'rarely a problem' due to most queries not needing to go beyond
> 'Blog.objects.all()'? Maybe I'm odd, but rarely do I have much use
> for a single table query... not the other way around...
Django behaves the same way as most experienced SQL writers. Using
DISTINCT in a query can be avoided in a very large set of cases. It also
introduces a lot of extra work into the query (since a sort is
required). As SQL is about sets of rows, specifying things so that the
duplicate rows aren't selected in the first place is preferred, rather
than having to eliminate them from the result set.
Single versus multi-table queries has no relevance to your question
here. It's fairly common to have multi table queries created by Django
and duplicate rows are only returned in very rare situations.
What is the specific problem area you are working in that is routinely
returning duplicate entries and where distinct() isn't solving the
problem?
> Not trying to be negative,
And yet you're succeeding without any effort at all!
Could you perhaps dial the attitude back a bit with all the "are you
seriously..." stuff? Suffice it to say that Django is being used for at
least a few thousand quite serious projects in more situations than you
or I can imagine and by some organizations that even you would probably
consider "serious".
> but I'm struggling with making the ORM
> useful beyond quite simple (almost trivial) queries.
Practice makes perfect.
> The ORM seems to
> inevitably produce some seriously sloppy SQL under the covers.
Concrete details are appreciated. I would dispute your claim, by the
way, so what are the models you're using and the querysets you're
constructing?
At the end of the day, if Django isn't meeting your requirements, you
should definitely look at some other options, however, none of the items
you've posted really have enough details to allow us to suggest whether
there are any obvious improvements. However, most of the things you've
mentioned simply aren't issues in the majority situations I'm aware of
(and I've seen and worked with both some very small sites and some huge
ones), so generalisations aren't really appropriate. Asking questions
about specific cases where you can provide a short self-contained code
fragment to show what you're doing will be much more beneficial to all.
Regards,
Malcolm
jrs_66
Any idea where a very experienced SQL coder can find a few details
beyond the three pages of model/queryset documentation? Such as
information on a) SQL CASE statements (or equivalent), b) performing
LEFT JOINs (or equivalent). Would studying the source code be my best
option?
On May 1, 2:03 pm, Malcolm Tredinnick <malc...@pointy-stick.com>
wrote:
Phil Mocek
> On May 1, 2:03 pm, Malcolm Tredinnick wrote:
> > So what's the model-related situation where a case-statement is
> > going to be necessary? I know some exist, but I'm interested in
> > which particular problem you're trying to solve so that we can
> > help with a solution that fits well with Django.
> > So what is the specific problem you are trying to solve that
> > requires explicitly specifying an outer join? Once again,
> > details will help us to help you.
[snip]
> > What is the specific problem area you are working in that is
> > routinely returning duplicate entries and where distinct() isn't
> > solving the problem?
[snip]
> > > The ORM seems to inevitably produce some seriously sloppy SQL
> > > under the covers.
> >
> > Concrete details are appreciated. I would dispute your claim, by
> > the way, so what are the models you're using and the querysets
> > you're constructing?
[snip]
> Thanks! Very helpful!
>
> Any idea where a very experienced SQL coder can find a few details
> beyond the three pages of model/queryset documentation? Such as
> information on a) SQL CASE statements (or equivalent), b)
> performing LEFT JOINs (or equivalent). Would studying the source
> code be my best option?
Malcom generously offered to assist you and posed some followup
questions which you have seemingly ignored. If you're unwilling to
engage in this discussion, you're wasting lots of other people's
time. If you want to get the assistance you seek, it would behoove
you to provide the information that was requested.
Also, although reasonable people disagree over whether top-posting
or bottom-posting is preferable, it's always bad form to mix the
two. You did so in your reply. Additionally, in a conversation
such as this, top-posting as you did tends to result in
information being lost -- as it seems to have been here. If
you're unfamiliar with these concepts, the following information
will surely be of use to you:
* <http://en.wikipedia.org/wiki/Posting_style#Inline_replying>
* <http://catb.org/jargon/html/T/top-post.html>
* <http://www.faqs.org/rfcs/rfc1855.html>
* <http://www.caliburn.nl/topposting.html>
--
Phil Mocek
Malcolm Tredinnick
> Thanks! Very helpful!
>
> Any idea where a very experienced SQL coder can find a few details
> beyond the three pages of model/queryset documentation? Such as
> information on a) SQL CASE statements (or equivalent), b) performing
> LEFT JOINs (or equivalent). Would studying the source code be my best
> option?
There isn't going to be any Django documentation on those things because
they aren't Django concepts. If you want to write SQL, write SQL
(cursor.execute() is your friend). How to "perform" them in Django isn't
really a sensible question as it's not the goal of Django to wrap SQL,
as I've explained. What is the specific problem you're trying to solve
using Python objects that require these? "Doing a CASE query" is not a
specific problem, for example -- it's an attempt at a solution.
As for more documentation, there is lots of stuff around, but you're
still very short on specifics, so I have no idea what might be
appropriate. Typing "django queryset documentation" into Google shows a
bunch of stuff, for example and that's about the most naïve search term
I could think of. More specific searches will no doubt lead to better
results.
Reading the source never hurts, since it's exceedingly well documented.
Whether that would help or not depends on what you're looking to do and
how much time you spend on it, so I can't answer that question.
Again, provide some details about the problem(s) you're trying to solve
and we can help.
Regards,
Malcolm
Malcolm Tredinnick
[...]
> Again, provide some details about the problem(s) you're trying to solve
> and we can help.
Email is always hard to interpret, since it doesn't include body
language. If there's any confusion about the intent of my words: I'm
*not* intending to flame you or trivialise whatever issues you might be
happening (at least in my second reply; your first post had some
"issues"). I *would* like to understand what they are so that I can
provide more guided assistance.
At the moment, I can only see "I want to write this particular SQL" and
that's not a problem Django is designed to solve (except that it
provides you with the extra() queryset call and access to the underlying
database connection). But I suspect that isn't really the problem, hence
my request for details.
Hopefully that removes any inadvertent ambiguity.
Cheers,
Malcolm