sqlalchemy: referencing label()'d column in a filter or clauselement

Question

I'm trying to perform a query that works across a many->many relation ship between bmarks and tags with a secondary table of bmarks_tags. The query involves several subqueries and I have a need to DISTINCT a column. I later want to join that to another table via the DISTINCT'd ids.

I've tried it a few ways and this seems 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:

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

Does anyone know how I can perform the join across the bmarks_tags.tag_id and the result of the Tag.tid.distinct()?

Thanks

Schema:

# 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 == t.c.tid)
return DBSession.execute(test)

Answer 2

It is hard to tell what you are trying to accomplish, but since you are using orm anyways (and there is not much reason anymore to go with bare selects in sa these days), you should probably start by establishing a many-to-many relation:

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 your query and go from there:

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

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