CODEWITHSUNDEEP
djangodjango-modelsdjango-orm
Romeo Mihalcea
Romeo Mihalcea

Reputation: 10252

Django selecting a count

I'm fairly new to Django and I want to get the total clients a user has (my users will be selling something through my website) so I have a table called orders where I keep the user_id of the user who purchased and the product_id being purchased. Each product id is related to a product that has a user (the one I'm doing the query for):

select COUNT(distinct(o.user_id)) as total_clients from `order` o 
    inner join product p on p.id=o.product_id where p.user_id=32;

User id 32 is logged in and I want to show him how many clients purchased his products.

I want to do this in a get instead of filter as it makes more sense.

Here's what my logic tells me to write:

clients = Order.objects.get(
    status = Order.COMPLETED,
    product__user = self.user
).annotate(
    total_clients = Count( 'user', distinct = True )
)

return clients.total_clients

.and here's what it returns:

Exception Type: MultipleObjectsReturned
Exception Value:  get() returned more than one Order -- it returned 2!

I could probably be running a query instead of using the orm but I don't want that. This is a rather simple query that I'm sure Django is handling very easily and I want to keep away from writing strings in my code.

Here's my model setup:

class UserProfile( models.Model ):
    user = models.OneToOneField( User, related_name = 'profile' )
    ....


    def get_total_clients( self ):
        clients = Order.objects.get(
            status = Order.COMPLETED,
            product__user = self.user
        ).annotate(
            total_clients = Count( 'user', distinct = True )
        )

        return clients.total_clients


class Order( models.Model ):
    PENDING = 0
    COMPLETED = 1
    REFUNDED = 2

    STATUS_CHOICES = (
        (PENDING, "Pending"),
        (COMPLETED, "Completed"),
        (REFUNDED, "Refunded"),
    )

    user = models.ForeignKey( User, related_name = "orders" )
    product = models.ForeignKey( Product, related_name = "orders" )
    amount = models.DecimalField( max_digits = 6, decimal_places = 2, default = 0.00 )
    status = models.SmallIntegerField(
        choices = STATUS_CHOICES, default = PENDING, db_index = True
    )


class Product( models.Model ):
    user = models.ForeignKey( User, related_name = "unlocks" )
    name = models.CharField( max_length = 255 )

Upvotes: 1

Views: 197

Answers (2)

sobolevn
sobolevn

Reputation: 18070

If i got you right, you are intrested in the amount of consumers who ordered a product from a user. Some docs, that may be helpful.

My suggestion is:

result = Product.objects.distinct().filter(
    # or any query:
    orders__status=Order.COMPLETED,
    user=default_user

).aggregate( # see the difference between aggregate() and annotate()
    total_users=Count('orders__user', distinct=True)
)

I expect the result to be: {'total_users': NUM_OF_USERS}

In raw SQL it will be something like:

SELECT DISTINCT COUNT(DISTINCT "main_order"."user_id") AS 
"total_users" FROM "main_product" INNER JOIN "main_order" ON (     
"main_product"."id" = "main_order"."product_id" ) WHERE 
("main_order"."status" = STATUS AND "main_product"."user_id" = USER_ID)

Is that what you wanted?

Upvotes: 0

stellasia
stellasia

Reputation: 5612

Django queryset have a count method: 

clients = Order.objects.filter(
                            status = Order.COMPLETED,
                            product__user = self.user
             )
return clients.count()

Upvotes: 1

Related Questions