can you express the exact query you want in SQL? I can translate to that
easily if you have it. Otherwise if you're looking to figure out what the SQL
would be I'd have to find time to look more closely.
On Mar 11, 2014, at 7:29 PM, Michael Weylandt <michael.weyla...@gmail.com>
wrote:
> Database wizards,
>
> I've got a situation where I have one table with multiple one-to-many
> mappings (i.e., it is the "one" in a "one-to-many" with more than one other
> table).
>
> For each row of the "one", I want to group each of the "many"s by some column
> and do an outer join between the "one" and the "group by max" rows of each
> "many."
>
> Minimal example:
>
> #################################################################################################
> from sqlalchemy import create_engine, Column, String, Integer, ForeignKey
> from sqlalchemy.orm import sessionmaker
> from sqlalchemy.ext.declarative import declarative_base
>
> engine = create_engine("sqlite:///:memory:", echo = True)
> session = sessionmaker(bind = engine)()
>
> Base = declarative_base()
>
> class A(Base):
> __tablename__ = 'A'
> id = Column(Integer, primary_key = True)
> letter = Column(String)
>
> def __repr__(self):
> return "A(id = %s, letter = %s)" % (self.id, self.letter)
>
> class B(Base):
> __tablename__ = 'B'
> id = Column(Integer, primary_key = True)
> A_id = Column(Integer, ForeignKey('A.id'), nullable = False)
> word = Column(String)
> number = Column(Integer)
>
> def __repr__(self):
> return "B(A_id = %s, word = %s, number = %s)" % (self.A_id,
> self.word, self.number)
>
>
> class C(Base):
> __tablename__ = 'C'
> id = Column(Integer, primary_key = True)
> A_id = Column(Integer, ForeignKey('A.id'), nullable = False)
> word = Column(String)
> number = Column(Integer)
>
> def __repr__(self):
> return "B(A_id = %s, word = %s, number = %s)" % (self.A_id,
> self.word, self.number)
>
> Base.metadata.create_all(engine)
>
> session.add_all([
> A(letter = "A", id = 1),
> A(letter = "B", id = 2),
> A(letter = "C", id = 3),
> ])
> session.commit()
>
> session.add_all([
> B(A_id = 1, word = "Foo", number = 1),
> B(A_id = 1, word = "Bar", number = 2),
> B(A_id = 3, word = "Baz", number = 2),
> ])
> session.commit()
>
> session.add_all([
> C(A_id = 1, word = "cat", number = 5),
> C(A_id = 2, word = "dog", number = 6),
> C(A_id = 2, word = "cow", number = 2),
> ])
> session.commit()
>
> ## I want a query which returns
> ## A.letter | B.word | B.number | C.word | C.number
> ## ------------------------------------------------
> ## A | Bar | 2 | cat | 5
> ## B | NULL | NULL | dog | 6
> ## C | Baz | 2 | NULL | NULL
> ##
> ## That is, for each A
> ## 1) Group table B by A_id, and select the B in each group with the
> highest B.number
> ## 1) Group table C by A_id, and select the C in each group with the
> highest C.number
> ## Returning NULL (left outer join) if nothing is found
>
> ################################################################################
>
> I've tried playing with subqueries and func.max and I'm able to get the outer
> join + grouping, but I'm not getting the "max row" for each group. Something
> like this:
>
> ###################################################################################
> from sqlalchemy import func
> b_subq = session.query(func.max(B.number).label("b_max"),
> B).group_by(B.A_id).subquery()
> c_subq = session.query(func.max(C.number).label("c_max"),
> C).group_by(C.A_id).subquery()
>
> session.query(A, B, C).outerjoin(B, C).group_by(A).outerjoin(b_subq,
> c_subq).all() ## Fails for some reason
> session.query(A, B, C).outerjoin(B, C).outerjoin(b_subq).group_by(A).all() ##
> Works after removing c_subq
> ###################################################################################
>
> Any pointers?
>
> Thanks,
> Michael
>
> (Details: python 2.7.3, sqlalchemy 0.8.1, RHEL 6)
>
> --
> 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/d/optout.
--
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/d/optout.
