SQL Behovsguiden

behov.sql

-- Create tables --
DROP TABLE IF EXISTS wp_posts;
CREATE TABLE wp_posts (
    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    post_type VARCHAR(255) NOT NULL DEFAULT 'hso_product',
    post_status VARCHAR(255) NOT NULL DEFAULT 'publish',
    menu_order INT NOT NULL DEFAULT 0
);

DROP TABLE IF EXISTS wp_terms;
CREATE TABLE wp_terms (
    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    type INT NOT NULL,
    name VARCHAR(255) NOT NULL
);

DROP TABLE IF EXISTS wp_term_relationships;
CREATE TABLE wp_term_relationships (
    object_id INT NOT NULL,
    term_taxonomy_id INT NOT NULL
);

-- Insert data --
INSERT INTO wp_posts(name) VALUES('Bokföring');
INSERT INTO wp_posts(name) VALUES('Bygglön');

INSERT INTO wp_terms(type, name) VALUES(1, 'Bygg');
INSERT INTO wp_terms(type, name) VALUES(1, 'Restaurang');

INSERT INTO wp_terms(type, name) VALUES(2, 'Bokföring');
INSERT INTO wp_terms(type, name) VALUES(2, 'Lönehantering');
INSERT INTO wp_terms(type, name) VALUES(2, 'Fakturering');

INSERT INTO wp_term_relationships(object_id, term_taxonomy_id) 
VALUES(
    (SELECT ID FROM wp_posts WHERE name='Bokföring'), 
    (SELECT ID FROM wp_terms WHERE name='Bygg')
);
INSERT INTO wp_term_relationships(object_id, term_taxonomy_id) 
VALUES(
    (SELECT ID FROM wp_posts WHERE name='Bokföring'), 
    (SELECT ID FROM wp_terms WHERE name='Restaurang')
);
INSERT INTO wp_term_relationships(object_id, term_taxonomy_id) 
VALUES(
    (SELECT ID FROM wp_posts WHERE name='Bokföring'), 
    (SELECT ID FROM wp_terms WHERE name='Bokföring')
);

INSERT INTO wp_term_relationships(object_id, term_taxonomy_id) 
VALUES(
    (SELECT ID FROM wp_posts WHERE name='Bygglön'), 
    (SELECT ID FROM wp_terms WHERE name='Bygg')
);
INSERT INTO wp_term_relationships(object_id, term_taxonomy_id) 
VALUES(
    (SELECT ID FROM wp_posts WHERE name='Bygglön'), 
    (SELECT ID FROM wp_terms WHERE name='Lönehantering')
);

-- View tables --
SELECT * FROM wp_posts;
SELECT * FROM wp_terms;
SELECT * FROM wp_term_relationships;

-- product search --
SET @business = (SELECT ID FROM wp_terms WHERE name='Restaurang');
SET @function1 = (SELECT ID FROM wp_terms WHERE name='Bokföring');
SET @function2 = (SELECT ID FROM wp_terms WHERE name='Lönehantering');

-- Minimal code --
SELECT SQL_CALC_FOUND_ROWS wp_posts.* 
FROM wp_posts  
INNER JOIN wp_term_relationships AS r_business ON (wp_posts.ID = r_business.object_id)
INNER JOIN wp_term_relationships AS r_function ON (wp_posts.ID = r_function.object_id)
WHERE (r_business.term_taxonomy_id = @business) 
AND (r_function.term_taxonomy_id IN (@function1, @function2));

-- full wp query --
SELECT SQL_CALC_FOUND_ROWS wp_posts.* 
FROM wp_posts  
INNER JOIN wp_term_relationships AS r_business ON (wp_posts.ID = r_business.object_id)
INNER JOIN wp_term_relationships AS r_function ON (wp_posts.ID = r_function.object_id) WHERE 1=1
AND (r_business.term_taxonomy_id = @business) 
AND (r_function.term_taxonomy_id IN (@function1, @function2))
AND wp_posts.post_type = 'hso_product' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'future' OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending') 
GROUP BY wp_posts.ID 
ORDER BY  wp_posts.menu_order 
ASC LIMIT 0, 5;