mardi 8 septembre 2015

SQLite cross reference unique combinations

I've got two tables already populated with data with the given schemas:

    CREATE TABLE objects
    (
        id BIGINT NOT NULL,
        PRIMARY KEY (id)
    )
    CREATE TABLE tags
    (
        id BIGINT NOT NULL,
        tag_key VARCHAR(100) NOT NULL,
        tag_value VARCHAR(500),
        PRIMARY KEY (id , tag_key)
    )

object.id and tags.id refer to the same object

I'd like to populate a third table with the unique combinations of tag_key and tag_value. For example:

    INSERT OR REPLACE INTO objects (id) VALUES (0);
    INSERT OR REPLACE INTO tags (id, tag_key, tag_value) VALUES (0, 'a', 'x');
    INSERT OR REPLACE INTO objects (id) VALUES (1);
    INSERT OR REPLACE INTO tags (id, tag_key, tag_value) VALUES (1, 'a', 'y');
    INSERT OR REPLACE INTO objects (id) VALUES (2);
    INSERT OR REPLACE INTO tags (id, tag_key, tag_value) VALUES (2, 'a', 'x');
    INSERT OR REPLACE INTO tags (id, tag_key, tag_value) VALUES (2, 'a', 'y');
    INSERT OR REPLACE INTO objects (id) VALUES (3);
    INSERT OR REPLACE INTO tags (id, tag_key, tag_value) VALUES (3, 'a', 'x');
    INSERT OR REPLACE INTO objects (id) VALUES (4);
    INSERT OR REPLACE INTO tags (id, tag_key, tag_value) VALUES (4, 'a', 'y');

Should result in 3 entries of

0: ([a,x]) 
1: ([a,y]) 
3: ([a,x][a,y])

Currently I have:

    CREATE TABLE tags_combinations
    (
        id INTEGER PRIMARY KEY,
        tag_key VARCHAR(100) NOT NULL,
        tag_value VARCHAR(500)
    );

The id shouldn't be related to the original id of the object, just something to group unique combinations.

This is the query I have so far:

    SELECT
        t1.tag_key, t1.tag_value
    FROM
        tags t1
    WHERE
        t1.id
    IN
        (
    /* select ids who's every tags entry is not under one id in tags_combinations */
            SELECT 
                t2.id 
            FROM
                tags t2
            WHERE
                t2.tag_key, t2.tag_value
            NOT IN
            (

            )
        );

The part with the comment is what I am not sure about, how would I select every id from tags that does not have all of the corresponding tag_key and tag_value entries already under one id in tags_combinations?

Aucun commentaire:

Enregistrer un commentaire