I'm trying to do this query with SQL Alchemy, but I'm going to give
the example in mixed SQLAlchemy and SQL to keep the posting concise.
I have three tables each with an id field and another field. This
example uses an email, thread, and threading algorithm (shortened to
alg) tables.
CREATE TABLE alg (id INT NOT NULL, name VARCHAR(32));
CREATE TABLE email (id INT NOT NULL, subject VARCHAR(256));
CREATE TABLE thread (id INT NOT NULL, algid INT, emailid INT);
email has a ManyToMany mapping to thread and thread has a ManyToOne
mapping to alg.
I would like to find any emails that do not have a threading record
which is straight forward and results in the following SA and SQL:
query =
model.email.query().outerjoin('thread').filter_by(emailId=None)
SELECT * FROM email LEFT OUTER JOIN thread ON thread.emailid =
email.id LEFT OUTER JOIN alg ON alg.id = thread.algid WHERE
thread.emailid IS NULL;
This is great, I get my list of emails that don't have any threading
records . . . but now I need to know how many emails don't have
threading records for a particular algorithm. . . Do I have to do this
using a temporary table?
Doesn't work:
query =
model.email.query().outerjoin('thread').filter_by(emailId=None).outerjoin('alg').filter_by(name='alg
name')
Thanks for your help and let me know if this isn't clear enough!
-Eric
I'm stumped and could use some basic database help here.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
