def test_weird_nesting(self):
node = parseq(
Q(a='A', b='B'),
(
Q(c='C') |
Q(d='D')
)
)
self.assertEqual(unicode(node), '(a = A AND b = B) AND (c = C OR d = D)')
node = parseq((
(
Q(c='C') |
Q(d='D')
) |
(
Q(e='E', f='F')
)
), a='A', b='B')
self.assertEqual(unicode(node), '(a = A AND b = B) AND (c = C OR d = D OR (e = E AND f = F))')
node = parseq((
(
Q(c='C') |
Q(d='D')
) |
(
Q(e='E', f='F') |
Q(g='G', h='H')
)
), a='A', b='B')
self.assertEqual(unicode(node), '(a = A AND b = B) AND ((c = C OR d = D) OR ((e = E AND f = F) OR (h = H AND g = G)))')
def test_update_with_q(self):
uq = UpdateQuery(Blog, title='A').where(Q(id=1))
self.assertEqual(uq.sql(),
('UPDATE blog SET title=? WHERE id = ?', ['A', 1]))
uq = UpdateQuery(Blog, title='A').where(Q(id=1) | Q(id=3))
self.assertEqual(
uq.sql(),
('UPDATE blog SET title=? WHERE (id = ? OR id = ?)', ['A', 1, 3]))
def test_model_get_with_q(self):
a = self.create_blog(title='a')
b = self.create_blog(title='b')
b2 = Blog.get(Q(title='b') | Q(title='c'))
self.assertEqual(b2.id, b.id)
self.assertQueriesEqual([
('INSERT INTO blog (title) VALUES (?)', ['a']),
('INSERT INTO blog (title) VALUES (?)', ['b']),
('SELECT * FROM blog WHERE (title = ? OR title = ?) LIMIT 1 OFFSET 0', ['b', 'c']),
])
def test_model_select_with_q(self):
a = self.create_blog(title='a')
b = self.create_blog(title='b')
qr = Blog.select().where(Q(title='a') | Q(title='b'))
self.assertEqual(list(qr), [a, b])
qr = Blog.select().where(Q(title__in=['a']) | Q(title__in=['c', 'd']))
self.assertEqual(list(qr), [a])
self.assertQueriesEqual([
('INSERT INTO blog (title) VALUES (?)', ['a']),
('INSERT INTO blog (title) VALUES (?)', ['b']),
('SELECT * FROM blog WHERE (title = ? OR title = ?)', ['a', 'b']),
('SELECT * FROM blog WHERE (title IN (?) OR title IN (?,?))', ['a', 'c', 'd']),
])
def obtener_retiros():
retiros = models.Retiro.select()
# Parametros
campos_de_busqueda = request.args.get('sSearch').split(" ")
for palabra in campos_de_busqueda:
if parece_fecha(palabra):
retiros = retiros.where(fecha__icontains=palabra)
else:
condicion_de_busqueda = Q(nombre__icontains=palabra) | Q(
apellido__icontains=palabra)
retiros = retiros.where().join(
models.Socio).where(condicion_de_busqueda)
# Aplicando limites
limite = int(request.args.get('iDisplayLength'))
desde = int(request.args.get('iDisplayStart'))
indice_columna_ordenamiento = int(request.args.get('iSortCol_0'))
# Intenta ordenar los resultados en base a la seleccion del usuario
if indice_columna_ordenamiento > 0:
columnas = {
1: 'apellido',
2: 'numero',
3: 'fecha',
4: 'monto',
}
columna_a_ordenar = columnas[indice_columna_ordenamiento]
tipo_ordenamiento = request.args.get('sSortDir_0')
retiros = retiros.order_by((columna_a_ordenar, tipo_ordenamiento))
else:
retiros = retiros.order_by(('numero', 'desc'))
retiros = retiros.paginate((desde / limite) + 1, limite)
datos = [convertir_en_formato_de_tabla(d) for d in retiros]
total_vistos = retiros.count()
total = len(list(retiros))
return jsonify({
'aaData': datos,
'iTotalDisplayRecords': total_vistos,
'iTotalRecords': total,
})
def test_complex_subquery(self):
a_blog = Blog.create(title='a blog')
b_blog = Blog.create(title='b blog')
c_blog = Blog.create(title='c blog')
a = User.create(username='******', blog=a_blog)
b = User.create(username='******', blog=b_blog)
c = User.create(username='******', blog=c_blog)
some_users = User.select().where(username__in=['a', 'b'])
c_blog_qr = Blog.select().join(User).where(~Q(id__in=some_users))
self.assertEqual(list(c_blog_qr), [c_blog])
ac_blog_qr = Blog.select().join(User).where(~Q(id__in=some_users)
| Q(username='******'))
self.assertEqual(list(ac_blog_qr), [a_blog, c_blog])
def test_simple(self):
node = Q(a='A') | Q(b='B')
self.assertEqual(unicode(node), 'a = A OR b = B')
node = parseq(Q(a='A') | Q(b='B'))
self.assertEqual(unicode(node), '(a = A OR b = B)')
node = Q(a='A') & Q(b='B')
self.assertEqual(unicode(node), 'a = A AND b = B')
node = parseq(Q(a='A') & Q(b='B'))
self.assertEqual(unicode(node), '(a = A AND b = B)')
def test_node_and_q(self):
node = parseq(
(Q(a='A') & Q(b='B')) |
(Q(c='C'))
)
self.assertEqual(unicode(node), '(c = C OR (a = A AND b = B))')
node = parseq(Q(c='C') & (Q(a='A') | Q(b='B')))
self.assertEqual(unicode(node), '((c = C) AND (a = A OR b = B))')
def test_mixed(self):
node = parseq(Q(a='A'), Q(b='B'), c='C', d='D')
self.assertEqual(unicode(node), 'a = A AND b = B AND (c = C AND d = D)')
node = parseq((Q(a='A') & Q(b='B')), c='C', d='D')
self.assertEqual(unicode(node), '(c = C AND d = D) AND (a = A AND b = B)')
node = parseq((Q(a='A') | Q(b='B')), c='C', d='D')
self.assertEqual(unicode(node), '(c = C AND d = D) AND (a = A OR b = B)')
def test_querying_across_joins_with_q(self):
a, a1, a2, b, b1, b2, t1, t2 = self.get_common_objects()
sq = Blog.select().join(Entry).join(EntryTag).where(
Q(tag='t1') | Q(tag='t2'))
self.assertEqual(list(sq), [a, b])
# permutations
sq = Blog.select().join(Entry).where(
Q(title='a2')).join(EntryTag).where(Q(tag='t1') | Q(tag='t2'))
self.assertEqual(list(sq), [a])
sq = Blog.select().join(Entry).where(
Q(title='b2')).join(EntryTag).where(Q(tag='t1') | Q(tag='t2'))
self.assertEqual(list(sq), [b])
sq = Blog.select().join(Entry).where(Q(title='a2') | Q(
title='b2')).join(EntryTag).where(Q(tag='t1'))
self.assertEqual(list(sq), [a])
sq = Blog.select().join(Entry).where(Q(title='a2') | Q(
title='b2')).join(EntryTag).where(Q(tag='t2'))
self.assertEqual(list(sq), [b])
# work the other way
sq = EntryTag.select().join(Entry).join(Blog).where(
Q(title='a') | Q(title='b'))
self.assertEqual(list(sq), [t1, t2])
sq = EntryTag.select().join(Entry).where(
Q(title='a2')).join(Blog).where(Q(title='a') | Q(title='b'))
self.assertEqual(list(sq), [t1])
sq = EntryTag.select().join(Entry).where(
Q(title='b2')).join(Blog).where(Q(title='a') | Q(title='b'))
self.assertEqual(list(sq), [t2])
sq = EntryTag.select().join(Entry).where(
Q(title='a2') | Q(title='b2')).join(Blog).where(Q(title='a'))
self.assertEqual(list(sq), [t1])
sq = EntryTag.select().join(Entry).where(
Q(title='a2') | Q(title='b2')).join(Blog).where(Q(title='b'))
self.assertEqual(list(sq), [t2])
def test_nesting(self):
node = parseq((Q(a='A') | Q(b='B')), (Q(c='C') | Q(d='D')))
self.assertEqual(unicode(node),
'(a = A OR b = B) AND (c = C OR d = D)')
node = parseq((Q(a='A') | Q(b='B')) & (Q(c='C') | Q(d='D')))
self.assertEqual(unicode(node),
'((a = A OR b = B) AND (c = C OR d = D))')
node = parseq((Q(a='A') | Q(b='B')) | (Q(c='C') | Q(d='D')))
self.assertEqual(unicode(node),
'((a = A OR b = B) OR (c = C OR d = D))')
def test_selecting_across_joins_with_q(self):
sq = SelectQuery(Entry,
'*').where(Q(title='a')
| Q(pk=1)).join(Blog).where(title='e')
self.assertEqual(sq.sql(), (
'SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.pk = ?) AND t2.title = ?',
['a', 1, 'e']))
sq = SelectQuery(Entry,
'*').where(Q(title='a') | Q(pk=1)
| Q(title='b')).join(Blog).where(title='e')
self.assertEqual(sq.sql(), (
'SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.pk = ? OR t1.title = ?) AND t2.title = ?',
['a', 1, 'b', 'e']))
# test simple chaining
sq = SelectQuery(Entry, '*').where(Q(title='a') | Q(pk=1)).where(
Q(title='b')).join(Blog).where(title='e')
self.assertEqual(sq.sql(), (
'SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.pk = ?) AND t1.title = ? AND t2.title = ?',
['a', 1, 'b', 'e']))
sq = SelectQuery(Entry, '*').where(Q(title='a') | Q(pk=1)).where(
title='b').join(Blog).where(title='e')
self.assertEqual(sq.sql(), (
'SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.pk = ?) AND t1.title = ? AND t2.title = ?',
['a', 1, 'b', 'e']))
# test q on both models
sq = SelectQuery(
Entry,
'*').where(Q(title='a')
| Q(pk=1)).join(Blog).where(Q(title='e') | Q(id=2))
self.assertEqual(sq.sql(), (
'SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.pk = ?) AND (t2.title = ? OR t2.id = ?)',
['a', 1, 'e', 2]))
# test q on both with nesting
sq = SelectQuery(
Entry,
'*').where(Q(title='a')
| Q(pk=1)).join(Blog).where((Q(title='e') | Q(id=2))
& (Q(title='f') | Q(id=3)))
self.assertEqual(sq.sql(), (
'SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.pk = ?) AND ((t2.title = ? OR t2.id = ?) AND (t2.title = ? OR t2.id = ?))',
['a', 1, 'e', 2, 'f', 3]))
def test_select_with_negation(self):
sq = SelectQuery(Blog, '*').where(~Q(title='a'))
self.assertEqual(sq.sql(),
('SELECT * FROM blog WHERE NOT title = ?', ['a']))
sq = SelectQuery(Blog, '*').where(~Q(title='a') | Q(title='b'))
self.assertEqual(
sq.sql(), ('SELECT * FROM blog WHERE (NOT title = ? OR title = ?)',
['a', 'b']))
sq = SelectQuery(Blog, '*').where(~Q(title='a') | ~Q(title='b'))
self.assertEqual(
sq.sql(),
('SELECT * FROM blog WHERE (NOT title = ? OR NOT title = ?)',
['a', 'b']))
sq = SelectQuery(Blog, '*').where(~(Q(title='a') | Q(title='b')))
self.assertEqual(
sq.sql(),
('SELECT * FROM blog WHERE (NOT (title = ? OR title = ?))',
['a', 'b']))
# chaining?
sq = SelectQuery(Blog,
'*').where(~(Q(title='a') | Q(id=1))).where(Q(id=3))
self.assertEqual(
sq.sql(),
('SELECT * FROM blog WHERE (NOT (title = ? OR id = ?)) AND id = ?',
['a', 1, 3]))
# mix n'match?
sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1)).where(
~(Q(title='c') | Q(id=3)), title='b')
self.assertEqual(sq.sql(), (
'SELECT * FROM blog WHERE (title = ? OR id = ?) AND (NOT (title = ? OR id = ?)) AND title = ?',
['a', 1, 'c', 3, 'b']))
def test_select_with_q(self):
sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1))
self.assertEqual(
sq.sql(),
('SELECT * FROM blog WHERE (title = ? OR id = ?)', ['a', 1]))
sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1) | Q(id=3))
self.assertEqual(
sq.sql(),
('SELECT * FROM blog WHERE (title = ? OR id = ? OR id = ?)',
['a', 1, 3]))
# test simple chaining
sq = SelectQuery(Blog,
'*').where(Q(title='a') | Q(id=1)).where(Q(id=3))
self.assertEqual(
sq.sql(),
('SELECT * FROM blog WHERE (title = ? OR id = ?) AND id = ?',
['a', 1, 3]))
sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1)).where(id=3)
self.assertEqual(
sq.sql(),
('SELECT * FROM blog WHERE (title = ? OR id = ?) AND id = ?',
['a', 1, 3]))
# test chaining with Q objects
sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1)).where(
(Q(title='c') | Q(id=3)))
self.assertEqual(sq.sql(), (
'SELECT * FROM blog WHERE (title = ? OR id = ?) AND (title = ? OR id = ?)',
['a', 1, 'c', 3]))
# test mixing it all up
sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1)).where(
(Q(title='c') | Q(id=3)), title='b')
self.assertEqual(sq.sql(), (
'SELECT * FROM blog WHERE (title = ? OR id = ?) AND (title = ? OR id = ?) AND title = ?',
['a', 1, 'c', 3, 'b']))
