def test_q_complex_int3(self):
q = Q(Q(id__lt=5, id__gt=50, join_type="OR"),
join_type="AND",
intnum=80)
r = q.resolve(IntFields, IntFields._meta.basequery)
self.assertEqual(r.where_criterion.get_sql(),
'"intnum"=80 AND ("id"<5 OR "id">50)')
async def test_filter_not_with_or(self):
await Tournament.create(name="0")
await Tournament.create(name="1")
await Tournament.create(name="2")
tournaments = await Tournament.filter(Q(name="1") | ~Q(name="2"))
self.assertEqual(len(tournaments), 2)
self.assertSetEqual({t.name for t in tournaments}, {"0", "1"})
async def reply_parser(client, oid, reply_type):
if cache := await reply_cache.get_or_none(
None,
query := Q(Q(oid=oid), Q(reply_type=reply_type)),
Q(created__gte=timezone.now() - reply_cache.timeout),
):
logger.info(f"拉取评论缓存: {cache.created}")
r = cache.content
def test_q_complex_char3(self):
q = Q(~Q(char__lt=5, char__gt=50, join_type="OR"),
join_type="AND",
char_null=80)
r = q.resolve(CharFields, CharFields._meta.basequery)
self.assertEqual(
r.where_criterion.get_sql(),
"\"char_null\"='80' AND NOT (\"char\"<'5' OR \"char\">'50')",
)
async def list(self, ctx, *, search):
tags = await Tag.filter(
Q(key__icontains=search) | Q(author__icontains=search)
).all()
rows = []
for tag in tags:
rows.append([tag.key, tag.value, tag.author])
table = tabulate(rows, headers=["Key", "Value", "Author"], tablefmt="simple")
await ctx.send(f"```diff\n{table}\n```")
async def test_filter_by_aggregation_field_with_or_not_reversed(self):
tournament = await Tournament.create(name="0")
await Tournament.create(name="1")
await Tournament.create(name="2")
await Event.create(name="1", tournament=tournament)
tournaments = await Tournament.annotate(
events_count=Count("events")
).filter(~(Q(name="2") | Q(events_count=1)))
self.assertEqual(len(tournaments), 1)
self.assertSetEqual({t.name for t in tournaments}, {"1"})
async def test_filter_by_aggregation_field_with_and_as_two_nodes(self):
tournament = await Tournament.create(name="0")
tournament_second = await Tournament.create(name="1")
await Event.create(name="1", tournament=tournament)
await Event.create(name="2", tournament=tournament_second)
tournaments = await Tournament.annotate(
events_count=Count("events")
).filter(Q(events_count=1) & Q(name="0"))
self.assertEqual(len(tournaments), 1)
self.assertEqual(tournaments[0].id, tournament.id)
async def lookup_alliance(target: str or int) -> int:
"""
Search through the database for a given alliance.
"""
try:
fetched = await models.AlliancesIndex.get_or_none(id=int(target))
except ValueError:
fetched = await models.AlliancesIndex.get_or_none(
Q(name=target) | Q(acronym=target))
return fetched.id if fetched else None
async def lookup_nation(target: str or int) -> int:
"""
Search through the database for a given nation.
"""
try:
fetched = await models.NationsIndex.get_or_none(id=int(target))
except ValueError:
fetched = await models.NationsIndex.get_or_none(
Q(name=target) | Q(leader=target))
return fetched.id if fetched else None
async def test_aggregation_with_filter(self):
obj1 = await self.model.create(chars="aaa")
await self.model.create(chars="bbb", fk=obj1)
await self.model.create(chars="ccc", fk=obj1)
obj = (await self.model.filter(chars="aaa").annotate(
all=Count("fkrev", _filter=Q(chars="aaa")),
one=Count("fkrev", _filter=Q(fkrev__chars="bbb")),
no=Count("fkrev", _filter=Q(fkrev__chars="aaa")),
).first())
self.assertEqual(obj.all, 2)
self.assertEqual(obj.one, 1)
self.assertEqual(obj.no, 0)
async def get_employees(active: Optional[bool] = None,
intern: Optional[bool] = None):
q_object = Q()
if active is not None:
q_object = q_object & Q(active=active)
if intern is not None:
interns = ['stagiair', 'stagiaire', 'intern']
if intern:
q_object = q_object & Q(function__in=interns)
else:
q_object = q_object & ~Q(function__in=interns)
return await Employee_Pydantic.from_queryset(Employee.filter(q_object))
async def test_aggregation_with_filter(self):
tournament = await Tournament.create(name="New Tournament")
await Event.create(name="Event 1", tournament=tournament)
await Event.create(name="Event 2", tournament=tournament)
await Event.create(name="Event 3", tournament=tournament)
tournament_with_filter = (await Tournament.all().annotate(
all=Count("events", _filter=Q(name="New Tournament")),
one=Count("events", _filter=Q(events__name="Event 1")),
two=Count("events", _filter=Q(events__name__not="Event 1")),
).first())
self.assertEqual(tournament_with_filter.all, 3)
self.assertEqual(tournament_with_filter.one, 1)
self.assertEqual(tournament_with_filter.two, 2)
async def test_filtering(self):
tournament = Tournament(name="Tournament")
await tournament.save()
second_tournament = Tournament(name="Tournament 2")
await second_tournament.save()
event_first = Event(name="1", tournament=tournament)
await event_first.save()
event_second = Event(name="2", tournament=second_tournament)
await event_second.save()
event_third = Event(name="3", tournament=tournament)
await event_third.save()
event_forth = Event(name="4", tournament=second_tournament)
await event_forth.save()
team_first = Team(name="First")
await team_first.save()
team_second = Team(name="Second")
await team_second.save()
await team_first.events.add(event_first)
await event_second.participants.add(team_second)
found_events = (await Event.filter(
Q(pk__in=[event_first.pk, event_second.pk])
| Q(name="3")).filter(participants__not=team_second.id
).order_by("name",
"tournament_id").distinct())
self.assertEqual(len(found_events), 2)
self.assertEqual(found_events[0].pk, event_first.pk)
self.assertEqual(found_events[1].pk, event_third.pk)
await Team.filter(events__tournament_id=tournament.id
).order_by("-events__name")
await Tournament.filter(events__name__in=["1", "3"]).distinct()
teams = await Team.filter(name__icontains="CON")
self.assertEqual(len(teams), 1)
self.assertEqual(teams[0].name, "Second")
teams = await Team.filter(name__iexact="SeCoNd")
self.assertEqual(len(teams), 1)
self.assertEqual(teams[0].name, "Second")
tournaments = await Tournament.filter(
events__participants__name__startswith="Fir")
self.assertEqual(len(tournaments), 1)
self.assertEqual(tournaments[0], tournament)
async def test_filter_not(self):
await Tournament.create(name="0")
await Tournament.create(name="1")
tournaments = await Tournament.filter(~Q(name="1"))
self.assertEqual(len(tournaments), 1)
self.assertEqual(tournaments[0].name, "0")
def test_q_compound_and(self):
q1 = Q(moo="cow")
q2 = Q(moo="bull")
q = q1 & q2
self.assertEqual(q1.children, ())
self.assertEqual(q1.filters, {"moo": "cow"})
self.assertEqual(q1.join_type, "AND")
self.assertEqual(q2.children, ())
self.assertEqual(q2.filters, {"moo": "bull"})
self.assertEqual(q2.join_type, "AND")
self.assertEqual(q.children, (q1, q2))
self.assertEqual(q.filters, {})
self.assertEqual(q.join_type, "AND")
async def test_q_object_related_query(self):
tournament_first = await Tournament.create(name="0")
tournament_second = await Tournament.create(name="1")
event = await Event.create(name="1", tournament=tournament_second)
await Event.create(name="1", tournament=tournament_first)
fetched_event = await Event.filter(tournament=tournament_second
).first()
self.assertEqual(fetched_event.pk, event.pk)
fetched_event = await Event.filter(Q(tournament=tournament_second)
).first()
self.assertEqual(fetched_event.pk, event.pk)
fetched_event = await Event.filter(Q(tournament=tournament_second.id)
).first()
self.assertEqual(fetched_event.pk, event.pk)
async def run():
await Tortoise.init(db_url="sqlite://:memory:", modules={"models": ["__main__"]})
await Tortoise.generate_schemas()
tournament = Tournament(name="Tournament")
await tournament.save()
second_tournament = Tournament(name="Tournament 2")
await second_tournament.save()
event_first = Event(name="1", tournament=tournament)
await event_first.save()
event_second = await Event.create(name="2", tournament=second_tournament)
await Event.create(name="3", tournament=tournament)
await Event.create(name="4", tournament=second_tournament)
await Event.filter(tournament=tournament)
team_first = Team(name="First")
await team_first.save()
team_second = Team(name="Second")
await team_second.save()
await team_first.events.add(event_first)
await event_second.participants.add(team_second)
print(
await Event.filter(Q(id__in=[event_first.id, event_second.id]) | Q(name="3"))
.filter(participants__not=team_second.id)
.order_by("tournament__id")
.distinct()
)
print(await Team.filter(events__tournament_id=tournament.id).order_by("-events__name"))
print(
await Tournament.filter(events__name__in=["1", "3"])
.order_by("-events__participants__name")
.distinct()
)
print(await Team.filter(name__icontains="CON"))
print(await Tournament.filter(events__participants__name__startswith="Fir"))
print(await Tournament.filter(id__icontains=1).count())
async def test_q_object_backward_related_query(self):
await Tournament.create(name="0")
tournament = await Tournament.create(name="Tournament")
event = await Event.create(name="1", tournament=tournament)
fetched_tournament = await Tournament.filter(events=event.event_id
).first()
self.assertEqual(fetched_tournament.id, tournament.id)
fetched_tournament = await Tournament.filter(Q(events=event.event_id)
).first()
self.assertEqual(fetched_tournament.id, tournament.id)
async def test_q_object_when(self):
category = Case(When(Q(intnum__gt=2, intnum__lt=8), then="middle"),
default="default")
sql = IntFields.all().annotate(category=category).values(
"intnum", "category").sql()
dialect = self.db.schema_generator.DIALECT
if dialect == "mysql":
expected_sql = "SELECT `intnum` `intnum`,CASE WHEN `intnum`>2 AND `intnum`<8 THEN 'middle' ELSE 'default' END `category` FROM `intfields`"
else:
expected_sql = 'SELECT "intnum" "intnum",CASE WHEN "intnum">2 AND "intnum"<8 THEN \'middle\' ELSE \'default\' END "category" FROM "intfields"'
self.assertEqual(sql, expected_sql)
async def test_filter_by_aggregation_field_with_or_as_one_node(self):
tournament = await Tournament.create(name="0")
await Tournament.create(name="1")
await Tournament.create(name="2")
await Event.create(name="1", tournament=tournament)
tournaments = await Tournament.annotate(events_count=Count("events")
).filter(
Q(events_count=1,
name="2",
join_type=Q.OR))
self.assertEqual(len(tournaments), 2)
self.assertSetEqual({t.name for t in tournaments}, {"0", "2"})
async def run():
await Tortoise.init(db_url="sqlite://:memory:",
modules={"models": ["__main__"]})
await Tortoise.generate_schemas()
tournament = await Tournament.create(name="New Tournament", desc="great")
await tournament.save()
await Tournament.create(name="Second tournament")
await Tournament.create(name=" final tournament ")
await Event(name="Without participants",
tournament_id=tournament.id).save()
event = Event(name="Test", tournament_id=tournament.id)
await event.save()
participants = []
for i in range(2):
team = Team(name=f"Team {(i + 1)}")
await team.save()
participants.append(team)
await event.participants.add(participants[0], participants[1])
await event.participants.add(participants[0], participants[1])
print(await Tournament.all().annotate(events_count=Count("events")
).filter(events_count__gte=1))
print(await
Tournament.all().annotate(events_count_with_filter=Count(
"events", _filter=Q(name="New Tournament"))
).filter(events_count_with_filter__gte=1))
print(await Event.filter(id=event.id).first().annotate(
lowest_team_id=Min("participants__id")))
print(await Tournament.all().annotate(events_count=Count("events")
).order_by("events_count"))
print(await Event.all().annotate(tournament_test_id=Sum("tournament__id")
).first())
print(await Tournament.annotate(clean_desciption=Coalesce("desc", "")
).filter(clean_desciption=""))
print(await Tournament.annotate(trimmed_name=Trim("name")
).filter(trimmed_name="final tournament"))
print(await
Tournament.annotate(name_len=Length("name")
).filter(name_len__gt=len("New Tournament")))
print(await Tournament.annotate(name_lo=Lower("name")
).filter(name_lo="new tournament"))
print(await Tournament.annotate(name_lo=Upper("name")
).filter(name_lo="NEW TOURNAMENT"))
def query_filter_builder(cls, user_attribute: str, value: Any) -> List[Q]:
"""Build a filter with Q function and attributes separated
by following keywords: Or, And, AND, OR\n
Args:
user_attribute (str): attributes
ex: first_nameOrlast_nameAndemail\n
value (Any): value that equals attribute\n
Returns:
List[Q]: List of Q functions according to attributes and value\n
"""
attributes = re.compile(r"Or|And|OR|AND").split(user_attribute)
query_builder: list[Q] = []
for attr in attributes:
attr = attr.strip().lower()
cond = {f"{attr}__icontains": value}
if user_attribute.split(attr)[0].lower().endswith("or"):
last_query: Q = query_builder.pop()
query_builder.append(Q(last_query, Q(**cond), join_type="OR"))
elif attr != "":
query_builder = [*query_builder, Q(**cond)]
return query_builder
def test_q_partial_or(self):
q = Q(join_type="OR", moo="cow")
self.assertEqual(q.children, ())
self.assertEqual(q.filters, {"moo": "cow"})
self.assertEqual(q.join_type, "OR")
def test_q_bad_join_type(self):
with self.assertRaisesRegex(OperationalError,
"join_type must be AND or OR"):
Q(join_type=3)
def test_q_basic(self):
q = Q(moo="cow")
self.assertEqual(q.children, ())
self.assertEqual(q.filters, {"moo": "cow"})
self.assertEqual(q.join_type, "AND")
def test_q_notq(self):
with self.assertRaisesRegex(OperationalError,
"All ordered arguments must be Q nodes"):
Q(Q(), 1)
def test_q_compound_and_notq(self):
with self.assertRaisesRegex(OperationalError,
"AND operation requires a Q node"):
Q() & 2 # pylint: disable=W0106
def test_q_multiple_or2(self):
q = Q(join_type="OR", id=8, intnum=80)
r = q.resolve(IntFields, IntFields._meta.basequery)
self.assertEqual(r.where_criterion.get_sql(), '"id"=8 OR "intnum"=80')
def test_q_with_blank_or3(self):
q = Q() | Q(id__gt=5)
r = q.resolve(CharFields, CharFields._meta.basequery)
self.assertEqual(r.where_criterion.get_sql(), '"id">5')
def test_q_with_blank_and(self):
q = Q(Q(id__gt=5), Q(), join_type=Q.AND)
r = q.resolve(CharFields, CharFields._meta.basequery)
self.assertEqual(r.where_criterion.get_sql(), '"id">5')
