Stodge 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])))
>
>
Try this query instead:
tag_list = ['my document', 'source code', 'random stuff']
q = session.query(Document)
for t in tag_list:
q = q.filter(Document.tags.any(Tag.tag == t))
q = q.filter(~Document.tags.any(~Tag.tag.in_(tag_list)))
Alternatively, if your database supports aggregating row sets into
arrays/strings (e.g. PostgreSQL supports ARRAY(SELECT ...) to collect
row sets into an array, MySQL and others have GROUP_CONCAT() that I
believe you can use for this purpose), you may prefer a different
technique. I will show an example with PostgreSQL and ARRAY().
subq = session.query(Tag.tag)
subq = subq.join(document_tags)
subq = subq.filter(document_tags.c.document_id == Document.id)
subq = subq.order_by(Tag.tag.asc())
subq = subq.correlate(Document)
subq = subq.subquery()
q = session.query(Document)
q = q.filter(func.array(subq) == sorted(tag_list))
q = q.correlate(s) # May not be needed
This should generate SQL like:
SELECT <Document columns>
FROM document
WHERE ARRAY(
SELECT tag.tag
FROM tag
JOIN document_tags ON document_tags.tag_id = tag.id
WHERE document_tags.document_id = document.id
ORDER BY tag.tag ASC
) = %(array_1)s
where array_1 would be ['my document', 'random stuff', 'source code'].
Note how both the subquery and array_1 have to be sorted. I tend to
prefer this type of query since its complexity doesn't grow as you add
more tags in your search criteria.
-Conor
>
> On Mar 19, 10:15 am, Michael Bayer <mike...@zzzcomputing.com> 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 <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.
