Tortoise-orm: queryset.count() support field and distinct parameter

[yinkh]

Filter model with fk field then use distinct().count() get wrong count num
I have two model like:

class Client(Model):
    uuid = fields.UUIDField(pk=True, description='ID')
    name = fields.CharField(max_length=255, description='name')

class Ability(Model):
    id = fields.UUIDField(pk=True, description='ID')
    client = fields.ForeignKeyField(model_name='models.Client', related_name='client_abilities',
                                    description='client')
    type = fields.SmallIntField(description='type ')
    name = fields.CharField(max_length=255, description='name')

then i create there client and 6 ability like:

c1 = Client(name='c1')
c2 = Client(name='c2')
c3 = Client(name='c3')

a1 = Ability(client=c1, type=1, name='a1')
a2 = Ability(client=c1, type=1, name='a2')
a3 = Ability(client=c2, type=1, name='a3')
a4 = Ability(client=c2, type=1, name='a4')
a5 = Ability(client=c3, type=1, name='a5')
a6 = Ability(client=c3, type=1, name='a6')

then I filter Client like:

await Client.filter(client_abilities__type=1).distinct()it will return [c1,c2,c3]
await Client.filter(client_abilities__type=1).distinct().count() it will return 6, but the expected num is 3.

then i print sql for this two orm statement:
Client.filter(client_abilities__type=1).distinct().sql() get something like:

SELECT DISTINCT `client`.`uuid`,`client`.`name`  FROM `client` LEFT OUTER JOIN `ability` ON `client`.`uuid`=`ability`.`client_id` WHERE `ability`.`type`=1

exec the sql i get this

uuid    name
5afaf55a-e175-4a38-8b11-205d4d99ad5e    demo
892b2ee2-cb5a-498b-a408-6c431553b721    demo
db94723c-ce25-4fc4-bf90-0f23800c2aae    demo

await Client.filter(client_abilities__type=1).distinct().count().sql() get something like:

SELECT COUNT(*) FROM `client` LEFT OUTER JOIN `ability` ON `client`.`uuid`=`ability`.`client_id` WHERE `client`.`delete`=false AND `ability`.`type`=1

exec the sql i get this

COUNT(*)
6

Describe the solution you'd like
exce sql:

SELECT COUNT(DISTINCT `client`.`uuid`) FROM `client` LEFT OUTER JOIN `ability` ON `client`.`uuid`=`ability`.`client_id` WHERE `client`.`delete`=false AND `ability`.`type`=1

i can get right count:

COUNT(DISTINCT `client`.`uuid`)
3

so,i want tortoise-orm support something like(if this is no better way):
Client.filter(**filters).distinct().count('uuid', distinct=True)

Describe alternatives you've considered
I think the key point is
venv/Lib/site-packages/tortoise/queryset.py Line 899

    def _make_query(self) -> None:
        self.query = copy(self.model._meta.basequery)
        self.resolve_filters(
            model=self.model,
            q_objects=self.q_objects,
            annotations=self.annotations,
            custom_filters=self.custom_filters,
        )
        self.query._select_other(Count("*"))

i try change self.query._select_other(Count("*")) to self.query._select_other(Count(field)).distinct(), but it not work,i dont know how to change uuid to client`.`uuid

looking forward to your reply! thanks

[asitm9]

Queryset.count() support field and distinct parameter

To enable this feature the following changes are required:

    if param and distinct:
        self.query._select_other(Count(Field(param)).distinct())
    self.query._select_other(Count("*"))

The query would look like Client.filter(**filters).count('uuid', distinct=True)

I have recently started exploring the tortoise-orm code, if we have any better alternative to pass the argument to the count method, please let me know.

I can modify the test code for the change & can create a PR.

[jdgjxxchy]

Queryset.count() support field and distinct parameter

To enable this feature the following changes are required:

    if param and distinct:
        self.query._select_other(Count(Field(param)).distinct())
    self.query._select_other(Count("*"))

The query would look like Client.filter(**filters).count('uuid', distinct=True)

I have recently started exploring the tortoise-orm code, if we have any better alternative to pass the argument to the count method, please let me know.

I can modify the test code for the change & can create a PR.

Oh! That's exactly what I want!
It happened to me days ago EXACTLY what this issue talk about.
Looking forward to a new release solving this.

[neonardo1]

This would be wonderful to get implemented.

[long2ice]

You can just use annotate instead, like:

await Client.filter(client_abilities__type=1).annotate(count=Count("uuid", distinct=True)).values("count")

[neonardo1]

You can just use annotate instead, like:

await Client.filter(client_abilities__type=1).annotate(count=Count("uuid", distinct=True)).values("count")

Works beautifully, thanks!