def test_nested_get_with_null_value_in_outer_expr(self):
class A(self.base_cls):
name = Column(String)
b_id = Column('b_id', Integer, ForeignKey('b.id'))
b = relationship('B', backref='a_list')
class B(self.base_cls):
name = Column(String)
c_id = Column('c_id', Integer, ForeignKey('c.id'))
c = relationship('C', backref='b_list')
class C(self.base_cls):
name = Column(String)
session = self.init()
c1 = C(name='c1')
b1, b2 = B(name='b1', c=c1), B(name='b2')
a1, a2, a3 = A(name='a1', b=b1), A(name='a2', b=b2), A(name='a3')
session.add_all([a1, a2, a3, b1, b2, c1])
session.commit()
res = tuple(dict(obj) for obj in ConstructQuery({
'a_name': A.name,
'b_name': get_(B.name, A.b),
'c_name': get_(get_(C.name, B.c), A.b),
}).with_session(session.registry()).order_by(A.name).all())
self.assertEqual(res, (
{'a_name': 'a1', 'b_name': 'b1', 'c_name': 'c1'},
{'a_name': 'a2', 'b_name': 'b2', 'c_name': None},
{'a_name': 'a3', 'b_name': None, 'c_name': None},
))
def test_one_to_one(self):
class A(self.base_cls):
name = Column(String)
b = relationship('B', uselist=False)
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=B(name='b1')),
A(name='a2'),
B(name='b2'),
A(name='a3', b=B(name='b3')),
])
session.commit()
query = (
ConstructQuery({
'a_name': A.name,
'b_name': get_(if_(B.id, apply_(capitalize, [B.name]), '~'),
A.b),
})
.with_session(session.registry())
)
self.assertEqual(
tuple(dict(obj) for obj in query.all()),
({'a_name': 'a1', 'b_name': 'B1'},
{'a_name': 'a2', 'b_name': '~'},
{'a_name': 'a3', 'b_name': 'B3'}),
)
def test_with_custom_queries(self):
class A(self.base_cls):
name = Column(String)
class B(self.base_cls):
name = Column(String)
session = self.init()
session.add_all([
A(name='a1'), A(name='a2'), A(name='a3'),
B(name='b1'), B(name='b2'), B(name='b3'),
])
session.commit()
sq1 = CollectionSubQuery(B).order_by(B.name.desc())
q1 = (
ConstructQuery({'a_name': A.name, 'b_list': map_(B.name, sq1)},
session)
.order_by(A.name)
)
self.assertEqual({(obj.a_name, tuple(obj.b_list)) for obj in q1.all()},
{('a1', ('b3', 'b2', 'b1')),
('a2', ('b3', 'b2', 'b1')),
('a3', ('b3', 'b2', 'b1'))})
sq2 = ObjectSubQuery(B).order_by(B.name.desc())
q2 = (
ConstructQuery({'a_name': A.name, 'b_name': get_(B.name, sq2)},
session)
.order_by(A.name)
)
self.assertEqual({(obj.a_name, obj.b_name) for obj in q2.all()},
{('a1', 'b3'), ('a2', 'b3'), ('a3', 'b3')})
def test_non_empty_in_op_in_relative_subqueries(self):
in_op = ColumnOperators.in_
class EmptyInOpError(Exception):
pass
def wrapper(self, values):
if not values:
raise EmptyInOpError
return in_op(self, values)
patcher = patch.object(ColumnOperators, 'in_', wrapper)
class A(self.base_cls):
value = Column(String)
class B(self.base_cls):
value = Column(String)
session = self.init()
session.add_all([A(), A(), A()])
session.commit()
obj_sq = RelativeObjectSubQuery(A.value, B.value)
obj_query = ConstructQuery({
'a_id': A.id,
'b_id': get_(B.id, obj_sq)
}, session)
with patcher:
obj_query.all()
list_sq = RelativeCollectionSubQuery(A.value, B.value)
list_query = ConstructQuery({
'a_id': A.id,
'b_id': map_(B.id, list_sq)
}, session)
with patcher:
list_query.all()
def test_one_to_one(self):
class A(self.base_cls):
name = Column(String)
b = relationship('B', uselist=False)
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=B(name='b1')),
A(name='a2'),
B(name='b2'),
A(name='a3', b=B(name='b3')),
])
session.commit()
query = (ConstructQuery({
'a_name':
A.name,
'b_name':
get_(if_(B.id, apply_(capitalize, [B.name]), '~'), A.b),
}).with_session(session.registry()))
self.assertEqual(
tuple(dict(obj) for obj in query.all()),
({
'a_name': 'a1',
'b_name': 'B1'
}, {
'a_name': 'a2',
'b_name': '~'
}, {
'a_name': 'a3',
'b_name': 'B3'
}),
)
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_with_custom_queries(self):
class A(self.base_cls):
name = Column(String)
class B(self.base_cls):
name = Column(String)
session = self.init()
session.add_all([
A(name='a1'),
A(name='a2'),
A(name='a3'),
B(name='b1'),
B(name='b2'),
B(name='b3'),
])
session.commit()
sq1 = CollectionSubQuery(B).order_by(B.name.desc())
q1 = (ConstructQuery({
'a_name': A.name,
'b_list': map_(B.name, sq1)
}, session).order_by(A.name))
self.assertEqual({(obj.a_name, tuple(obj.b_list))
for obj in q1.all()}, {('a1', ('b3', 'b2', 'b1')),
('a2', ('b3', 'b2', 'b1')),
('a3', ('b3', 'b2', 'b1'))})
sq2 = ObjectSubQuery(B).order_by(B.name.desc())
q2 = (ConstructQuery({
'a_name': A.name,
'b_name': get_(B.name, sq2)
}, session).order_by(A.name))
self.assertEqual({(obj.a_name, obj.b_name)
for obj in q2.all()}, {('a1', 'b3'), ('a2', 'b3'),
('a3', 'b3')})
def test_non_empty_in_op_in_relative_subqueries(self):
in_op = ColumnOperators.in_
class EmptyInOpError(Exception):
pass
def wrapper(self, values):
if not values:
raise EmptyInOpError
return in_op(self, values)
patcher = patch.object(ColumnOperators, 'in_', wrapper)
class A(self.base_cls):
value = Column(String)
class B(self.base_cls):
value = Column(String)
session = self.init()
session.add_all([A(), A(), A()])
session.commit()
obj_sq = RelativeObjectSubQuery(A.value, B.value)
obj_query = ConstructQuery({'a_id': A.id,
'b_id': get_(B.id, obj_sq)},
session)
with patcher:
obj_query.all()
list_sq = RelativeCollectionSubQuery(A.value, B.value)
list_query = ConstructQuery({'a_id': A.id,
'b_id': map_(B.id, list_sq)},
session)
with patcher:
list_query.all()
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_nested(self):
"""
A <- B -> C -> D <- E
"""
class A(self.base_cls):
name = Column(String)
class B(self.base_cls):
name = Column(String)
a_id = Column('a_id', Integer, ForeignKey('a.id'))
a = relationship('A', backref='b_list')
c_id = Column('c_id', Integer, ForeignKey('c.id'))
c = relationship('C', backref='b_list')
class C(self.base_cls):
name = Column(String)
d_id = Column('d_id', Integer, ForeignKey('d.id'))
d = relationship('D', backref='c_list')
class D(self.base_cls):
name = Column(String)
class E(self.base_cls):
name = Column(String)
d_id = Column('d_id', Integer, ForeignKey('d.id'))
d = relationship('D', backref='e_list')
session = self.init()
a1, a2, a3 = A(name='a1'), A(name='a2'), A(name='a3')
d1 = D(name='d1',
c_list=[
C(name='c1',
b_list=[
B(name='b1'),
B(name='b2', a=a2),
B(name='b3', a=a3)
]),
C(name='c2',
b_list=[
B(name='b4', a=a1),
B(name='b5'),
B(name='b6', a=a3)
]),
C(name='c3',
b_list=[
B(name='b7', a=a1),
B(name='b8', a=a2),
B(name='b9')
])
],
e_list=[E(name='e1'), E(name='e2'),
E(name='e3')])
session.add_all([a1, a2, a3, d1])
session.commit()
# A <- B -> C
r1 = tuple(
dict(obj) for obj in ConstructQuery(
{
'a_name': A.name,
'b_names': map_(B.name, A.b_list),
'c_names': map_(get_(C.name, B.c), A.b_list)
}).with_session(session.registry()).order_by(A.name).all())
self.assertEqual(r1, (
{
'a_name': 'a1',
'b_names': ['b4', 'b7'],
'c_names': ['c2', 'c3']
},
{
'a_name': 'a2',
'b_names': ['b2', 'b8'],
'c_names': ['c1', 'c3']
},
{
'a_name': 'a3',
'b_names': ['b3', 'b6'],
'c_names': ['c1', 'c2']
},
))
# B -> C -> D
r2 = tuple(
dict(obj)
for obj in ConstructQuery({
'b_name': B.name,
'c_name': get_(C.name, B.c),
'd_name': get_(get_(D.name, C.d), B.c),
}).with_session(session.registry()).order_by(B.name).all())
self.assertEqual(r2, (
{
'b_name': 'b1',
'c_name': 'c1',
'd_name': 'd1'
},
{
'b_name': 'b2',
'c_name': 'c1',
'd_name': 'd1'
},
{
'b_name': 'b3',
'c_name': 'c1',
'd_name': 'd1'
},
{
'b_name': 'b4',
'c_name': 'c2',
'd_name': 'd1'
},
{
'b_name': 'b5',
'c_name': 'c2',
'd_name': 'd1'
},
{
'b_name': 'b6',
'c_name': 'c2',
'd_name': 'd1'
},
{
'b_name': 'b7',
'c_name': 'c3',
'd_name': 'd1'
},
{
'b_name': 'b8',
'c_name': 'c3',
'd_name': 'd1'
},
{
'b_name': 'b9',
'c_name': 'c3',
'd_name': 'd1'
},
))
# C -> D <- E
r3 = tuple(
dict(obj) for obj in ConstructQuery(
{
'c_name': C.name,
'd_name': get_(D.name, C.d),
'e_names': get_(map_(E.name, D.e_list), C.d),
}).with_session(session.registry()).order_by(C.name).all())
self.assertEqual(r3, (
{
'c_name': 'c1',
'd_name': 'd1',
'e_names': ['e1', 'e2', 'e3']
},
{
'c_name': 'c2',
'd_name': 'd1',
'e_names': ['e1', 'e2', 'e3']
},
{
'c_name': 'c3',
'd_name': 'd1',
'e_names': ['e1', 'e2', 'e3']
},
))
# D <- C <- B
r4 = dict(
ConstructQuery({
'd_name': D.name,
'c_names': map_(C.name, D.c_list),
'b_names': map_(map_(B.name, C.b_list), D.c_list),
}).with_session(session.registry()).order_by(D.name).one())
self.assertEqual(r4['d_name'], 'd1')
self.assertEqual(set(r4['c_names']), {'c1', 'c2', 'c3'})
self.assertEqual(
set(map(frozenset, r4['b_names'])), {
frozenset({'b1', 'b2', 'b3'}),
frozenset({'b4', 'b5', 'b6'}),
frozenset({'b7', 'b8', 'b9'}),
})
def test_nested(self):
"""
A <- B -> C -> D <- E
"""
class A(self.base_cls):
name = Column(String)
class B(self.base_cls):
name = Column(String)
a_id = Column('a_id', Integer, ForeignKey('a.id'))
a = relationship('A', backref='b_list')
c_id = Column('c_id', Integer, ForeignKey('c.id'))
c = relationship('C', backref='b_list')
class C(self.base_cls):
name = Column(String)
d_id = Column('d_id', Integer, ForeignKey('d.id'))
d = relationship('D', backref='c_list')
class D(self.base_cls):
name = Column(String)
class E(self.base_cls):
name = Column(String)
d_id = Column('d_id', Integer, ForeignKey('d.id'))
d = relationship('D', backref='e_list')
session = self.init()
a1, a2, a3 = A(name='a1'), A(name='a2'), A(name='a3')
d1 = D(name='d1',
c_list=[C(name='c1',
b_list=[B(name='b1'),
B(name='b2', a=a2),
B(name='b3', a=a3)]),
C(name='c2',
b_list=[B(name='b4', a=a1),
B(name='b5'),
B(name='b6', a=a3)]),
C(name='c3',
b_list=[B(name='b7', a=a1),
B(name='b8', a=a2),
B(name='b9')])],
e_list=[E(name='e1'), E(name='e2'), E(name='e3')])
session.add_all([a1, a2, a3, d1])
session.commit()
# A <- B -> C
r1 = tuple(dict(obj) for obj in ConstructQuery({
'a_name': A.name,
'b_names': map_(B.name, A.b_list),
'c_names': map_(get_(C.name, B.c), A.b_list)
}).with_session(session.registry()).order_by(A.name).all())
self.assertEqual(r1, (
{'a_name': 'a1', 'b_names': ['b4', 'b7'], 'c_names': ['c2', 'c3']},
{'a_name': 'a2', 'b_names': ['b2', 'b8'], 'c_names': ['c1', 'c3']},
{'a_name': 'a3', 'b_names': ['b3', 'b6'], 'c_names': ['c1', 'c2']},
))
# B -> C -> D
r2 = tuple(dict(obj) for obj in ConstructQuery({
'b_name': B.name,
'c_name': get_(C.name, B.c),
'd_name': get_(get_(D.name, C.d), B.c),
}).with_session(session.registry()).order_by(B.name).all())
self.assertEqual(r2, (
{'b_name': 'b1', 'c_name': 'c1', 'd_name': 'd1'},
{'b_name': 'b2', 'c_name': 'c1', 'd_name': 'd1'},
{'b_name': 'b3', 'c_name': 'c1', 'd_name': 'd1'},
{'b_name': 'b4', 'c_name': 'c2', 'd_name': 'd1'},
{'b_name': 'b5', 'c_name': 'c2', 'd_name': 'd1'},
{'b_name': 'b6', 'c_name': 'c2', 'd_name': 'd1'},
{'b_name': 'b7', 'c_name': 'c3', 'd_name': 'd1'},
{'b_name': 'b8', 'c_name': 'c3', 'd_name': 'd1'},
{'b_name': 'b9', 'c_name': 'c3', 'd_name': 'd1'},
))
# C -> D <- E
r3 = tuple(dict(obj) for obj in ConstructQuery({
'c_name': C.name,
'd_name': get_(D.name, C.d),
'e_names': get_(map_(E.name, D.e_list), C.d),
}).with_session(session.registry()).order_by(C.name).all())
self.assertEqual(r3, (
{'c_name': 'c1', 'd_name': 'd1', 'e_names': ['e1', 'e2', 'e3']},
{'c_name': 'c2', 'd_name': 'd1', 'e_names': ['e1', 'e2', 'e3']},
{'c_name': 'c3', 'd_name': 'd1', 'e_names': ['e1', 'e2', 'e3']},
))
# D <- C <- B
r4 = dict(ConstructQuery({
'd_name': D.name,
'c_names': map_(C.name, D.c_list),
'b_names': map_(map_(B.name, C.b_list), D.c_list),
}).with_session(session.registry()).order_by(D.name).one())
self.assertEqual(r4['d_name'], 'd1')
self.assertEqual(set(r4['c_names']), {'c1', 'c2', 'c3'})
self.assertEqual(set(map(frozenset, r4['b_names'])), {
frozenset({'b1', 'b2', 'b3'}),
frozenset({'b4', 'b5', 'b6'}),
frozenset({'b7', 'b8', 'b9'}),
})