query altered by count()

The outline is a follows: I want to join three relations unary o
nullary o squared, where squared = binary^2, and find out which
elements n are in relation n (unary o nullary o squared) fixed to some
nullary element fixed.

Here are the details:

from sqlalchemy import *
from sqlalchemy.orm import *

metadata = MetaData("...")
metadata.bind.echo=True

nullary = Table("nullary", metadata,
               Column("id", Integer, primary_key=True),
               Column("data", String))

nullary.create()

nullary.insert().execute([1}, {"data": 2}]({"data":))

class Nullary(object):
   pass

mapper(Nullary, nullary)

unary = Table("unary", metadata,
             Column("id", Integer, primary_key=True),
             Column("fk", Integer, ForeignKey("nullary.id")))

unary.create()

unary.insert().execute([1}, {"fk": 2}]({"fk":))

class Unary(object):
   pass

mapper(Unary, unary,
      properties={"ref": relation(Nullary, uselist=False,
backref="inv")})

binary = Table("binary", metadata,
              Column("id", Integer, primary_key=True),
              Column("fk1", Integer, ForeignKey("nullary.id")),
              Column("fk2", Integer, ForeignKey("nullary.id")))

binary.create()

binary.insert().execute([1, "fk2": 1}, {"fk1": 2, "fk2": 2}]({"fk1":))

class Binary(object):
   pass

mapper(Binary, binary,
      properties={"ref1": relation(Nullary, uselist=False,
                                   primaryjoin=binary.c.fk1 ==
nullary.c.id),
                  "ref2": relation(Nullary, uselist=False,
                                   primaryjoin=binary.c.fk2 ==
nullary.c.id)})

binary_alias = binary.alias("binary_alias")

squared = select([                binary_alias.c.fk2.label("fk2"),
                 func.count(text("*")).label("n")](binary.c.fk1.label("fk1"),
),
                True,
                [binary_alias,
                      binary.c.fk2 == binary_alias.c.fk1)](join(binary,))
squared = squared.group_by(binary.c.fk1,
binary_alias.c.fk2).alias("squared")

class Squared(object):
   pass

mapper(Squared, squared, primary_key=(squared.c.fk1, squared.c.fk2),
                        properties={"ref1": relation(Nullary),
                                    "ref2": relation(Nullary)})

session = create_session()

fixed = session.query(Nullary).get(1)

query = session.query(Squared).filter_by(ref2=fixed)
query = query.add_entity(Nullary).join("ref1")
count = query.count()
query = query.add_entity(Unary).join("inv", from_joinpoint=True)
query = query.order_by([squared.c.n](squared.c.n))
print [for s, n, u in query](n)
Comments (2)
