MIME-Version: 1.0
From: =?UTF-8?B?QnJpY2UgQW5kcsOp?= <brice@famille-andre.be>
Date: Mon, 22 Aug 2022 08:52:41 +0200
Message-ID: 
 <CAOBG12mB0Vhx11xq73K2Yxjxg35Ayw9vSzsz-Hv06xNJGgXbSA@mail.gmail.com>
Subject: Most efficient way to select events from users having common
 subscriptions
To: pgsql-sql@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000864a6505e6cee593"
Archived-At: 
 <https://www.postgresql.org/message-id/CAOBG12mB0Vhx11xq73K2Yxjxg35Ayw9vSzsz-Hv06xNJGgXbSA%40mail.gmail.com>
Precedence: bulk

--000000000000864a6505e6cee593
Content-Type: text/plain; charset="UTF-8"

Hello,

I have a project on which users may be registered to different challenges.
And I would want to implement a "news" panel, where users are notified of
events performed by other users, which have at least one challenge in
common.

My DB schema has tables like follows :

   - table "user"
      - user_id
      - etc.
   - table "challenge"
      - challenge_id
      - etc.
   - table "user_challenge_association"
      - user_id
      - challenge_id
      - team_id
   - table "activity"
      - user_id

I would thus want to perform a request returning all activities of all
users which have at least one entry in "user_challenge_association" where
"challenge_id" is the same as another entry in "user_challenge_association"
associated to the user for which I am doing the request.

Imagine "user" contains (user_1, user_2, user_3), imaging "challenge"
contains (challenge_1, challenge_2), imagine that user_1 is only registered
to challenge_1 user_2 is only registered to challenge_2, and user_3 is
registered to both challenges, I would want :

   - that request for user_1 only returns activities of user_3
   - that request for user_2 only returns activities of user_3
   - that request for user_3 returns activities of user_1 and user_2

What would be the best way to perform such a query ?

Additional question : How could I integrate in the result a column
indicating if, in all existing challenges, the resulting activity is linked
to a user that was at least once in the same team as the user for which we
perform the request ? (team information is in "user_challenge_association"
table

Many thanks for your advices,
Brice

--000000000000864a6505e6cee593
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hello,<div><br></div><div>I have a project on which users =
may be registered to different challenges. And I would want to implement a =
&quot;news&quot; panel, where users are notified of events performed by oth=
er users, which have at least one challenge in common.</div><div><br></div>=
<div>My DB schema has tables like follows : <br></div><div><ul><li>table &q=
uot;user&quot;</li><ul><li>user_id</li><li>etc.</li></ul><li>table &quot;ch=
allenge&quot;</li><ul><li>challenge_id</li><li>etc.</li></ul><li>table &quo=
t;user_challenge_association&quot;</li><ul><li>user_id</li><li>challenge_id=
</li><li>team_id</li></ul><li>table &quot;activity&quot;</li><ul><li>user_i=
d</li></ul></ul><div>I would thus want to perform a request returning all a=
ctivities of all users which have at least one entry in &quot;user_challeng=
e_association&quot; where &quot;challenge_id&quot; is the same as another e=
ntry in &quot;user_challenge_association&quot; associated to the user for w=
hich I am doing the request.</div><div><br></div><div>Imagine &quot;user&qu=
ot; contains (user_1, user_2, user_3), imaging &quot;challenge&quot; contai=
ns (challenge_1, challenge_2), imagine that user_1 is only registered to ch=
allenge_1 user_2 is only registered to challenge_2, and user_3 is registere=
d to both challenges, I would want : <br></div><div><ul><li>that request fo=
r user_1 only returns activities of user_3</li><li>that request for user_2 =
only returns activities of user_3</li><li>that request for user_3 returns a=
ctivities of user_1 and user_2</li></ul><div>What would be the best way to =
perform such a query ?</div><div><br></div><div>Additional question : How c=
ould I integrate in the result a column indicating if, in all existing chal=
lenges, the resulting activity is linked to a user that was at least once i=
n the same team as the user for which we perform the request ? (team inform=
ation is in &quot;user_challenge_association&quot; table</div><div><br></di=
v><div>Many thanks for your advices,</div><div>Brice<br></div></div></div><=
/div>

--000000000000864a6505e6cee593--