def test_query_findChangeSelect(self): """ You can change just the select portion of the query with find """ oper = yield self.getOperator() pool = yield self.getPool() p = Parent() p.id = 2 yield pool.runInteraction(oper.insert, p) c = Child() c.id = 4 c.parent_id = 2 yield pool.runInteraction(oper.insert, c) query = Query(Child, Parent.id == Child.parent_id, Parent.id == p.id) items = yield pool.runInteraction(oper.query, query) self.assertEqual(len(items), 1) self.assertEqual(items[0].id, 4) query = query.find(Parent) items = yield pool.runInteraction(oper.query, query) self.assertEqual(len(items), 1) self.assertEqual(items[0].id, 2)
def test_query_noConstraints(self): """ You can query for an object with no constraints """ oper = yield self.getOperator() pool = yield self.getPool() parent = yield pool.runInteraction(oper.insert, Parent()) query = Query(Parent) results = yield pool.runInteraction(oper.query, query) self.assertEqual(len(results), 1) self.assertTrue(isinstance(results[0], Parent)) # sub query child = Child() child.parent_id = parent.id yield pool.runInteraction(oper.insert, child) query = query.find(Child, Child.parent_id == Parent.id) results = yield pool.runInteraction(oper.query, query) self.assertEqual(len(results), 1) self.assertTrue(isinstance(results[0], Child)) # left join query = Query(Parent).find(Child, joins=[LeftJoin(Child, Child.parent_id == Parent.id)]) results = yield pool.runInteraction(oper.query, query) self.assertEqual(len(results), 1) self.assertTrue(isinstance(results[0], Child))
def test_query_buildLeftJoin(self): """ You can build on to a left-joined query """ oper = yield self.getOperator() pool = yield self.getPool() parent = yield pool.runInteraction(oper.insert, Parent()) child = Child(u'child') child.parent_id = parent.id yield pool.runInteraction(oper.insert, child) yield pool.runInteraction(oper.insert, Parent()) book = Book() book.name = u'Around the World in 80 Days' yield pool.runInteraction(oper.insert, book) yield pool.runInteraction(oper.insert, FavoriteBook(child.id, book.id)) def q(query): return pool.runInteraction(oper.query, query) # single query = Query(Parent, Parent.id != None) rows = yield q(query) self.assertEqual(len(rows), 2) # left join query = query.find(Child, joins=[ LeftJoin(Child, Child.parent_id == Parent.id)]) rows = yield q(query) self.assertEqual(len(rows), 2) self.assertIn(None, rows, "Should have one null record") # left join 2 query = query.find(FavoriteBook, joins=[ LeftJoin(FavoriteBook, FavoriteBook.child_id == Child.id)]) rows = yield q(query) self.assertEqual(len(rows), 2) self.assertIn(None, rows, "Should have one null record") # change select query = query.find((Parent, FavoriteBook)) rows = yield q(query) self.assertEqual(len(rows), 2) self.assertIn(None, [x[1] for x in rows], "Should have null FavoriteBook")
def test_query_buildLeftJoin_atTheEnd(self): """ You should be able to successfully query """ oper = yield self.getOperator() pool = yield self.getPool() handle = ormHandle(pool) p1 = yield handle.insert(Parent()) yield handle.insert(Parent()) c1 = Child() c1.parent_id = p1.id yield handle.insert(c1) c2 = Child() c2.parent_id = p1.id yield handle.insert(c2) b1 = yield handle.insert(Book()) yield handle.insert(Book()) yield handle.insert(FavoriteBook(c1.id, b1.id)) query = Query(Child, Child.parent_id == Parent.id, Parent.id == p1.id) query = query.find(Child, Child.id == c1.id) query = query.find(FavoriteBook, FavoriteBook.child_id == Child.id) query = query.find((FavoriteBook, Book), joins=[LeftJoin(Book, FavoriteBook.book_id == Book.id)]) rows = yield pool.runInteraction(oper.query, query) self.assertEqual(len(rows), 1) self.assertTrue(isinstance(rows[0][0], FavoriteBook)) self.assertEqual(rows[0][0].child_id, c1.id) self.assertEqual(rows[0][0].book_id, b1.id) self.assertTrue(isinstance(rows[0][1], Book)) self.assertEqual(rows[0][1].id, b1.id)
def test_query_build(self): """ You can build on to a query """ oper = yield self.getOperator() pool = yield self.getPool() # distractions to make sure we're not picking them up p = yield pool.runInteraction(oper.insert, Parent()) c = Child(u'nope') c.parent_id = p.id yield pool.runInteraction(oper.insert, c) b = Book() b.name = u'Gone in Sixty Seconds' yield pool.runInteraction(oper.insert, b) f = FavoriteBook(c.id, b.id) yield pool.runInteraction(oper.insert, f) # data we're looking for parent = yield pool.runInteraction(oper.insert, Parent()) child = Child(u'child') child.parent_id = parent.id yield pool.runInteraction(oper.insert, child) book = Book() book.name = u'Around the World in 80 Days' yield pool.runInteraction(oper.insert, book) fav = yield pool.runInteraction(oper.insert, FavoriteBook(child.id, book.id)) def q(query): return pool.runInteraction(oper.query, query) # simple query = Query(Parent, Eq(Parent.id, parent.id)) rows = yield q(query) self.assertEqual(len(rows), 1, "Should return just the one parent") # one join query2 = query.find(Child, Eq(Parent.id, Child.parent_id)) rows = yield q(query2) self.assertEqual(len(rows), 1, "Should return just the child") row = rows[0] self.assertEqual(row.name, 'child') # two joins query3 = query2.find(FavoriteBook, Eq(Child.id, FavoriteBook.child_id)) rows = yield q(query3) self.assertEqual(len(rows), 1, "Should return just the one favorite book") row = rows[0] self.assertTrue(isinstance(row, FavoriteBook)) self.assertEqual(row.child_id, child.id) self.assertEqual(row.book_id, fav.book_id) # three joins query4 = query3.find(Book, Eq(Book.id, FavoriteBook.book_id)) rows = yield q(query4) self.assertEqual(len(rows), 1, "Should return the one book") row = rows[0] self.assertTrue(isinstance(row, Book)) self.assertEqual(row.name, u'Around the World in 80 Days') # Parent straight to Book query5 = query.find(Book, And( Eq(Book.id, FavoriteBook.book_id), Eq(FavoriteBook.child_id, Child.id), Eq(Child.parent_id, Parent.id), Eq(Parent.id, parent.id), )) rows = yield q(query5) self.assertEqual(len(rows), 1, "Just the one book still") row = rows[0] self.assertEqual(row.name, u'Around the World in 80 Days')