How to find all records that share the same field value as some other record?

Question

I need to extract all records which have a field which does NOT have a unique value.

I can't figure out an elegant way to do it - using annotation or some other way. I see a "value_annotate" method to the object manager but it's unclear if it's at all related.

Currently I'm using the inelegant way of simple looping through all values and doing a get on the value, and if there's an exception it means it's not unique..

Thanks

Answer 1

I can't say much about the Django part, but the query would look something like:

SELECT * 
FROM foo
WHERE id IN (
  SELECT MAX(id) 
  FROM foo 
  GROUP BY bar 
  HAVING COUNT(*)=1)

This will return all records where the "bar" field is unique.

Answer 2

I'd go direct to a raw query in this case. This'll look something like the following, assuming you're using Django 1.2:

query = """
SELECT *
FROM table
GROUP BY field
HAVING COUNT(*) > 1
"""
non_uniques = Table.objects.raw(query)

For earlier than 1.2, see the django docs on raw queries