Re: [sqlalchemy] mapping objects from a select containing a "group by"

[Michael Bayer]

any select() that you call alias() on, you can map to.  in fact the example in 
the docs actually has a group_by():

http://docs.sqlalchemy.org/en/rel_0_8/orm/mapper_config.html#mapping-a-class-against-arbitrary-selects


but I already wrote you another example before even checking that:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class A(Base):
    __tablename__ = 'a'

    id = Column(Integer, primary_key=True)
    bs = relationship("B")


class B(Base):
    __tablename__ = 'b'

    id = Column(Integer, primary_key=True)
    a_id = Column(Integer, ForeignKey('a.id'))

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

s = Session(e)
s.add_all([
    A(id=1, bs=[B(), B(), B()]),
    A(id=2, bs=[B(), B()]),
    A(id=3, bs=[B(), B(), B(), B()]),
    A(id=4, bs=[B()]),
])
s.commit()

grouped = select([func.count(B.id).label('count'), 
B.a_id]).group_by(B.a_id).alias()
class BCounts(Base):
    __table__ = grouped
    __mapper_args__ = {"primary_key": grouped.c.a_id}


for bc in s.query(BCounts).filter(BCounts.count < 4):
    print bc.a_id, bc.count



On Jun 20, 2013, at 2:20 PM, Chris Withers <ch...@simplistix.co.uk> wrote:

> Hi All,
> 
> I'd like to map an object to the result of doing a select containing a group 
> by clause. Where can I find out how to do that?
> 
> Initially, I wouldn't expect any modifications to this object to be possible, 
> or if they were, to be persisted, although it would be cool if, eventually, I 
> could get it such that modifications to attributes that were common across 
> all the rows were persisted back.
> 
> cheers,
> 
> Chris
> 
> -- 
> Simplistix - Content Management, Batch Processing & Python Consulting
>            - http://www.simplistix.co.uk
> 
> -- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
> 
> 

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.