> SELECT t
> FROM (
> SELECT thing.thing_id AS t, COUNT
> (thing.thing_id) AS c
> FROM (
> SELECT thing_id
> FROM myapp_thing_sources
> WHERE source_id = ...
> ) AS thing, myapp_thing_sources
> WHERE thing.thing_id = myapp_thing_sources.thing_id
> GROUP BY thing.thing_id
> )
> WHERE c = 1;
Just as a SQL aside, this pattern
SELECT <fields>
FROM (
SELECT <fields>, count(*) AS c
FROM <from clause>
WHERE <where clause>
GROUP BY <fields>
)
WHERE c = 1
looks like the place to use the HAVING clause:
SELECT <fields>
FROM <from clause>
WHERE <where clause>
GROUP BY <fields>
HAVING Count(*) = 1
(the HAVING clause allows you to do "WHERE"-like filtering on
aggregate results). I don't know if this impacts matters at all,
but it might make the query a little more readable. It might even
speed up the query as the underlying intent is explicit so the
SQL parser may be able to optimize it better.
I'm also confused by your query...it looks like you're pulling in
two instances of the same myapp_thing_sources table and then
doing a one-to-one join with itself. That leads me to believe
that the whole orginal convoluted snarl of SQL can be rewritten
more cleanly as
.extra(where="""
myapp_whatever.id IN (
SELECT mts.thing_id
FROM myapp_thing_sources mts
WHERE mts.source_id = %s
GROUP BY mts.thing_id
HAVING Count(*) = 1
)
""", params=[source.id])
> .extra(where=["""
> id IN (
> SELECT t
> FROM (
> SELECT thing.thing_id AS t, COUNT
> (thing.thing_id) AS c
> FROM (
> SELECT thing_id
> FROM myapp_thing_sources
> WHERE source_id = %s
> ) AS thing, myapp_thing_sources
> WHERE thing.thing_id = myapp_thing_sources.thing_id
> GROUP BY thing.thing_id
> )
> WHERE c = 1
> )
> """], params=[source.id]
> )
>
> but that doesn't work either (even though the raw SQL does,
> even with the WHERE id IN (...). )
It looks like it should be fairly kosher, though you might want
to explicitly give the id to make sure that you've got the ID you
want...something like
myapp_whatever.id IN (...)
However, normally I would expect if more than one id column was
found in your query that you'd be getting errors from the SQL
engine telling you something like "more than one field called
'id'...please qualify it with the tablename".
Whatever.objects.extra(...)._get_sql_clause()
returned and included it for diagnostic purposes (possibly mildly
redacting the data if needed, but the structure should be
unchanged). Fortunately, it's easy to dump the stuff of interest
to a file:
f = file('xxxx.txt', 'w')
f.write(repr(Whatever.objects.extra(...)._get_sql_clause()))
f.write('\n')
f.close()
> but that doesn't work either (even though the raw SQL does,
> even with the WHERE id IN (...). )
It would help if you made a note of what "that doesn't work
either" means. Perhaps the actual error message or perhaps the
"wrong" resulting output along with the expected output.
I also presume you're not shifting DB engines between runs...I
know older MySQL installations were braindead in the sub-select
department. However, if you're using sqlite across the board, you
should be fine.
-tim
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
