Hi Mike,
Thanks for the help.
I'm not sure that's the right set of subqueries though; looking at the
generated SQL, the result seems closer to:
CREATE TABLE "#B_temp" AS (SELECT * FROM B WHERE id IN (SELECT max(number)
mn FROM B GROUP BY A_id))
CREATE TABLE "#C_temp" AS (SELECT * FROM C WHERE id IN (SELECT max(number)
mn FROM C GROUP BY A_id))
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;
And indeed the result of the first q you suggested gives
[(A(id = 1, letter = A), 2, 1, u'Bar', 2, None, None, None, None),
(A(id = 2, letter = B), None, None, None, None, None, None, None, None),
(A(id = 3, letter = C), None, None, None, None, None, None, None, None)]
which has too many "None"s.
Michael
On Tuesday, March 11, 2014 9:55:10 PM UTC-4, Michael Bayer wrote:
>
> OK subqueries like this:
>
> bsubq =
> session.query(B).filter(B.id.in_(session.query(func.max(B.number)).group_by(B.a_id).as_scalar())).subquery()
> csubq =
> session.query(C).filter(C.id.in_(session.query(func.max(C.number)).group_by(C.a_id).as_scalar())).subquery()
>
>
> select them all like this:
>
> q = session.query(A, bsubq, csubq).outerjoin(bsubq,
> A.id==bsubq.c.a_id).outerjoin(csubq, A.id==csubq.c.a_id)
> print q.all()
>
> or if you want B C entities:
>
> q = session.query(A, aliased(B, bsubq), aliased(C,
> csubq)).outerjoin(bsubq, A.id==bsubq.c.a_id).outerjoin(csubq,
> A.id==csubq.c.a_id)
> print q.all()
>
>
>
>
> On Mar 11, 2014, at 8:23 PM, Michael Weylandt
> <michael....@gmail.com<javascript:>>
> wrote:
>
> Sorry for the barrage, Mike. I didn't check my code worked before I posted
> (shame on me). I'm looking for:
>
> CREATE TABLE "#B_temp" AS (SELECT * FROM B WHERE id IN (SELECT id FROM B
> JOIN (SELECT max(number) mn FROM B GROUP BY A_id) AS max ON max.mn =
> B.number));
>
> CREATE TABLE "#C_temp" AS (SELECT * FROM C WHERE id IN (SELECT id FROM C
> JOIN (SELECT max(number) mn FROM C GROUP BY A_id) AS max ON max.mn =
> C.number));
>
> 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;
>
> 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>
>> 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 tosqlalchemy+...@googlegroups.com.
>> To post to this group, send email to sqlal...@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 tosqlalchemy+...@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.
