def test_bound_to_query_entities(self):
class A(self.base_cls):
name = Column(String)
c_id = Column(Integer, ForeignKey('c.id'))
c = relationship('C')
class B(self.base_cls):
name = Column(String)
a_id = Column(Integer, ForeignKey('a.id'))
c_id = Column(Integer, ForeignKey('c.id'))
a = relationship('A', backref='b_list')
c = relationship('C')
class C(self.base_cls):
name = Column(String)
session = self.init()
session.add_all([
A(name='a1', c=C(name='c11'), b_list=[
B(name='b1', c=C(name='c21')),
B(name='b2', c=C(name='c22')),
]),
A(name='a2', c=C(name='c12'), b_list=[
B(name='b3', c=C(name='c23')),
B(name='b4', c=C(name='c24')),
]),
A(name='a3', c=C(name='c13'), b_list=[
B(name='b5', c=C(name='c25')),
B(name='b6', c=C(name='c26')),
]),
])
session.commit()
@define
def concat_names(a, c1, b, c2):
def body(a_name, c1_name, b_name, c2_name):
return ' '.join((a_name, c1_name, b_name, c2_name))
return body, [a.name, c1.name, b.name, c2.name]
sq = (
RelativeCollectionSubQuery.from_relation(A.b_list)
.outerjoin(B.c)
.order_by(B.name.asc())
)
query = (
ConstructQuery({
'names': map_(concat_names.defn(A, bind(C, None), B, C), sq),
})
.outerjoin(A.c)
.order_by(A.name.asc())
.with_session(session.registry())
)
self.assertEqual(
[dict(obj) for obj in query.all()],
[{'names': ['a1 c11 b1 c21', 'a1 c11 b2 c22']},
{'names': ['a2 c12 b3 c23', 'a2 c12 b4 c24']},
{'names': ['a3 c13 b5 c25', 'a3 c13 b6 c26']}],
)
def test_bound_to_query_reference(self):
class A(self.base_cls):
name = Column(String)
b_list = relationship('B')
class B(self.base_cls):
name = Column(String)
a_id = Column(Integer, ForeignKey('a.id'))
ref = Column(Integer)
class C(self.base_cls):
name = Column(String)
ref = Column(Integer)
session = self.init()
session.add_all([
A(name='a1', b_list=[B(name='b1', ref=-1),
B(name='b2', ref=2)]),
A(name='a2', b_list=[B(name='b3', ref=-3),
B(name='b4', ref=4)]),
C(name='c1', ref=1),
C(name='c2', ref=-1),
C(name='c3', ref=2),
C(name='c4', ref=-2),
C(name='c5', ref=3),
C(name='c6', ref=-3),
C(name='c7', ref=4),
C(name='c8', ref=-4),
])
session.commit()
sq1 = (RelativeCollectionSubQuery.from_relation(A.b_list).order_by(
B.name.asc()))
sq2 = (RelativeCollectionSubQuery(bind(func.abs(B.ref), sq1),
func.abs(C.ref)).order_by(
C.name.asc()))
query = (ConstructQuery({
'a_name': A.name,
'c_names': map_(map_(C.name, sq2), sq1),
}).order_by(A.name.asc()).with_session(session.registry()))
self.assertEqual(
[dict(obj) for obj in query.all()],
[
{
'a_name': 'a1',
'c_names': [['c1', 'c2'], ['c3', 'c4']]
},
{
'a_name': 'a2',
'c_names': [['c5', 'c6'], ['c7', 'c8']]
},
],
)
def test_bound_to_query_reference(self):
class A(self.base_cls):
name = Column(String)
b_list = relationship('B')
class B(self.base_cls):
name = Column(String)
a_id = Column(Integer, ForeignKey('a.id'))
ref = Column(Integer)
class C(self.base_cls):
name = Column(String)
ref = Column(Integer)
session = self.init()
session.add_all([
A(name='a1', b_list=[B(name='b1', ref=-1), B(name='b2', ref=2)]),
A(name='a2', b_list=[B(name='b3', ref=-3), B(name='b4', ref=4)]),
C(name='c1', ref=1), C(name='c2', ref=-1),
C(name='c3', ref=2), C(name='c4', ref=-2),
C(name='c5', ref=3), C(name='c6', ref=-3),
C(name='c7', ref=4), C(name='c8', ref=-4),
])
session.commit()
sq1 = (
RelativeCollectionSubQuery.from_relation(A.b_list)
.order_by(B.name.asc())
)
sq2 = (
RelativeCollectionSubQuery(bind(func.abs(B.ref), sq1), func.abs(C.ref))
.order_by(C.name.asc())
)
query = (
ConstructQuery({
'a_name': A.name,
'c_names': map_(map_(C.name, sq2), sq1),
})
.order_by(A.name.asc())
.with_session(session.registry())
)
self.assertEqual(
[dict(obj) for obj in query.all()],
[
{'a_name': 'a1', 'c_names': [['c1', 'c2'], ['c3', 'c4']]},
{'a_name': 'a2', 'c_names': [['c5', 'c6'], ['c7', 'c8']]},
],
)
def test_bound_to_query_expressions(self):
class A(self.base_cls):
name = Column(String)
b_list = relationship('B')
class B(self.base_cls):
name = Column(String)
a_id = Column(Integer, ForeignKey('a.id'))
session = self.init()
session.add_all([
A(name='a1', b_list=[B(name='b1')]),
A(name='a2', b_list=[B(name='b4'), B(name='b5')]),
A(name='a3', b_list=[B(name='b7'),
B(name='b8'),
B(name='b9')]),
])
session.commit()
sq = (RelativeObjectSubQuery.from_relation(A.b_list).group_by(B.a_id))
query = (ConstructQuery({
'a_name': A.name,
'b_count': get_(bind(func.count(), sq), sq),
}).order_by(A.name.asc()).with_session(session.registry()))
self.assertEqual(
[dict(obj) for obj in query.all()],
[{
'a_name': 'a1',
'b_count': 1
}, {
'a_name': 'a2',
'b_count': 2
}, {
'a_name': 'a3',
'b_count': 3
}],
)
def test_bound_to_query_expressions(self):
class A(self.base_cls):
name = Column(String)
b_list = relationship('B')
class B(self.base_cls):
name = Column(String)
a_id = Column(Integer, ForeignKey('a.id'))
session = self.init()
session.add_all([
A(name='a1', b_list=[B(name='b1')]),
A(name='a2', b_list=[B(name='b4'), B(name='b5')]),
A(name='a3', b_list=[B(name='b7'), B(name='b8'), B(name='b9')]),
])
session.commit()
sq = (
RelativeObjectSubQuery.from_relation(A.b_list)
.group_by(B.a_id)
)
query = (
ConstructQuery({
'a_name': A.name,
'b_count': get_(bind(func.count(), sq), sq),
})
.order_by(A.name.asc())
.with_session(session.registry())
)
self.assertEqual(
[dict(obj) for obj in query.all()],
[{'a_name': 'a1', 'b_count': 1},
{'a_name': 'a2', 'b_count': 2},
{'a_name': 'a3', 'b_count': 3}],
)
def test_bound_to_query_entities(self):
class A(self.base_cls):
name = Column(String)
c_id = Column(Integer, ForeignKey('c.id'))
c = relationship('C')
class B(self.base_cls):
name = Column(String)
a_id = Column(Integer, ForeignKey('a.id'))
c_id = Column(Integer, ForeignKey('c.id'))
a = relationship('A', backref='b_list')
c = relationship('C')
class C(self.base_cls):
name = Column(String)
session = self.init()
session.add_all([
A(name='a1',
c=C(name='c11'),
b_list=[
B(name='b1', c=C(name='c21')),
B(name='b2', c=C(name='c22')),
]),
A(name='a2',
c=C(name='c12'),
b_list=[
B(name='b3', c=C(name='c23')),
B(name='b4', c=C(name='c24')),
]),
A(name='a3',
c=C(name='c13'),
b_list=[
B(name='b5', c=C(name='c25')),
B(name='b6', c=C(name='c26')),
]),
])
session.commit()
@define
def concat_names(a, c1, b, c2):
def body(a_name, c1_name, b_name, c2_name):
return ' '.join((a_name, c1_name, b_name, c2_name))
return body, [a.name, c1.name, b.name, c2.name]
sq = (RelativeCollectionSubQuery.from_relation(A.b_list).outerjoin(
B.c).order_by(B.name.asc()))
query = (ConstructQuery({
'names':
map_(concat_names.defn(A, bind(C, None), B, C), sq),
}).outerjoin(A.c).order_by(A.name.asc()).with_session(
session.registry()))
self.assertEqual(
[dict(obj) for obj in query.all()],
[{
'names': ['a1 c11 b1 c21', 'a1 c11 b2 c22']
}, {
'names': ['a2 c12 b3 c23', 'a2 c12 b4 c24']
}, {
'names': ['a3 c13 b5 c25', 'a3 c13 b6 c26']
}],
)
