Is there any way to get total count of table row and table data in single Query

Question

The problem is, to get count and table data, I have to hit the database two times in Django. For example:

count = queryset.count() # To get count
data = queryset.values('columns') # To get data

Is there any way to get data in the single query. One solution is to use len() function, but it is not good for a bigger table to load in RAM.

In mysql, I got this, But how to execute through Django ORM

SELECT t1.count, id FROM table1, (select count(*) as count FROM table1) as t1 limit 10;

Any help will be appreciated.

Possible duplicate of django most efficient way to count same field values in a query — floydya
– floydya, Commented Oct 4, 2018 at 7:00

Sree · Accepted Answer · 2018-10-04 06:23:43Z

0

I mean len should just count what is already in memory if you get the data first, so it should be better than two queries to database.

data = queryset.values('columns') # To get data
count = len(data) # To get count from database records in memory

Anyway, for direct database queries without the model layer from Django docs:

from django.db import connection

def my_custom_sql(self):
    with connection.cursor() as cursor:
        cursor.execute("SELECT t1.count, id FROM table1, (select count(*) as count FROM table1) as t1 limit 10")
        row = dictfetchall(cursor)

    return row

edited Oct 4, 2018 at 6:23

answered Oct 4, 2018 at 6:17

Sree

3601 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

vermanil Over a year ago

yes, @stree len() had solved my problem, but It will load all database records in memory which is an issue for a bigger table.

Collectives™ on Stack Overflow

Is there any way to get total count of table row and table data in single Query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related