Hi Mike,
I've simplified the query I'm looking for into a one line: (not sure why I
didn't think of HAVING earlier -- definitely making a mountain from
molehills)
SELECT A.*, B1.*, C1.* FROM A LEFT OUTER JOIN (SELECT * FROM B GROUP BY
A_id HAVING number = max(number)) AS B1 ON A.id = B1.A_id LEFT OUTER JOIN
(SELECT * FROM C GROUP BY A_id HAVING number = max(number)) AS C1 on A.id =
C1.A_id;
id letter id A_id word number id
A_id word number
---------- ---------- ---------- ---------- ---------- ----------
---------- ---------- ---------- ----------
1 A 2 1 Bar 2 1
1 cat 5
2 B 2
2 dog 6
3 C 3 3 Baz 2
I think this looks like the following in sqlalchemy:
b_subq = session.query(B).group_by(B.A_id).having(B.number ==
func.max(B.number)).subquery()
c_subq = session.query(C).group_by(B.A_id).having(C.number ==
func.max(C.number)).subquery()
q = session.query(A, bsubq, csubq).outerjoin(bsubq, bsubq.c.A_id ==
A.id).outerjoin(csubq, csubq.c.A_id == A.id)
print q
But this doesn't seem to give the right results (omitting the "dog" + 6
match). If I take the generated SQL and feed it "by hand" it works though,
so I'll have to look elsewhere to resolve that.
I do note that only "A" is returned as an object. Is there any way to get
"B" and "C" wrapped up nicely by the ORM as well?
Michael
On Wednesday, March 12, 2014 12:02:10 PM UTC-4, Michael Weylandt wrote:
>
> 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>
>> 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.
>> 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
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.
