Learning SQL Joins On Many-To-Many Relationships

joinmanytomany.sql

/*
 Usage:
 $ sqlite3 test.db
 >.read test.sql -- Home - Recent Page
 
 Get Concepts for a particular user
 SELECT * FROM EntityConcept WHERE EntityID=0;
 
 Get Users with a Particular Concept
 SELECT * FROM EntityConcept WHERE ConceptID=1;
 
 Need Entity by most recently added, name, heartcount, media
 SELECT EntityID,Name, HeartCount From Entity ORDER BY datetime(DateAdded) DESC;
 SELECT Value,Type FROM Media WHERE EntityID=0;


 Home - Entities Page
 Need Entity Name sorted and an Image
 Select EntityID,Name,Value FROM Entity ORDER BY Name ASC;
 SELECT Value,Type FROM Media WHERE EntityID=0 AND type='image' LIMIT 1;


 Home - Concepts Page
 Need Concept name sorted and an image
 SELECT Concept.ConceptID,Tag.TagID,Concept.Name,Tag.Name FROM Tag JOIN ConceptTag USING(TagID) JOIN Concept USING(ConceptID) ORDER BY Concept.Name ASC, Tag.Name ASC;
 SELECT Value FROM EntityConcept JOIN Media USING (EntityID) WHERE ConceptID=0 AND Type='image' ORDER BY RANDOM() LIMIT 1;


 Home - Tags Page
 Need tags and an image for a random entity that has that tag
 SELECT TagID,Name FROM Tag ORDER BY Name ASC;
 SELECT Value FROM EntityTag JOIN Media USING (EntityID) where TagID=0 AND Type='image' ORDER BY RANDOM() LIMIT 1;


 Entity Details Page
 SELECT * FROM Entity WHERE EntityID=0;
 SELECT Value,Type FROM Media WHERE EntityID=0;


 Add New Entity Page
 INSERT INTO Entity(Name,ViewCount,HeartCount,DateAdded,Info) VALUES('Joe',20,10,'2021-04-18T04:17:09Z','Test');


 Entity Details Page update:
 UPDATE Entity SET Name='Test' WHERE EntityID=3;


 Delete Entity/Tag/Concept/Media
 DELETE FROM Entity WHERE EntityID=0;


 Search Page
 Get matches by name and show an image representing that search result
 SELECT EntityID,Name FROM Entity WHERE Name LIKE '%searchterm%';
 SELECT TagID,Name FROM Tag WHERE Name LIKE '%searchterm%';
 SELECT ConceptID,Name FROM Concept WHERE Name LIKE '%searchterm%';


 All Entities with tags
 SELECT EntityID FROM Entity INTERSECT Select EntityID FROM EntityTag;


 Tags mapped to concepts
 SELECT TagID FROM Tag INTERSECT Select TagID FROM ConceptTag;

 */
PRAGMA foreign_keys = ON;
DROP INDEX IF EXISTS ConceptTagTagIDIndex;
DROP INDEX IF EXISTS ConceptTagConceptIDIndex;
DROP TABLE IF EXISTS ConceptTag;
DROP INDEX IF EXISTS EntityTagTagIDIndex;
DROP INDEX IF EXISTS EntityTagEntityIDIndex;
DROP TABLE IF EXISTS EntityTag;
DROP TABLE IF EXISTS Concept;
DROP TABLE IF EXISTS Tag;
DROP INDEX IF EXISTS MediaEntityIDIndex;
DROP TABLE IF EXISTS Media;
DROP TABLE IF EXISTS Entity;
DROP VIEW IF EXISTS EntityConcept;
CREATE TABLE IF NOT EXISTS Entity(
    EntityID INTEGER PRIMARY KEY,
    Name TEXT UNIQUE COLLATE NOCASE,
    ViewCount INTEGER,
    HeartCount INTEGER,
    DateAdded TEXT,
    Info TEXT
);
CREATE TABLE IF NOT EXISTS Media(
    MediaID INTEGER PRIMARY KEY,
    EntityID INTEGER REFERENCES Entity ON DELETE CASCADE,
    Value TEXT,
    Type TEXT
);
CREATE INDEX IF NOT EXISTS MediaEntityIDIndex ON Media(EntityID);
CREATE TABLE IF NOT EXISTS Tag(
    TagID INTEGER PRIMARY KEY,
    Name TEXT UNIQUE COLLATE NOCASE
);
CREATE TABLE IF NOT EXISTS Concept(
    ConceptID INTEGER PRIMARY KEY,
    Name TEXT UNIQUE COLLATE NOCASE
);
CREATE TABLE IF NOT EXISTS EntityTag(
    EntityID INTEGER REFERENCES Entity ON DELETE CASCADE,
    TagID INTEGER REFERENCES Tag ON DELETE CASCADE,
    PRIMARY KEY (EntityID,TagID)
);
CREATE INDEX IF NOT EXISTS EntityTagEntityIDIndex ON EntityTag(EntityID);
CREATE INDEX IF NOT EXISTS EntityTagTagIDIndex ON EntityTag(TagID);

CREATE TABLE IF NOT EXISTS ConceptTag(
    ConceptID INTEGER REFERENCES Concept ON DELETE CASCADE,
    TagID INTEGER REFERENCES Tag ON DELETE CASCADE,
    PRIMARY KEY (ConceptID,TagID)
);
CREATE INDEX IF NOT EXISTS ConceptTagConceptIDIndex ON ConceptTag(ConceptID);
CREATE INDEX IF NOT EXISTS ConceptTagTagIDIndex ON ConceptTag(TagID);
CREATE VIEW IF NOT EXISTS EntityConcept (EntityID, ConceptID, EntityName, ConceptName) AS
SELECT PT.EntityID,
    CT.ConceptID,
    C.Name,
    PT.Name
FROM ConceptTag CT
    LEFT JOIN (
        SELECT PT.TagID,
            PT.EntityID,
            P.Name
        FROM EntityTag PT
            JOIN Entity P ON PT.EntityID = P.EntityID
    ) as PT ON PT.TagID = CT.TagID
    LEFT JOIN (
        SELECT C.ConceptID,
            C.Name
        FROM Concept C
    ) as C ON C.ConceptID = CT.ConceptID
GROUP BY CT.ConceptID,
    PT.EntityID
HAVING (
        (
            SELECT COUNT()
            FROM ConceptTag
            WHERE ConceptID = C.ConceptID
        ) = (
            SELECT COUNT()
            FROM (
                    SELECT TagID
                    FROM EntityTag
                    WHERE EntityID = PT.EntityID
                    INTERSECT
                    SELECT TagID
                    FROM ConceptTag
                    WHERE ConceptID = CT.ConceptID
                )
        )
    );
INSERT
    OR REPLACE INTO Entity
VALUES(0, 'Joe', 20, 10, '2021-04-18T02:13:09Z', 'Test');
INSERT
    OR REPLACE INTO Entity
VALUES(1, 'Bob', 5, 3, '2021-04-17T02:13:09Z', 'Test');
INSERT
    OR REPLACE INTO Entity
VALUES(
        2,
        'Smith',
        15,
        8,
        '2021-04-16T02:13:09Z',
        'Test'
    );
INSERT
    OR REPLACE INTO Media
VALUES(0, 0, 'image1.png', 'image');
INSERT
    OR REPLACE INTO Media
VALUES(1, 0, 'image2.png', 'image');
INSERT
    OR REPLACE INTO Media
VALUES(2, 0, 'reddit.com/joe', 'link');
INSERT
    OR REPLACE INTO Media
VALUES(3, 1, 'image1.png', 'image');
INSERT
    OR REPLACE INTO Media
VALUES(4, 1, 'twitter.com/bob', 'link');
INSERT
    OR REPLACE INTO Media
VALUES(5, 1, 'Bob search', 'search');
INSERT
    OR REPLACE INTO Media
VALUES(6, 2, 'Smith search', 'search');
INSERT
    OR REPLACE INTO Media
VALUES(7, 2, 'twitter.com/smith', 'link');
INSERT
    OR REPLACE INTO Tag
VALUES(0, 'Red');
INSERT
    OR REPLACE INTO Tag
VALUES(1, 'White');
INSERT
    OR REPLACE INTO Tag
VALUES(2, 'Blue');
INSERT
    OR REPLACE INTO Tag
VALUES(3, 'Green');
INSERT
    OR REPLACE INTO Tag
VALUES(4, 'Yellow');
INSERT
    OR REPLACE INTO Concept
VALUES(0, 'Colors');
INSERT
    OR REPLACE INTO Concept
VALUES(1, 'RGB');
INSERT
    OR REPLACE INTO EntityTag
VALUES(0, 0);
INSERT
    OR REPLACE INTO EntityTag
VALUES(0, 1);
INSERT
    OR REPLACE INTO EntityTag
VALUES(0, 2);
INSERT
    OR REPLACE INTO EntityTag
VALUES(0, 3);
INSERT
    OR REPLACE INTO EntityTag
VALUES(0, 4);
INSERT
    OR REPLACE INTO EntityTag
VALUES(1, 1);
INSERT
    OR REPLACE INTO EntityTag
VALUES(1, 2);
INSERT
    OR REPLACE INTO EntityTag
VALUES(1, 3);
INSERT
    OR REPLACE INTO EntityTag
VALUES(2, 0);
INSERT
    OR REPLACE INTO EntityTag
VALUES(2, 2);
INSERT
    OR REPLACE INTO EntityTag
VALUES(2, 3);
INSERT
    OR REPLACE INTO ConceptTag
VALUES(0, 0);
INSERT
    OR REPLACE INTO ConceptTag
VALUES(0, 1);
INSERT
    OR REPLACE INTO ConceptTag
VALUES(0, 2);
INSERT
    OR REPLACE INTO ConceptTag
VALUES(0, 3);
INSERT
    OR REPLACE INTO ConceptTag
VALUES(0, 4);
INSERT
    OR REPLACE INTO ConceptTag
VALUES(1, 0);
INSERT
    OR REPLACE INTO ConceptTag
VALUES(1, 2);
INSERT
    OR REPLACE INTO ConceptTag
VALUES(1, 3);