Hi Michael et al, I can not find a way to express my query in SQLAlchemy. What I want to do is to load all ORM-mapped objects for which another query can not yield any result (not exists):
select * from entry where not exists (select 1 from lookup where lookup.skipped_id = entry.id) For my application, the subquery is a bit more complicated (it's a join over two tables). In any case, I can not correlate the ORM query with the sql query. The attached example prints the following query (SQLAlchemy 0.6.8): SELECT entry.id AS entry_id, entry.content AS entry_content FROM entry WHERE NOT (EXISTS (SELECT 1 FROM lookup)) How can I correlate the subquery in this context? There is a bunch of correlate methods (Query.correlate, Select.correlate) but I do not really understand how to make use of it here. Do you have a hint for me? Greetings, Torsten -- DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH Torsten Landschoff Office Dresden Tel: +49-(0)351-4519587 Fax: +49-(0)351-4519561 mailto:torsten.landsch...@dynamore.de http://www.dynamore.de Registration court: Mannheim, HRB: 109659, based in Karlsruhe, Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
from sqlalchemy import * from sqlalchemy.orm import * metadata = MetaData() entry_table = Table("entry", metadata, Column("id", Integer, primary_key=True), Column("content", String) ) lookup_table = Table("lookup", metadata, Column("skipped_id", Integer, ForeignKey(entry_table.c.id))) class Entry(object): pass mapper(Entry, entry_table) engine = create_engine("sqlite:///", echo=True) metadata.create_all(engine) Session = sessionmaker(engine) session = Session() for v in (1, 3, 7, 9): session.execute(lookup_table.insert().values(skipped_id=v)) session.query(Entry).filter( not_(exists("1", from_obj=lookup_table) .correlate(entry_table))).all()