I think I'm looking for something like this:
CREATE TABLE #B_temp(id INTEGER, A_id INTEGER, word VARCHAR, number
INTEGER);
CREATE TABLE #C_temp(id INTEGER, A_id INTEGER, word VARCHAR, number
INTEGER);
SELECT * FROM B WHERE id IN (SELECT MAX(id) FROM B GROUP BY A_id) INTO
#B_temp;
SELECT * FROM C WHERE id IN (SELECT MAX(id) FROM C GROUP BY A_id) INTO
#C_temp;
SELECT A.* , #B_temp.*, #C_temp.* FROM A LEFT OUTER JOIN #B_temp ON A.id =
#B_temp.A_id LEFT OUTER JOIN #C_temp ON A.id = #C_temp.A_id;
but with all the nice generality that comes from sqlalchemy.
Thanks for the quick response,
Michael
On Tuesday, March 11, 2014 7:41:32 PM UTC-4, Michael Bayer wrote:
>
> 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....@gmail.com<javascript:>>
> 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+...@googlegroups.com <javascript:>.
> To post to this group, send email to sqlal...@googlegroups.com<javascript:>
> .
> 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.
