Search for similar elements

similar_search.sql

---- Récupération de 5 nodes similaires à une node ayant les tags suivants:
-- machin, machin2, machin3
/*
+---------------+           +------------------+            +------------------+
|     Node      |           |     Tagging      |            |        Tag       |
+---------------+           +------------------+            +------------------+
| id            | <  -  -   | resource_id      |  --------->| id               |
| contenu       |           | ressource_type   | /          | name             |
+---------------+           | tag_id           |/           +------------------+
                            +------------------+
*/

SELECT DISTINCT * FROM node a
WHERE id IN (
-- Possibilité d'avoir plusieurs ids identiques
    SELECT resourceId FROM tagging tg
    INNER JOIN t.name as t ON t.id = tg.tag_id
    WHERE t.name IN('machin', 'machin2', 'machin3')
        AND resourceType = 'soloist_node'
    GROUP BY resourceId
    ORDER BY COUNT(t.id)
    LIMIT 5
)
ORDER BY node.created_at ASC;



-- Le reste ne devrait plus être nécessaire.
-- Si il n'y a pas 5 résultats:

-- Le seul changement c'est le count
SELECT DISTINCT * FROM node a
WHERE id IN (
    SELECT resourceId FROM tagging tg
    INNER JOIN tag as t ON t.id = tg.tag_id
    WHERE t.name IN('machin', 'machin2', 'machin3')
        AND resourceType = 'soloist_node'
    GROUP BY resourceId
    HAVING COUNT(t.id) = 2
)
ORDER BY node.created_at ASC;

-- Si on a toujours pas le nombre de résultats voulus

SELECT DISTINCT * FROM node a
WHERE id IN (
    SELECT resourceId FROM tagging tg
    INNER JOIN tag as t ON t.id = tg.tag_id
    WHERE t.name IN('machin', 'machin2', 'machin3')
        AND resourceType = 'soloist_node'
    GROUP BY resourceId
    HAVING COUNT(t.id) = 1
)
ORDER BY node.created_at ASC;