2
votes

SQL pour le genre mutuel

match
---
id person_1_id persona_2_id liked
9  1           2            true
10 1           3            false
11 2           1            true
12 2           3            true
13 3           1            false
14 3           2            false
person_1_id is the liker and person_2_id is the one being liked.How would I query for the mutual likes for person id = 1? Meaning person_1_id = 1 likes person_2_id and persona_2_id likes them back. The expected result would be a single row that contains person_id = 2.

postgresql sql

0 commentaires

3 Réponses :

2
votes

Utilisez une auto-jointure:

SELECT DISTINCT t1.person_1_id, t1.person_2_id
FROM mytable t1
WHERE 
    EXISTS (
        SELECT 1 
        FROM mytable t2 
        WHERE 
            t1.person_1_id = t2.person_2_id 
            AND t1.person_2_id = t2.person_1_id
            AND t2.liked = true
    )
    AND t1.liked = true;

Ou une sous-requête corrélée avec une condition EXISTS :

SELECT DISTINCT t1.person_1_id, t1.person_2_id
FROM mytable t1
INNER JOIN mytable t2 
    ON t1.person_1_id = t2.person_2_id 
    AND t1.person_2_id = t2.person_1_id
    AND t2.liked = true
WHERE t1.liked = true;

0 commentaires

0
votes

Vous pouvez le faire avec la clause exist

SELECT DISTINCT t1.person_1_id, t1.person_2_id
from tbl t1
where exists (select null from tbl t2
  where t1.person_1_id = t2.person_2_.id and
     t1.person_2_id = t2.person_1_id and
     t2.liked = true
) and t1.liked = true;

Si vous souhaitez simplement interroger person_1_id = 1, ajoutez simplement et t1.person_1_id = 1 à la fin

0 commentaires

1
votes

Cette requête vous donnera les résultats que vous souhaitez. Il recherche toute personne que person_id 1 aime et qui l'aime également:

persona_2_id
2

Résultat:

SELECT persona_2_id
FROM match m
WHERE person_1_id = 1 AND
      liked = true AND
      EXISTS (SELECT *
              FROM match m1
              WHERE m1.person_1_id = m.persona_2_id AND
                    m1.persona_2_id = m.person_1_id AND
                    m1.liked = true)

Démo sur dbfiddle

0 commentaires