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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
