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 <sto...@gmail.com> 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 <sto...@gmail.com> 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" <mike...@zzzcomputing.com> 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 sqlalch...@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.
>>
>>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@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.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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.
