Joining 2 tables with Count in MySQL and Django

Question

Part 1: MySQL

From a MySQL database how can I join the following two counts

SELECT COUNT(activity_id) as activities, user_id FROM Activity 
    WHERE activity_completed_time IS NOT NULL
    AND group = somevalue
    GROUP BY user_id;

SELECT COUNT(id) as comments, author_id FROM Comment
    WHERE group = somevalue
    GROUP BY author_id;

These queries return two separate tables but I should be able to join them on Activity.user_id = Comment.author_id.

The first query gives:

activities | user_id

The second query gives:

comments | author_id

What I want to return is:

user_id | activities | comments

I also need 0 values for activities and comments where they only appear in 1 table (NULL would work fine).

Part 2: Django

Is it possible to then do this in a single query in Django without using raw queries?

Would it be quicker to join 2 separate queries in Python with 2 Django querysets or SQL?

I have activity and comments for users stored in separate models:

class Activity(models.Model):
    user_id = models.CharField(max_length=50, primary_key=True)
    activity_id = models.CharField(max_length=5)
    activity_completed_time = models.DateTimeField()
    group = models.CharField(max_length=70, primary_key=True)


class Comment(models.Model):
    id = models.IntegerField(primary_key=True)
    author_id = models.CharField(max_length=50)
    group = models.CharField(max_length=70, primary_key=True)

The two separate queries with conditions that return the necessary data are:

Activity.objects.values('user_id')
                .filter(group='value')
                .filter(activity_completed_time__isnull = false)
                .annotate('total_completed' = Count('activity_id'))

Comment.objects.values('author_id')
               .filter(group='value')
               .annotate('total_comments' = Count('id'))

It is obviously possible to do this in several different ways, I am trying to find the quickest way to do it as I am processing over 5.5 million records in the Activity table and over 790k records in the Comment table.

score 0 · Answer 1 · edited May 23 '17 at 12:10

0

If I am understanding this correctly ,you need to join 2 querysets.

Try this answer:

How to combine 2 or more querysets in a Django view?

If this isn't what you want, and you need some inner/outer SQL join maybe you should consider executing some RAW SQL .

edited May 23 '17 at 12:10

Community

1
1

answered Apr 12 '17 at 08:04

Danae Vogiatzi

178
5
23

Thanks for your reply. So it is obviously possible to do it in python/Django however I think it would probably be quicker if I can join the records in SQL. I've updated the question and split it into SQL and Django to make it clearer what I'm trying to do and why I'd like to optimise the SQL before processing in Django. – Jack Clarke Apr 12 '17 at 12:47

Joining 2 tables with Count in MySQL and Django

1 Answers1