Sqlalchemy: reference to label () 'd column in filter or disclaimer

Question

I am trying to execute a query that works through many-> many relationships between bmarks and tags with a secondary bmarks_tags table. The query includes several subqueries, and I need to DISABLE the column. Later, I want to join this table using the DISTINCT'd identifiers.

I tried this in several ways and it seems the closest:

tagid = alias(Tag.tid.distinct()) test = select([bmarks_tags.c.bmark_id], from_obj=[bmarks_tags.join(DBSession.query(tagid.label('tagid'))), bmarks_tags.c.tag_id == tagid]) return DBSession.execute(qry) 

But I get an error message:

 ⇝ AttributeError: '_UnaryExpression' object has no attribute 'named_with_column' 

Does anyone know how I can make the connection through bmarks_tags.tag_id and the result of Tag.tid.distinct ()?

thanks

Scheme:

 # this is the secondary table that ties bmarks to tags bmarks_tags = Table('bmark_tags', Base.metadata, Column('bmark_id', Integer, ForeignKey('bmarks.bid'), primary_key=True), Column('tag_id', Integer, ForeignKey('tags.tid'), primary_key=True) ) class Tag(Base): """Bookmarks can have many many tags""" __tablename__ = "tags" tid = Column(Integer, autoincrement=True, primary_key=True) name = Column(Unicode(255), unique=True) 

Answer 1

Something like this should work:

 t = DBSession.query(Tag.tid.distinct().label('tid')).subquery('t') test = select([bmarks_tags.c.bmark_id], bmarks_tags.c.tag_id == tctid) return DBSession.execute(test) 

Answer 2

It's hard to say what you're trying to accomplish, but since you use orm all the time (and there is no more reason to go with bare choices these days), you should probably start by creating a multi-user mode, to-many:

 bmarks_tags = Table('bmark_tags', Base.metadata, Column('bmark_id', Integer, ForeignKey('bmarks.bid'), primary_key=True), Column('tag_id', Integer, ForeignKey('tags.tid'), primary_key=True) ) class Tag(Base): """Bookmarks can have many many tags""" __tablename__ = "tags" tid = Column(Integer, primary_key=True) name = Column(Unicode(255), unique=True) class BMark(Base): __tablename__ = 'bmarks' bid = Column(Integer, primary_key=True) tags = relation(Tag, secondary=bmarks_tags, backref="bmarks") 

Then get a request and from there:

 query = DBSession.query(BMark).join(BMark.tags) 

If not, give us the actual sql you are trying to emit sqlalchemy.