count from multiple tables in a single query?

[Abraham Varricatt]

Hello,

Is it possible to get the count of entries from multiple tables in a single query call? I'm looking at the official docs on aggregation and I can't find anything. For example assume I have the following 2 tables,

class Author(models.Model):
    name = models.CharField(max_length=100)
    age = models.IntegerField()

class Publisher(models.Model):
    name = models.CharField(max_length=300)
    num_awards = models.IntegerField()

If I want to get the total count from both tables it can be done like this,

author_count = Author.objects.count()
publisher_count = Publisher.objects.count()

My concern is that this results in two different queries to the database. Can it be done with a single query call?


Yours,
Abraham V.

[James Schneider]

If I want to get the total count from both tables it can be done like this,

author_count = Author.objects.count()
publisher_count = Publisher.objects.count()

My concern is that this results in two different queries to the database. Can it be done with a single query call?

Probably not with the standard ORM and the model setup you provided with no relation between the tables. You'll likely have to drop down to raw SQL. I'd do testing. I doubt you'll have much of a performance gain by combining the two queries in to one since you are just doing a count and both tables have unique indexes, aside from saving the overhead of one SQL call (not the actual query itself). Two calls would keep the code simple and portable, which to me is more important than a marginal gain in performance.

-James

[Matthew Pava]

Hi Abraham,

If the models are related, you can use double underscore notation with the Count aggregate function.

If the models are unrelated, then I’m fairly certain that you can only use separate queries to get your results.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/5ffdbc1a-fb84-4bff-a711-eaad77c3ae15%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Abraham Varricatt]

Hello James/Matthew,

I was afraid to hear that. The models are unrelated and I do not want to take the trouble of dropping out of the ORM. Since it's a web-service I'm building, a work-around is to cache results, but I was challenged to find a single DB call solution and wanted to be sure that it wouldn't be possible with the ORM.

Thanks for replying!

Looking into caches,
Abraham V.

To post to this group, send email to djang...@googlegroups.com.

[Alceu Rodrigues de Freitas Junior]

Hi James,

I'm curious... why dropping down from the ORM and doing a single query wouldn't be portable?

I understand that it would happen if you use some kind of stored procedure in the DB, but I guess a single ANSI SQL would do it.

Thanks!

- Alceu

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.

To post to this group, send email to django...@googlegroups.com.

Visit this group at https://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CA%2Be%2BciVxVrzLuF7ASWiC34A2MenWvJd3P1GXsP%2Bi-mYQ9FuVvg%40mail.gmail.com.

[James Schneider]

On Wed, May 31, 2017 at 3:01 PM, Alceu Rodrigues de Freitas Junior <alceu.fr...@gmail.com> wrote:

Hi James,

I'm curious... why dropping down from the ORM and doing a single query wouldn't be portable?

I understand that it would happen if you use some kind of stored procedure in the DB, but I guess a single ANSI SQL would do it.

Thanks!

- Alceu

I might have painted that statement with too broad of a brush, but the underlying intention is correct. Sticking with the ORM ensures consistent behavior across all supported database backends. In the case of a simple COUNT(*) query, even with multiple tables involved, the SQL statement is likely portable across any of the included backends. 

My intention was to say that using the ORM will better guarantee that the call is portable, not necessarily saying that raw SQL cannot or would not be. Of course, many queries can be made that would work in any SQL-compliant RDBMS. 

At any rate, a quick Google showed that the OP is likely stuck with two queries anyway. The SO posts, etc. show sub-SELECT statements in use in order to get the result returned as a single data set. 

-James

[Melvyn Sopacua]

On Wednesday 31 May 2017 12:13:48 'Abraham Varricatt' via Django users wrote:

 

> If I want to get the total count from both tables it can be done like

> this,

>

> author_count = Author.objects.count()

> publisher_count = Publisher.objects.count()

>

> My concern is that this results in two different queries to the

> database. Can it be done with a single query call?

 

Why is that a concern? This isn't the kind of thing to optimize. Query optimization is about loops and relations. Not about getting information you need - it comes at a price. Have you timed what you could save?

--

Melvyn Sopacua