[sqlalchemy] Re: in_() operator is not currently implemented for many-to-one-relations - alternatives?
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.
[sqlalchemy] Re: in_() operator is not currently implemented for many-to-one-relations - alternatives?
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.
Re: [sqlalchemy] Re: in_() operator is not currently implemented for many-to-one-relations - alternatives?
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.
[sqlalchemy] Re: in_() operator is not currently implemented for many-to-one-relations - alternatives?
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 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.
--
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
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 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.
[sqlalchemy] Re: in_() operator is not currently implemented for many-to-one-relations - alternatives?
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 sto...@gmail.com 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 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.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to
Re: [sqlalchemy] Re: in_() operator is not currently implemented for many-to-one-relations - alternatives?
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))
[sqlalchemy] Re: in_() operator is not currently implemented for many-to-one-relations - alternatives?
Thanks! That worked beautifully. :)
On Mar 19, 11:18 am, Conor conor.edward.da...@gmail.com wrote:
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
[sqlalchemy] Re: in_() operator is not currently implemented for many-to-one-relations - alternatives?
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.
[sqlalchemy] Re: in_() operator is not currently implemented for many-to-one-relations - alternatives?
Thanks. I wrote the error message from memory, the exact wording is:
session.query(Document).filter(Document.tags.in_(tag_list))
Traceback (most recent call last):
File stdin, line 1, in module
File /usr/lib/python2.6/site-packages/sqlalchemy/sql/
expression.py, line 1274, in in_
return self.operate(operators.in_op, other)
File /usr/lib/python2.6/site-packages/sqlalchemy/orm/
attributes.py, line 120, in operate
return op(self.comparator, *other, **kwargs)
File /usr/lib/python2.6/site-packages/sqlalchemy/sql/operators.py,
line 49, in in_op
return a.in_(b)
File /usr/lib/python2.6/site-packages/sqlalchemy/orm/
properties.py, line 479, in in_
raise NotImplementedError(in_() not yet supported for relations.
For a
NotImplementedError: in_() not yet supported for relations. For a
simple many-to-one, use in_() against the set of foreign key values.
Thanks for the suggestions.
On Mar 15, 10:54 am, Michael Bayer mike...@zzzcomputing.com wrote:
Stodge wrote:
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.
