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')
