async def test_filter_groups_with_instances_of_same_table_in_schema():
async with database:
await create_data()
math_class = (await SchoolClass.objects.select_related([
"teachers__category__department", "students__category__department"
]).filter(
ormar.or_(
students__name="Jane",
teachers__category__name="Domestic",
students__category__name="Foreign",
)).get(name="Math"))
assert math_class.name == "Math"
assert math_class.students[0].name == "Jane"
assert len(math_class.dict().get("students")) == 2
assert math_class.teachers[
0].category.department.name == "Law Department"
assert math_class.students[
0].category.department.name == "Math Department"
classes = (await SchoolClass.objects.select_related([
"students__category__department", "teachers__category__department"
]).filter(
ormar.and_(
ormar.or_(students__name="Jane",
students__category__name="Foreign"),
teachers__category__department__name="Law Department",
)).all())
assert len(classes) == 1
assert classes[0].teachers[
0].category.department.name == "Law Department"
assert classes[0].students[
0].category.department.name == "Math Department"
def test_deeply_nested_or():
result = ormar.or_(
ormar.and_(
ormar.or_(name="aa", books__title="bb"),
ormar.or_(name="cc", books__title="dd"),
),
ormar.and_(
ormar.or_(books__year__lt=1900, books__title="11"),
ormar.or_(books__year__gt="xx", books__title="22"),
),
)
result.resolve(model_cls=Author)
assert len(result.actions) == 0
assert len(result._nested_groups) == 2
assert len(result._nested_groups[0]._nested_groups) == 2
book_prefix = result._nested_groups[0]._nested_groups[0].actions[
1].table_prefix
result_qry = str(result.get_text_clause())
expected_qry = (
f"( ( ( authors.name = 'aa' OR {book_prefix}_books.title = 'bb' ) AND "
f"( authors.name = 'cc' OR {book_prefix}_books.title = 'dd' ) ) "
f"OR ( ( {book_prefix}_books.year < 1900 OR {book_prefix}_books.title = '11' ) AND "
f"( {book_prefix}_books.year > 'xx' OR {book_prefix}_books.title = '22' ) ) )"
)
assert result_qry.replace("\n", "") == expected_qry.replace("\n", "")
def test_nested_and():
result = ormar.and_(ormar.or_(name="aa", books__title="bb"),
ormar.or_(name="cc", books__title="dd"))
result.resolve(model_cls=Author)
assert len(result.actions) == 0
assert len(result._nested_groups) == 2
book_prefix = result._nested_groups[0].actions[1].table_prefix
assert (str(result.get_text_clause()) == f"( ( authors.name = 'aa' OR "
f"{book_prefix}"
f"_books.title = 'bb' ) AND "
f"( authors.name = 'cc' OR "
f"{book_prefix}"
f"_books.title = 'dd' ) )")
def test_or_group():
result = ormar.or_(name="aa", books__title="bb")
result.resolve(model_cls=Author)
assert len(result.actions) == 2
assert result.actions[0].target_model == Author
assert result.actions[1].target_model == Book
assert (str(result.get_text_clause()) == f"( authors.name = 'aa' OR "
f"{result.actions[1].table_prefix}"
f"_books.title = 'bb' )")
def test_one_model_with_group():
result = ormar.or_(ormar.and_(year__gt=1900, title="bb"), title="uu")
result.resolve(model_cls=Book)
assert len(result.actions) == 1
assert len(result._nested_groups) == 1
def test_one_model_nested_group():
result = ormar.and_(ormar.or_(year__gt=1900, title="bb"),
ormar.or_(year__lt=1800, title="aa"))
result.resolve(model_cls=Book)
assert len(result.actions) == 0
assert len(result._nested_groups) == 2
async def test_or_filters():
async with database:
tolkien = await Author(name="J.R.R. Tolkien").save()
await Book(author=tolkien, title="The Hobbit", year=1933).save()
await Book(author=tolkien, title="The Lord of the Rings",
year=1955).save()
await Book(author=tolkien, title="The Silmarillion", year=1977).save()
sapkowski = await Author(name="Andrzej Sapkowski").save()
await Book(author=sapkowski, title="The Witcher", year=1990).save()
await Book(author=sapkowski, title="The Tower of Fools",
year=2002).save()
books = (await Book.objects.select_related("author").filter(
ormar.or_(author__name="J.R.R. Tolkien", year__gt=1970)).all())
assert len(books) == 5
books = (await Book.objects.select_related("author").filter(
ormar.or_(author__name="J.R.R. Tolkien", year__lt=1995)).all())
assert len(books) == 4
assert not any([x.title == "The Tower of Fools" for x in books])
books = (await Book.objects.select_related("author").filter(
ormar.or_(year__gt=1960, year__lt=1940)
).filter(author__name="J.R.R. Tolkien").all())
assert len(books) == 2
assert books[0].title == "The Hobbit"
assert books[1].title == "The Silmarillion"
books = (await Book.objects.select_related("author").filter(
ormar.and_(
ormar.or_(year__gt=1960, year__lt=1940),
author__name="J.R.R. Tolkien",
)).all())
assert len(books) == 2
assert books[0].title == "The Hobbit"
assert books[1].title == "The Silmarillion"
books = (await Book.objects.select_related("author").filter(
ormar.or_(
ormar.and_(year__gt=1960, author__name="J.R.R. Tolkien"),
ormar.and_(year__lt=2000, author__name="Andrzej Sapkowski"),
)).filter(title__startswith="The").all())
assert len(books) == 2
assert books[0].title == "The Silmarillion"
assert books[1].title == "The Witcher"
books = (await Book.objects.select_related("author").filter(
ormar.or_(
ormar.and_(
ormar.or_(year__gt=1960, year__lt=1940),
author__name="J.R.R. Tolkien",
),
ormar.and_(year__lt=2000, author__name="Andrzej Sapkowski"),
)).all())
assert len(books) == 3
assert books[0].title == "The Hobbit"
assert books[1].title == "The Silmarillion"
assert books[2].title == "The Witcher"
books = (await Book.objects.select_related("author").exclude(
ormar.or_(
ormar.and_(year__gt=1960, author__name="J.R.R. Tolkien"),
ormar.and_(year__lt=2000, author__name="Andrzej Sapkowski"),
)).filter(title__startswith="The").all())
assert len(books) == 3
assert not any(
[x.title in ["The Silmarillion", "The Witcher"] for x in books])
books = (await Book.objects.select_related("author").filter(
ormar.or_(
ormar.and_(year__gt=1960, author__name="J.R.R. Tolkien"),
ormar.and_(year__lt=2000, author__name="Andrzej Sapkowski"),
title__icontains="hobbit",
)).filter(title__startswith="The").all())
assert len(books) == 3
assert not any([
x.title in ["The Tower of Fools", "The Lord of the Rings"]
for x in books
])
books = (await Book.objects.select_related("author").filter(
ormar.or_(year__gt=1980, year__lt=1910)
).filter(title__startswith="The").limit(1).all())
assert len(books) == 1
assert books[0].title == "The Witcher"
books = (await Book.objects.select_related("author").filter(
ormar.or_(year__gt=1980, author__name="Andrzej Sapkowski")
).filter(title__startswith="The").limit(1).all())
assert len(books) == 1
assert books[0].title == "The Witcher"
books = (await Book.objects.select_related("author").filter(
ormar.or_(year__gt=1980, author__name="Andrzej Sapkowski")
).filter(title__startswith="The").limit(1).offset(1).all())
assert len(books) == 1
assert books[0].title == "The Tower of Fools"
books = (await Book.objects.select_related("author").filter(
ormar.or_(year__gt=1980, author__name="Andrzej Sapkowski")
).filter(title__startswith="The"
).limit(1).offset(1).order_by("-id").all())
assert len(books) == 1
assert books[0].title == "The Witcher"
with pytest.raises(QueryDefinitionError):
await Book.objects.select_related("author").filter("wrong").all()
books = await tolkien.books.filter(
ormar.or_(year__lt=1940, year__gt=1960)).all()
assert len(books) == 2
books = await tolkien.books.filter(
ormar.and_(ormar.or_(year__lt=1940, year__gt=1960),
title__icontains="hobbit")).all()
assert len(books) == 1
assert tolkien.books[0].title == "The Hobbit"
books = (await Book.objects.select_related("author").filter(
ormar.or_(author__name="J.R.R. Tolkien")).all())
assert len(books) == 3
books = (await Book.objects.select_related("author").filter(
ormar.or_(
ormar.and_(author__name__icontains="tolkien"),
ormar.and_(author__name__icontains="sapkowski"),
)).all())
assert len(books) == 5