def test_many_to_many_one(self): class Node(Base): pass mapper( Node, nodes, properties={ 'children': relationship( Node, secondary=node_to_nodes, primaryjoin=nodes.c.id == node_to_nodes.c.left_node_id, secondaryjoin=nodes.c.id == node_to_nodes.c.right_node_id, backref='parents'), 'favorite': relationship(Node, remote_side=nodes.c.id) }) sess = create_session() n1 = Node(data='n1') n2 = Node(data='n2') n3 = Node(data='n3') n4 = Node(data='n4') n5 = Node(data='n5') n4.favorite = n3 n1.favorite = n5 n5.favorite = n2 n1.children = [n2, n3, n4] n2.children = [n3, n5] n3.children = [n5, n4] sess.add_all([n1, n2, n3, n4, n5]) # can't really assert the SQL on this easily # since there's too many ways to insert the rows. # so check the end result sess.flush() eq_( sess.query(node_to_nodes.c.left_node_id, node_to_nodes.c.right_node_id).\ order_by(node_to_nodes.c.left_node_id, node_to_nodes.c.right_node_id).\ all(), sorted([ (n1.id, n2.id), (n1.id, n3.id), (n1.id, n4.id), (n2.id, n3.id), (n2.id, n5.id), (n3.id, n5.id), (n3.id, n4.id) ]) ) sess.delete(n1) self.assert_sql_execution( testing.db, sess.flush, # this is n1.parents firing off, as it should, since # passive_deletes is False for n1.parents CompiledSQL( "SELECT nodes.id AS nodes_id, nodes.data AS nodes_data, " "nodes.favorite_node_id AS nodes_favorite_node_id FROM " "nodes, node_to_nodes WHERE :param_1 = " "node_to_nodes.right_node_id AND nodes.id = " "node_to_nodes.left_node_id", lambda ctx: {u'param_1': n1.id}, ), CompiledSQL( "DELETE FROM node_to_nodes WHERE " "node_to_nodes.left_node_id = :left_node_id AND " "node_to_nodes.right_node_id = :right_node_id", lambda ctx: [{ 'right_node_id': n2.id, 'left_node_id': n1.id }, { 'right_node_id': n3.id, 'left_node_id': n1.id }, { 'right_node_id': n4.id, 'left_node_id': n1.id }]), CompiledSQL("DELETE FROM nodes WHERE nodes.id = :id", lambda ctx: {'id': n1.id}), ) for n in [n2, n3, n4, n5]: sess.delete(n) # load these collections # outside of the flush() below n4.children n5.children self.assert_sql_execution( testing.db, sess.flush, CompiledSQL( "DELETE FROM node_to_nodes WHERE node_to_nodes.left_node_id " "= :left_node_id AND node_to_nodes.right_node_id = " ":right_node_id", lambda ctx: [{ 'right_node_id': n5.id, 'left_node_id': n3.id }, { 'right_node_id': n4.id, 'left_node_id': n3.id }, { 'right_node_id': n3.id, 'left_node_id': n2.id }, { 'right_node_id': n5.id, 'left_node_id': n2.id }]), CompiledSQL("DELETE FROM nodes WHERE nodes.id = :id", lambda ctx: [{ 'id': n4.id }, { 'id': n5.id }]), CompiledSQL("DELETE FROM nodes WHERE nodes.id = :id", lambda ctx: [{ 'id': n2.id }, { 'id': n3.id }]), )
def test_many_to_many_one(self): class Node(Base): pass mapper(Node, nodes, properties={ 'children':relationship(Node, secondary=node_to_nodes, primaryjoin=nodes.c.id==node_to_nodes.c.left_node_id, secondaryjoin=nodes.c.id==node_to_nodes.c.right_node_id, backref='parents' ), 'favorite':relationship(Node, remote_side=nodes.c.id) }) sess = create_session() n1 = Node(data='n1') n2 = Node(data='n2') n3 = Node(data='n3') n4 = Node(data='n4') n5 = Node(data='n5') n4.favorite = n3 n1.favorite = n5 n5.favorite = n2 n1.children = [n2, n3, n4] n2.children = [n3, n5] n3.children = [n5, n4] sess.add_all([n1, n2, n3, n4, n5]) # can't really assert the SQL on this easily # since there's too many ways to insert the rows. # so check the end result sess.flush() eq_( sess.query(node_to_nodes.c.left_node_id, node_to_nodes.c.right_node_id).\ order_by(node_to_nodes.c.left_node_id, node_to_nodes.c.right_node_id).\ all(), sorted([ (n1.id, n2.id), (n1.id, n3.id), (n1.id, n4.id), (n2.id, n3.id), (n2.id, n5.id), (n3.id, n5.id), (n3.id, n4.id) ]) ) sess.delete(n1) self.assert_sql_execution( testing.db, sess.flush, # this is n1.parents firing off, as it should, since # passive_deletes is False for n1.parents CompiledSQL( "SELECT nodes.id AS nodes_id, nodes.data AS nodes_data, " "nodes.favorite_node_id AS nodes_favorite_node_id FROM " "nodes, node_to_nodes WHERE :param_1 = " "node_to_nodes.right_node_id AND nodes.id = " "node_to_nodes.left_node_id" , lambda ctx:{u'param_1': n1.id}, ), CompiledSQL( "DELETE FROM node_to_nodes WHERE " "node_to_nodes.left_node_id = :left_node_id AND " "node_to_nodes.right_node_id = :right_node_id", lambda ctx:[ {'right_node_id': n2.id, 'left_node_id': n1.id}, {'right_node_id': n3.id, 'left_node_id': n1.id}, {'right_node_id': n4.id, 'left_node_id': n1.id} ] ), CompiledSQL( "DELETE FROM nodes WHERE nodes.id = :id", lambda ctx:{'id': n1.id} ), ) for n in [n2, n3, n4, n5]: sess.delete(n) # load these collections # outside of the flush() below n4.children n5.children self.assert_sql_execution( testing.db, sess.flush, CompiledSQL( "DELETE FROM node_to_nodes WHERE node_to_nodes.left_node_id " "= :left_node_id AND node_to_nodes.right_node_id = " ":right_node_id", lambda ctx:[ {'right_node_id': n5.id, 'left_node_id': n3.id}, {'right_node_id': n4.id, 'left_node_id': n3.id}, {'right_node_id': n3.id, 'left_node_id': n2.id}, {'right_node_id': n5.id, 'left_node_id': n2.id} ] ), CompiledSQL( "DELETE FROM nodes WHERE nodes.id = :id", lambda ctx:[{'id': n4.id}, {'id': n5.id}] ), CompiledSQL( "DELETE FROM nodes WHERE nodes.id = :id", lambda ctx:[{'id': n2.id}, {'id': n3.id}] ), )