MYSQL: Gestionar relaciones de amistad - https://es.stackoverflow.com/q/180171/29967, MySQL

Language: Layout:

/*MYSQL: Gestionar relaciones de amistad - https://es.stackoverflow.com/q/180171/29967*/
    /*
    -- ATENCIÓN NO USE DROP TABLE CON SUS TABLAS REALES
    -- YA QUE DROP TABLE BORRARÁ SUS DATOS
    -- DROP TABLE SE USA AQUÍ SÓLO PARA PODER PROBAR LOS DATOS 
    */

SET FOREIGN_KEY_CHECKS=0; 
    
    DROP TABLE IF EXISTS users_20180713;
    DROP TABLE IF EXISTS matches_20180713;

SET FOREIGN_KEY_CHECKS=1;

/* NO COPIE LA SENTENCIA ^ DROP TABLE ^ EN SUS DATOS REALES */

CREATE TABLE IF NOT EXISTS users_20180713 
    (
        user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    )ENGINE=INNODB;
    
    INSERT INTO users_20180713 (user_id) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);

/*
       *La clave es la columna match_status que manejará los siguientes estados:
          1: amigos : se aceptaron mutuamente
          2: pendiente que emisor acepte a receptor
          3: pendiente que receptor acepte a emisor
          4: emisor bloqueó a receptor
          5: receptor bloqueó a emisor
          6: se bloquearon mutuamente
   */
    CREATE TABLE IF NOT EXISTS matches_20180713 
    (
        match_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        match_emisor   INT,
        match_receptor INT,
        match_status TINYINT,
        CONSTRAINT UNIQUE(match_emisor,match_receptor),
        FOREIGN KEY (match_emisor) REFERENCES users_20180713(user_id) 
        ON UPDATE CASCADE ON DELETE  CASCADE, 
        FOREIGN KEY (match_receptor) REFERENCES users_20180713(user_id)        
        ON UPDATE CASCADE ON DELETE  CASCADE  
    )ENGINE=INNODB;

INSERT INTO matches_20180713 
        (match_emisor,match_receptor,match_status)       
    VALUES 
        (1,2,1), 
        (5,1,4),
        (3,1,0),
        (4,1,1),
        (1,7,6)
     ;

SELECT 
    ue.user_id id_emisor, 
    ur.user_id id_receptor
FROM matches_20180713 m
    LEFT JOIN users_20180713 ue ON ue.user_id=m.match_emisor
    LEFT JOIN users_20180713 ur ON m.match_receptor=ur.user_id
WHERE 
    (m.match_emisor=1 OR m.match_receptor=1) AND m.match_status=1;

SELECT 
    ue.user_id id_emisor, 
    ur.user_id id_receptor,
    m.match_status
FROM matches_20180713 m
    LEFT JOIN users_20180713 ue ON ue.user_id=m.match_emisor
    LEFT JOIN users_20180713 ur ON m.match_receptor=ur.user_id
WHERE 
    (m.match_emisor=1 OR m.match_receptor=1) AND m.match_status>3;

MYSQL: Gestionar relaciones de amistad - https://es.stackoverflow.com/...