From ecc249f8829209d0427b6819b87fd3bdc0087c89 Mon Sep 17 00:00:00 2001 From: wookie184 Date: Thu, 27 Oct 2022 16:14:05 +0100 Subject: Add metricity query for messages in past n days Takes multiple users for efficiency as we may want to calculate this for many users at once. --- pydis_site/apps/api/models/bot/metricity.py | 28 ++++++++++++++++++++++++++++ 1 file changed, 28 insertions(+) (limited to 'pydis_site/apps/api/models') diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py index abd25ef0..73bc1f0c 100644 --- a/pydis_site/apps/api/models/bot/metricity.py +++ b/pydis_site/apps/api/models/bot/metricity.py @@ -130,3 +130,31 @@ class Metricity: raise NotFoundError() return values + + def total_messages_in_past_n_days( + self, + user_ids: list[str], + days: int + ) -> list[tuple[int, int]]: + """ + Query activity by a list of users in the past `days` days. + + Returns a list of (user_id, message_count) tuples. + """ + self.cursor.execute( + """ + SELECT + author_id, COUNT(*) + FROM messages + WHERE + author_id IN %s + AND NOT is_deleted + AND channel_id NOT IN %s + AND created_at > now() - interval '%s days' + GROUP BY author_id + """, + [tuple(user_ids), EXCLUDE_CHANNELS, days] + ) + values = self.cursor.fetchall() + + return values -- cgit v1.2.3 From 798c499c3c7673612a6815c0ab77d95be066d7ce Mon Sep 17 00:00:00 2001 From: wookie184 Date: Wed, 2 Nov 2022 18:57:22 +0000 Subject: Change the endpoint to be a POST not a GET --- pydis_site/apps/api/models/bot/metricity.py | 2 +- pydis_site/apps/api/tests/test_users.py | 34 ++++++++--------------------- pydis_site/apps/api/viewsets/bot/user.py | 6 ++--- 3 files changed, 13 insertions(+), 29 deletions(-) (limited to 'pydis_site/apps/api/models') diff --git a/pydis_site/apps/api/models/bot/metricity.py b/pydis_site/apps/api/models/bot/metricity.py index 73bc1f0c..f53dd33c 100644 --- a/pydis_site/apps/api/models/bot/metricity.py +++ b/pydis_site/apps/api/models/bot/metricity.py @@ -135,7 +135,7 @@ class Metricity: self, user_ids: list[str], days: int - ) -> list[tuple[int, int]]: + ) -> list[tuple[str, int]]: """ Query activity by a list of users in the past `days` days. diff --git a/pydis_site/apps/api/tests/test_users.py b/pydis_site/apps/api/tests/test_users.py index 60be8598..9c0fa6ba 100644 --- a/pydis_site/apps/api/tests/test_users.py +++ b/pydis_site/apps/api/tests/test_users.py @@ -1,4 +1,3 @@ -import json import random from unittest.mock import Mock, patch @@ -510,13 +509,10 @@ class UserMetricityTests(AuthenticatedAPITestCase): # When url = reverse("api:bot:user-metricity-activity-data") - # Can't send data in body with normal GET request so use generic request. - response = self.client.generic( - "GET", + response = self.client.post( url, - data=json.dumps([0, 1]), + data=[0, 1], QUERY_STRING="days=10", - content_type="application/json" ) # Then @@ -530,13 +526,10 @@ class UserMetricityTests(AuthenticatedAPITestCase): # When url = reverse("api:bot:user-metricity-activity-data") - # Can't send data in body with normal GET request so use generic request. - response = self.client.generic( - "GET", + response = self.client.post( url, - data=json.dumps([0, 1]), + data=[0, 1], QUERY_STRING="days=fifty", - content_type="application/json" ) # Then @@ -550,12 +543,9 @@ class UserMetricityTests(AuthenticatedAPITestCase): # When url = reverse('api:bot:user-metricity-activity-data') - # Can't send data in body with normal GET request so use generic request. - response = self.client.generic( - "GET", + response = self.client.post( url, - data=json.dumps([0, 1]), - content_type="application/json" + data=[0, 1], ) # Then @@ -569,12 +559,9 @@ class UserMetricityTests(AuthenticatedAPITestCase): # When url = reverse('api:bot:user-metricity-activity-data') - # Can't send data in body with normal GET request so use generic request. - response = self.client.generic( - "GET", + response = self.client.post( url, QUERY_STRING="days=10", - content_type="application/json" ) # Then @@ -588,13 +575,10 @@ class UserMetricityTests(AuthenticatedAPITestCase): # When url = reverse('api:bot:user-metricity-activity-data') - # Can't send data in body with normal GET request so use generic request. - response = self.client.generic( - "GET", + response = self.client.post( url, - data=json.dumps([123, 'username']), + data=[123, 'username'], QUERY_STRING="days=10", - content_type="application/json" ) # Then diff --git a/pydis_site/apps/api/viewsets/bot/user.py b/pydis_site/apps/api/viewsets/bot/user.py index f1aebee0..f803b3f6 100644 --- a/pydis_site/apps/api/viewsets/bot/user.py +++ b/pydis_site/apps/api/viewsets/bot/user.py @@ -139,7 +139,7 @@ class UserViewSet(ModelViewSet): - 200: returned on success - 404: if a user with the given `snowflake` could not be found - ### GET /bot/users/metricity_activity_data + ### POST /bot/users/metricity_activity_data Gets the number of messages sent on the server in a given period. Users with no messages in the specified period or who do not @@ -324,7 +324,7 @@ class UserViewSet(ModelViewSet): return Response(dict(detail="User not found in metricity"), status=status.HTTP_404_NOT_FOUND) - @action(detail=False) + @action(detail=False, methods=["POST"]) def metricity_activity_data(self, request: Request) -> Response: """Request handler for metricity_activity_data endpoint.""" if "days" in request.query_params: @@ -352,7 +352,7 @@ class UserViewSet(ModelViewSet): data = metricity.total_messages_in_past_n_days(user_ids, days) response_data = [ - {"id": d[0], "message_count": d[1]} + {"id": int(d[0]), "message_count": d[1]} for d in data ] return Response(response_data, status=status.HTTP_200_OK) -- cgit v1.2.3 From 5144f83c5cbd0571d1ca46f19da61860745d5f7e Mon Sep 17 00:00:00 2001 From: Amrou Bellalouna Date: Sun, 6 Nov 2022 02:20:11 +0100 Subject: add thread_id column to the nomination table --- .../api/migrations/0085_add_thread_id_to_nomination.py | 18 ++++++++++++++++++ pydis_site/apps/api/models/bot/nomination.py | 5 +++++ 2 files changed, 23 insertions(+) create mode 100644 pydis_site/apps/api/migrations/0085_add_thread_id_to_nomination.py (limited to 'pydis_site/apps/api/models') diff --git a/pydis_site/apps/api/migrations/0085_add_thread_id_to_nomination.py b/pydis_site/apps/api/migrations/0085_add_thread_id_to_nomination.py new file mode 100644 index 00000000..cb216a62 --- /dev/null +++ b/pydis_site/apps/api/migrations/0085_add_thread_id_to_nomination.py @@ -0,0 +1,18 @@ +# Generated by Django 4.1.2 on 2022-11-05 23:53 + +from django.db import migrations, models + + +class Migration(migrations.Migration): + + dependencies = [ + ('api', '0084_infraction_last_applied'), + ] + + operations = [ + migrations.AddField( + model_name='nomination', + name='thread_id', + field=models.BigIntegerField(blank=True, help_text="The nomination vote thread's id", null=True), + ), + ] diff --git a/pydis_site/apps/api/models/bot/nomination.py b/pydis_site/apps/api/models/bot/nomination.py index 221d8534..e96177d3 100644 --- a/pydis_site/apps/api/models/bot/nomination.py +++ b/pydis_site/apps/api/models/bot/nomination.py @@ -35,6 +35,11 @@ class Nomination(ModelReprMixin, models.Model): default=False, help_text="Whether a review was made." ) + thread_id = models.BigIntegerField( + help_text="The nomination vote thread's id.", + null=True, + blank=True + ) def __str__(self): """Representation that makes the target and state of the nomination immediately evident.""" -- cgit v1.2.3 From 8bc05d1c7def6b5558f554b0d5305bd346783583 Mon Sep 17 00:00:00 2001 From: Amrou Bellalouna Date: Mon, 7 Nov 2022 18:20:10 +0100 Subject: push forgotten model update --- pydis_site/apps/api/models/bot/nomination.py | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'pydis_site/apps/api/models') diff --git a/pydis_site/apps/api/models/bot/nomination.py b/pydis_site/apps/api/models/bot/nomination.py index e96177d3..9146c1d0 100644 --- a/pydis_site/apps/api/models/bot/nomination.py +++ b/pydis_site/apps/api/models/bot/nomination.py @@ -36,7 +36,7 @@ class Nomination(ModelReprMixin, models.Model): help_text="Whether a review was made." ) thread_id = models.BigIntegerField( - help_text="The nomination vote thread's id.", + help_text="The nomination vote's thread id.", null=True, blank=True ) -- cgit v1.2.3 From 285f48f4e0fd903f296921640f710fe6459a7154 Mon Sep 17 00:00:00 2001 From: Amrou Bellalouna Date: Sat, 12 Nov 2022 15:53:25 +0100 Subject: remove useless blank constraint This updates the migration as well --- .../api/migrations/0085_add_thread_id_to_nomination.py | 18 ------------------ .../migrations/0085_add_thread_id_to_nominations.py | 18 ++++++++++++++++++ pydis_site/apps/api/models/bot/nomination.py | 1 - 3 files changed, 18 insertions(+), 19 deletions(-) delete mode 100644 pydis_site/apps/api/migrations/0085_add_thread_id_to_nomination.py create mode 100644 pydis_site/apps/api/migrations/0085_add_thread_id_to_nominations.py (limited to 'pydis_site/apps/api/models') diff --git a/pydis_site/apps/api/migrations/0085_add_thread_id_to_nomination.py b/pydis_site/apps/api/migrations/0085_add_thread_id_to_nomination.py deleted file mode 100644 index ee9707f0..00000000 --- a/pydis_site/apps/api/migrations/0085_add_thread_id_to_nomination.py +++ /dev/null @@ -1,18 +0,0 @@ -# Generated by Django 4.1.2 on 2022-11-07 17:16 - -from django.db import migrations, models - - -class Migration(migrations.Migration): - - dependencies = [ - ('api', '0084_infraction_last_applied'), - ] - - operations = [ - migrations.AddField( - model_name='nomination', - name='thread_id', - field=models.BigIntegerField(blank=True, help_text="The nomination vote's thread id.", null=True), - ), - ] diff --git a/pydis_site/apps/api/migrations/0085_add_thread_id_to_nominations.py b/pydis_site/apps/api/migrations/0085_add_thread_id_to_nominations.py new file mode 100644 index 00000000..56a24cc3 --- /dev/null +++ b/pydis_site/apps/api/migrations/0085_add_thread_id_to_nominations.py @@ -0,0 +1,18 @@ +# Generated by Django 4.1.2 on 2022-11-12 14:52 + +from django.db import migrations, models + + +class Migration(migrations.Migration): + + dependencies = [ + ('api', '0084_infraction_last_applied'), + ] + + operations = [ + migrations.AddField( + model_name='nomination', + name='thread_id', + field=models.BigIntegerField(help_text="The nomination vote's thread id.", null=True), + ), + ] diff --git a/pydis_site/apps/api/models/bot/nomination.py b/pydis_site/apps/api/models/bot/nomination.py index 9146c1d0..58e70a83 100644 --- a/pydis_site/apps/api/models/bot/nomination.py +++ b/pydis_site/apps/api/models/bot/nomination.py @@ -38,7 +38,6 @@ class Nomination(ModelReprMixin, models.Model): thread_id = models.BigIntegerField( help_text="The nomination vote's thread id.", null=True, - blank=True ) def __str__(self): -- cgit v1.2.3