Getting closer. Maybe something like this: q1 = session.query(Document).join(Document.tags).filter(Tag.tag=='my document') q2 = session.query(Document).join(Document.tags).filter(Tag.tag=='source code') q3 = q1.intersect(q2) q4 = session.query(Document).filter(Document.title=='Source Code') print q4.intersect(q3).all()
This works (from my initial testing) but I need to make it dynamic so it can handle 1..n tags. On Mar 19, 10:47 am, Stodge <[email protected]> wrote: > Thanks. That doesn't quite work. Based on my data, the following > should (and does) work because it only returns document id=1, which > only has these two tags: > > tag_list = ['my document', 'source code'] > session.query(Document).\ > filter(Document.tags.any(Tag.tag.in_([t for t in tag_list]))).\ > filter(~Document.tags.any(~Tag.tag.in_([t for t in tag_list]))) > > The following should return no records, as there is no document that > has only these tags. Instead it returns document id=2, which only has > the tag 'random stuff': > > tag_list = ['my document', 'source code', 'random stuff'] > session.query(Document).\ > filter(Document.tags.any(Tag.tag.in_([t for t in tag_list]))).\ > filter(~Document.tags.any(~Tag.tag.in_([t for t in tag_list]))) > > On Mar 19, 10:15 am, Michael Bayer <[email protected]> wrote: > > > select document.* from document join tags on document.id=tags.document_id > > where tags.tag='foo' and tags.tag='bar' and tags.tag=.... > > > am I missing something ? that would return no rows in most cases. > > > if you want to find documents that have an exact list of tags, you'd have > > to do something like the IN query we started with, and additionally ensure > > no extra tags remain. > > > like: > > > sess.query(Document).\ > > filter(Document.tags.any(Tag.id.in_([t.id for t in > > tag_list])).\ > > filter(~Document.tags.any(~Tag.id.in_([t.id for t in > > tag_list])) > > > On Mar 19, 2010, at 8:31 AM, Stodge wrote: > > > > Now we're getting somewhere: > > > > expressions = [] > > > for tag in tag_list: > > > expressions += [Tag.tag==tag] > > > documents = > > > session.query(Document).join(Document.tags).filter(and_(*expressions)) > > > > Thanks to a Storm example I found. :) > > > > On Mar 19, 8:12 am, Stodge <[email protected]> wrote: > > >> Ok so far I have this: > > > >> expressions = [] > > >> for tag in tag_list: > > >> expressions += session.query(Document).filter(Tag.tag==tag) > > >> documents = > > >> session.query(Document).join(Document.tags).filter(and_(*expressions)) > > > >> Doesn't work but it's progress! :) > > > >> On Mar 18, 2:37 pm, Stodge <[email protected]> wrote: > > > >>> Thanks that worked beautifully. > > > >>> On a similar note, how would I match documents with only the tags that > > >>> I specify in the list? My naive attempt is: > > > >>> for tag in tag_list: > > >>> session.query(Document).join(Document.tags).filter_by(tag=tag) > > > >>> But that doesn't work. > > > >>> On Mar 15, 10:54 am, "Michael Bayer" <[email protected]> wrote: > > > >>>> Stodgewrote: > > >>>>> I have two classes with a third table: > > > >>>>> document_tags = Table('document_tags', metadata, > > >>>>> Column('document_id', Integer, ForeignKey('documents.id')), > > >>>>> Column('tag_id', Integer, ForeignKey('tags.id')) > > >>>>> ) > > > >>>>> class Document(Base): > > >>>>> __tablename__ = 'documents' > > > >>>>> id = Column(Integer, primary_key=True) > > >>>>> title = Column(String) > > >>>>> filename = Column(String) > > >>>>> tags = relation('Tag', secondary=document_tags, backref='tags') > > > >>>>> def __init__(self, title, filename): > > >>>>> self.title = title > > >>>>> self.filename = filename > > > >>>>> class Tag(Base): > > >>>>> __tablename__ = 'tags' > > > >>>>> id = Column(Integer, primary_key=True) > > >>>>> tag = Column(String) > > > >>>>> def __init__(self, tag): > > >>>>> self.tag = tag > > > >>>>> I want to find all documents with tags in a given list of tags: > > > >>>>> documents = > > >>>>> session.query(Document).filter(Document.tags.in_(tag_list)) > > > >>>>> except I get the familiar message that the "in_()" operator is not > > >>>>> currently implemented for many-to-one-relations. > > > >>>>> I've searched and found some alternatives but I can't get any to work. > > >>>>> Is there an easy example that will make this work? Thanks > > > >>>> if the error message says "many-to-one" then that's a bug. Your > > >>>> relation > > >>>> is many-to-many. > > > >>>> in this case the syntactically easiest method is to use any(). > > >>>> Document.tags.any(Tag.id.in_([t.id for t in tag_list])). > > > >>>> A join could be more performant, which would be: > > > >>>> query.join(Document.tags).filter(Tag.id.in_([t.id for t in tag_list])) > > > >>>>> -- > > >>>>> 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. > > > > -- > > > 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 > > > athttp://groups.google.com/group/sqlalchemy?hl=en. > > -- 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.
