async def test_filter_by_aggregation_field_coalesce(self):
        await self.model.create(chars="aaa", nullable="null")
        await self.model.create(chars="bbb")
        objs = await self.model.annotate(null=Coalesce("nullable", "null")).filter(null="null")

        self.assertEqual(len(objs), 2)
        self.assertSetEqual({(o.chars, o.null) for o in objs}, {("aaa", "null"), ("bbb", "null")})
Exemple #2
0
    async def test_filter_by_aggregation_field_coalesce(self):
        await Tournament.create(name="1", desc="demo")
        await Tournament.create(name="2")

        tournaments = await Tournament.annotate(
            clean_desc=Coalesce("desc", "demo")).filter(clean_desc="demo")
        self.assertEqual(len(tournaments), 2)
        self.assertSetEqual({(t.name, t.clean_desc)
                             for t in tournaments}, {("1", "demo"),
                                                     ("2", "demo")})
Exemple #3
0
    async def test_filter_by_aggregation_field_comparison_coalesce_numeric(self):
        await IntFields.create(intnum=3, intnum_null=10)
        await IntFields.create(intnum=1, intnum_null=4)
        await IntFields.create(intnum=2)

        ints = await IntFields.annotate(clean_intnum_null=Coalesce("intnum_null", 0)).filter(
            clean_intnum_null__gt=0
        )
        self.assertEqual(len(ints), 2)
        self.assertSetEqual({i.clean_intnum_null for i in ints}, {10, 4})
Exemple #4
0
    async def test_filter_by_aggregation_field_coalesce_numeric(self):
        await IntFields.create(intnum=1, intnum_null=10)
        await IntFields.create(intnum=4)

        ints = await IntFields.annotate(
            clean_intnum_null=Coalesce("intnum_null", 0)
        ).filter(clean_intnum_null__in=(0, 10))
        self.assertEqual(len(ints), 2)
        self.assertSetEqual(
            {(i.intnum_null, i.clean_intnum_null)
             for i in ints}, {(None, 0), (10, 10)})
    async def test_func_then(self):
        category = Case(When(intnum__gte=8, then=Coalesce("intnum_null", 10)),
                        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`>=8 THEN COALESCE(`intnum_null`,10) ELSE 'default' END `category` FROM `intfields`"
        else:
            expected_sql = 'SELECT "intnum" "intnum",CASE WHEN "intnum">=8 THEN COALESCE("intnum_null",10) ELSE \'default\' END "category" FROM "intfields"'
        self.assertEqual(sql, expected_sql)
Exemple #6
0
async def run():
    await Tortoise.init(db_url="mysql://*****:*****@localhost:55555/test_demo", 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(10):
        team = Team(name=f"Team {(i + 1)}")
        await team.save()
        participants.append(team)

    await event.participants.add(*participants)

    # await event.participants.add(participants[0], participants[1])

    print(await Tournament.all().annotate(events_count=Count("events")).filter(events_count__lte=3).values())
    print(
        await Tournament
        .annotate(events_count_with_filter=Count("events", _filter=Q(name="New Tournament")))
        .filter(events_count_with_filter__gte=0).values()
    )

    print(await Event.annotate(lowest_team_id=Count("participants__id")).values())

    print(await Tournament.annotate(events_count=Count("events")).order_by("events_count"))

    print(await Event.annotate(tournament_test_id=Sum("tournament__id")).first())

    print(
        await Tournament.annotate(clean_desciption=Coalesce("desc", "hehe")).values()
    )

    print(
        await Tournament.annotate(trimmed_name=Trim("name")).values()
    )

    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").values())
    print(await Tournament.annotate(name_lo=Upper("name")).filter(name_lo="NEW TOURNAMENT").values())

    print()
 async def test_concat_functions(self):
     author = await Author.create(name="Some One")
     await Book.create(name="Physics Book",
                       author=author,
                       rating=4,
                       subject="physics ")
     await Book.create(name="Mathematics Book",
                       author=author,
                       rating=3,
                       subject=" mathematics")
     await Book.create(name="No-subject Book", author=author, rating=3)
     ret = (await Book.all().annotate(long_info=Max(
         Concat("name", "(", Coalesce(Trim("subject"), "others"), ")"))
                                      ).values("long_info"))
     self.assertEqual(ret, [{"long_info": "Physics Book(physics)"}])
Exemple #8
0
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 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"))