Python - Django Rest Framework - Count total row from tables in the database with a single call

Question

I just switched to python and Django-rest-framework from java spring-boot, and I have a question below

Let's say I have some tables:

class table1(CommonModel):
class table2(CommonModel):
class table3(CommonModel):
class table4(CommonModel):
....

And I have an API to return total rows in these tables:

{
    "table1": 10,
    "table2": 10,
    "table3": 10,
    "table4": 10,
    ....
}

My code so far is to count the total on each table with
Model.objects.all().count() or Model.objects.filter(some_filter).count()

And the problem here is obvious, if I have 10 tables, I will have to call 10 count query, which I think is not a good way to go because it spams too many queries to the database.

I want to limit the number of the query to the database. The best would be to return the row count for all tables in a single query. I have looking for a solution like creating a custom query or so but nothing seems to solve my issue. Edit: I'm using Postgresql database

@DeepakTripathi I've updated the description, I'm using Postgres — Tan Sang, Oct 27 '22 at 07:36
For counting rows of all tables you need to query the system table as shown in this link `https://stackoverflow.com/a/2611745/11622508` — Deepak Tripathi, Oct 27 '22 at 07:53
@DeepakTripathi this solution seems to be database-wise. Instead, I'm looking for something more like a query or function with filters and returns some sort of a custom dto — Tan Sang, Oct 28 '22 at 02:57

score 0 · Answer 1 · answered Oct 28 '22 at 10:32

0

class TestAPIVIew(ListAPIView):
    permission_classes = [AllowAny]

    def get(self, request, *args, **kwargs):
        data = dict()
        installed_apps = settings.INSTALLED_APPS[5:-1]

# INSTALLED_APPS are getting apps name in a array. to excludes some of the apps i have used [5:-1]
        for app in installed_apps:
            if '.' in app:
                app_names_list = app.split('.')
                app_names_list_length = len(app_names_list)
                app = app.split('.')[app_names_list_length - 1]
            app_config = apps.get_app_config(app)
            for model in app_config.get_models():
                counter = model.objects.count()
                data[model.__name__] = counter
        return Response(data=data)

answered Oct 28 '22 at 10:32

PLABON DATTA

167
1
4

I don't think this solution reduces the number of queries call to the database because it still uses the for loop. But it does reduce the line of code though. I'm looking for something like a custom query where you count everything in a single query and return some sort of a custom data object – Tan Sang Nov 07 '22 at 02:41
As far as I know, Django ORM doesn't support anything like this. You can use the code given below in your code. This will return all the models in your database, the names of all the models and the number of rows in the model. ``` from django.db import connection ``` ``` cursor = connection.cursor() data_dict = dict() cursor.execute('SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema = "older_user"') data = cursor.fetchall() for item in data: data_dict[item[0]] = item[1] ``` – PLABON DATTA Nov 14 '22 at 08:38

Python - Django Rest Framework - Count total row from tables in the database with a single call

1 Answers1