have select_from() accept entities

[Former user]

Let's take the following mapping into consideration:

import sqlalchemy
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.sql import func
engine = create_engine('sqlite://')
metadata = MetaData()
metadata.bind = engine

Session = scoped_session(sessionmaker(bind = engine, autoflush = True,
autocommit = False))

# TABLES

owners_table = Table('owners', metadata, 
    Column('owner_id', Integer, primary_key=True)
)

dogs_table = Table('dogs', metadata, 
    Column('dog_id', Integer, primary_key=True),
    Column('owner_id', Integer, ForeignKey(owners_table.columns.owner_id))
)

metadata.create_all()

engine.execute(owners_table.insert(), [{'owner_id':1}]({'owner_id':1}))
engine.execute(owners_table.insert(), [{'owner_id':2}]({'owner_id':2}))
engine.execute(owners_table.insert(), [{'owner_id':3}]({'owner_id':3}))

engine.execute(dogs_table.insert(), ['dog_id':1}]({'owner_id':1,))
engine.execute(dogs_table.insert(), ['dog_id':2}]({'owner_id':1,))
engine.execute(dogs_table.insert(), ['dog_id':3}]({'owner_id':2,))

# POJOS
class Owner(object):
    pass

class Dog(object):
    pass

# MAPPERS
owners2dogs_relation = relation(Dog,
                                backref = "owner")
mapper(Owner, owners_table, properties = {"dogs" : owners2dogs_relation})
mapper(Dog, dogs_table)

s = Session()

Now I want to get the maximum number of dogs for an owner, and I don't care about the corresponding owner. I can do:

owner_id_to_dog_num_sub = \
s.query(Dog.owner_id, func.count('*').label('dogs_count')).\
group_by(Dog.owner_id).subquery()

print select([func.max\
(owner_id_to_dog_num_sub.columns.dogs_count)](func.max\
(owner_id_to_dog_num_sub.columns.dogs_count))\
, from_obj = owner_id_to_dog_num_sub).execute().\
fetchone()[0](0)

But here I select in the subquery the owner_id. If I do just:

owner_id_to_dog_num_sub = \
s.query(func.count('*').label('dogs_count')).\
group_by(Dog.owner_id).subquery()

It doesn't work, because it doesn't understand on which table the query should be performed... I can use the table object itself to perform the query, but I want to use my mapping. How can I do it?

[Former user]

submitted by [e-mail]

[Mike Bayer]

try putting the entity in the select list:

s.query(func.count(Dog.id).label('dogs_count')).\
group_by(Dog.owner_id).subquery()

if you really needed it, the equivalent to from_obj() in query is select_from():

s.query(func.count('*').label('dogs_count')).\
select_from(dogs_table).\
group_by(Dog.owner_id).subquery()

[Former user]

That's cheating. The first solution is cheating, because I wanted to write 'count('*')'. The second one is cheating, because instead of using the mapping, I use a table. I think the right solution would be to allow select_from to get the Dog class.

s.query(func.count('*').label('dogs_count')).\
select_from(Dog).\
group_by(Dog.owner_id).subquery()

[Mike Bayer]

that feature is acceptable. but SQLA standardizes on "what you put in the columns or WHERE clause" as the most straightforward way to select FROM something. this is actually a more verbose methodology you're proposing but I agree an entity should be accepted.

[Mike Bayer]

this works fine in 0.6.

[Mike Bayer]

Removing milestone: 0.6.xx (automated comment)