Performing a complex self-referencing request using Django ORM

Question

I have the following model:

class Message(Model): url = URLField("URL") email = EmailField("E-Mail") contacted = BooleanField("Contacted", default=False) 

With examples of data such as:

 | url | email | contacted | +-----+-----------------+-----------+ | foo | [e-mail] | N | | bar | [e-mail] | N | | baz | [e-mail] | Y | 

I would like to select all individual lines (by email address) whose email addresses have never been linked. In this sample data, the string [e-mail] will be returned only.

Answer 1

This will return the required entries:

 not_contacted = Message.objects.exclude( email__in=Message.objects.filter(contacted=True).values('email') ) 

This has the advantage that only one request is executed. Your query will look something like this:

 SELECT messages_message.id, messages_message.url, messages_message.email, messages_message.contacted FROM Messages WHERE NOT (messages_message.email IN ( SELECT U0.email from messages_message U0 WHERE U0.contacted = True ) ) 

Please note that for many, many records this query may not be optimal, but it will probably work for most purposes.

Answer 2

 DROP SCHEMA tmp CASCADE; CREATE SCHEMA tmp ; SET search_path=tmp; CREATE TABLE massage ( zurl varchar NOT NULL , zemail varchar NOT NULL , contacted boolean ); INSERT into massage(zurl, zemail, contacted) VALUES ( 'foo', ' [e-mail] ', False) ,( 'bar', ' [e-mail] ', False) ,( 'baz', ' [e-mail] ', True) ; SELECT DISTINCT zemail AS zemail , MIN(zurl) AS zurl FROM massage m WHERE NOT EXISTS ( SELECT * FROM massage nx WHERE nx.zemail = m.zemail AND nx.contacted = True ) GROUP BY zemail; 

If there are several entries for a given email address, then the above selects the one that has the “lowest” URL. If you want them all, the query will be even simpler:

 SELECT m.zurl, m.zemail FROM massage m WHERE NOT EXISTS ( SELECT * FROM massage nx WHERE nx.zemail = m.zemail AND nx.contacted = True ) ;