From 8e20d731f055f5a0f6ce7c63157eb0c8b8ac6778 Mon Sep 17 00:00:00 2001
From: Tor Andersson <tor@ccxvii.net>
Date: Thu, 24 Nov 2022 14:41:49 +0100
Subject: Move user dynamic info into SQL view.

---
 schema.sql | 33 +++++++++++++++++++++++++++++++++
 1 file changed, 33 insertions(+)

(limited to 'schema.sql')

diff --git a/schema.sql b/schema.sql
index 0ff0cc2..d4a6864 100644
--- a/schema.sql
+++ b/schema.sql
@@ -79,6 +79,39 @@ create view user_profile_view as
 		natural left join user_last_seen
 	;
 
+drop view if exists user_dynamic_view;
+create view user_dynamic_view as
+	select
+		user_id,
+		name,
+		mail,
+		(
+			select
+				count(*)
+			from
+				messages
+			where
+				to_id = user_id
+				and is_read = 0
+				and is_deleted_from_inbox = 0
+		) as unread,
+		(
+			select
+				count(*)
+			from
+				players
+				join games using(game_id)
+				join game_state using(game_id)
+			where
+				status = 1
+				and players.user_id = users.user_id
+				and active in ( players.role, 'Both', 'All' )
+		) as active,
+		is_banned
+	from
+		users
+	;
+
 -- Friend and Block Lists --
 
 create table if not exists contacts (
-- 
cgit v1.2.3