Ytukyg

Current code:

SELECT "conversation"."id", "conversation"."title", "conversation"."deleted", "conversation"."created_at", (

        SELECT  COUNT("currentUserConversations->messageReceivers"."id")

        FROM "Conversations" AS "conversation"

        INNER JOIN "UserConversation" AS "currentUserConversations" ON "conversation"."id" = "currentUserConversations"."conversation_id"

        INNER JOIN "MessageReceiver" AS "currentUserConversations->messageReceivers" ON "currentUserConversations"."id" = "currentUserConversations->messageReceivers"."user_conversation_id" AND "currentUserConversations->messageReceivers"."read" = false

        WHERE "currentUserConversations"."user_id" = 7

        ) AS "unreadM" 

FROM "Conversations" AS "conversation" 

INNER JOIN "UserConversation" AS "otherUserConversations" ON "conversation"."id" = "otherUserConversations"."conversation_id" 

INNER JOIN "UserConversation" AS "currentUserConversations" ON "conversation"."id" = "currentUserConversations"."conversation_id" 

INNER JOIN "MessageReceiver" AS "currentUserConversations->messageReceivers" ON "currentUserConversations"."id" = "currentUserConversations->messageReceivers"."user_conversation_id"

WHERE "currentUserConversations"."user_id" = 7 

AND "otherUserConversations"."user_id" = '4' 

GROUP BY "conversation"."id";

My Tables:

• Conversations


• UserConversation


• MessageReceiver

Relationships:

• N UserConversation per Conversations
  


• 1 MessageReceiver per UserConversation
  

What I'm trying to accomplish:

• 
  MessageReceiver has a read column


• 
  MessageReceiver.read can be true/false


• for the given query (everything outside the nested query) I want to count how many MessageReceivers has the read column set as false (and return it in the main query aliased as unreadM)

What's currently happening:

The nested query count all the MessageReceivers, even if they have the read propriety set to true...

Using PostgreSQL dialect.

If possible I would appreciate an approach that doesn't changes the "FROM". I also need to be able to filter the outer query to (conditionally, not in sql, but from express, generating the query based on external conditions) only retrieve conversations where there is atleast 1 unread message ("currentUserConversations->messageReceivers"."read" = false)

My brain is exploding, if anyone has a solution and an explanation I would really much appreciate it!

Expected Results:

id   title                         deleted   created_at              unreadM

4   "automaticSeeded title shvrn"   false   "2018-11-26 13:24:33.766355+01" "0"

7   "automaticSeeded title viowy"   false   "2018-11-26 13:24:33.766355+01" "4"

Returned Results:

id   title                         deleted   created_at              unreadM

4   "automaticSeeded title shvrn"   false   "2018-11-26 13:24:33.766355+01" "4"

7   "automaticSeeded title viowy"   false   "2018-11-26 13:24:33.766355+01" "4"

Sample Data:

UserConversation

id|conversation_id|user_id|deleted|admin

1|10|1|f|f

2|10|10|f|f

3|9|2|f|f

4|9|9|f|f

5|8|3|f|f

6|8|8|f|f

8|7|7|f|f

10|6|6|f|f

11|5|6|f|f

12|5|5|f|f

13|4|7|f|f

14|4|4|f|f

15|3|8|f|f

16|3|3|f|f

17|2|9|f|f

18|2|2|f|f

19|1|10|f|f

20|1|1|f|f

7|7|4|f|f

9|6|7|f|f

MessageReceiver:

id|user_conversation_id|message_id|read

1|1|1|t

2|1|2|t

3|1|3|t

4|1|4|t

5|2|5|t

6|2|6|t

7|2|7|t

8|2|8|t

9|3|9|t

10|3|10|t

11|3|11|t

12|3|12|t

17|5|17|t

18|5|18|t

19|5|19|t

20|5|20|t

21|6|21|t

22|6|22|t

23|6|23|t

24|6|24|t

29|8|29|f

30|8|30|f

31|8|31|f

32|8|32|f

33|9|33|t

34|9|34|t

35|9|35|t

36|9|36|t

37|10|37|t

38|10|38|t

39|10|39|t

40|10|40|t

41|11|41|t

42|11|42|t

43|11|43|t

44|11|44|t

45|12|45|f

46|12|46|f

47|12|47|f

48|12|48|f

49|13|49|t

50|13|50|t

51|13|51|t

52|13|52|t

53|14|53|t

54|14|54|t

55|14|55|t

56|14|56|t

57|15|57|t

58|15|58|t

59|15|59|t

60|15|60|t

61|16|61|f

62|16|62|f

63|16|63|f

64|16|64|f

65|17|65|t

66|17|66|t

67|17|67|t

68|17|68|t

69|18|69|t

70|18|70|t

71|18|71|t

72|18|72|t

73|19|73|t

74|19|74|t

75|19|75|t

76|19|76|t

77|20|77|f

78|20|78|f

79|20|79|f

80|20|80|f

25|7|25|t

26|7|26|t

14|4|14|t

13|4|13|t

16|4|16|t

15|4|15|t

27|7|27|t

28|7|28|t

Conversations:

id|title|deleted|created_at

1|automaticSeeded title ijmmg|f|2018-11-26 13:24:33.766355+01

2|automaticSeeded title xdjiy|f|2018-11-26 13:24:33.766355+01

3|automaticSeeded title bmvpv|f|2018-11-26 13:24:33.766355+01

4|automaticSeeded title shvrn|f|2018-11-26 13:24:33.766355+01

5|automaticSeeded title yjvai|f|2018-11-26 13:24:33.766355+01

6|automaticSeeded title ubzab|f|2018-11-26 13:24:33.766355+01

7|automaticSeeded title viowy|f|2018-11-26 13:24:33.766355+01

8|automaticSeeded title ecthq|f|2018-11-26 13:24:33.766355+01

9|automaticSeeded title tzotl|f|2018-11-26 13:24:33.766355+01

10|automaticSeeded title fakjf|f|2018-11-26 13:24:33.766355+01

It is not completely clear what you are trying to achieve, but your subquery is independent. It means that you will obtain always the same result for all rows in unreadM.

However, I believe that you want the following

SELECT c."id", c."title", c."deleted", c."created_at", 

    Count(

        CASE WHEN mr."read" = false THEN 1 END

    ) AS "unreadM" 

FROM "Conversations" AS c 

INNER JOIN "UserConversation" AS ouc ON c."id" = ouc."conversation_id" 

INNER JOIN "UserConversation" AS uc ON c."id" = uc."conversation_id" 

INNER JOIN "MessageReceiver" AS mr ON uc."id" = mr."user_conversation_id"

WHERE uc."user_id" = 7 

AND ouc."user_id" = 4 

GROUP BY c."id";

If you would do count(*) + where read = false instead of count(case when read = false ...) then you would lost conversations without unread messages.

Moreover, I would include all the conversation columns that you want in the output into the GROUP BY list. Just to meet the others DBMS requirements.

...

GROUP BY c."id", c."title", c."deleted", c."created_at";