here's the sql to recreate a similar schema:
what I want to get from this is:
user request info for an item_model
result should be
all the item_models owned (owned_items) by users with same "mod_usr_attr" and "attr1" as the requesting user (authenticated users).
Then the data should be aggregated as
---------------------------------------------------------------------------------------
| owned_item | mod_usr_attr(case1)| mod_usr_attr(case2)|...|mod_usr_attr(caseN)|total |
---------------------------------------------------------------------------------------
| model_id | number of items | number of items |...| number of items |number|
| ... | ... | ... |...| ... | ... |
-- MySQL Script generated by MySQL Workbench
-- Mon Mar 21 17:29:29 2016
-- Model: New Model Version: 1.0
-- MySQL Workbench Forward Engineering
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;
USE `mydb` ;
-- -----------------------------------------------------
-- Table `mydb`.`makes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`makes` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`make` VARCHAR(255) CHARACTER SET 'utf8' NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `makes_make_unique` (`make` ASC))
ENGINE = InnoDB
AUTO_INCREMENT = 0
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `mydb`.`model_attr`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`model_attr` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`case1` INT(10) UNSIGNED NULL DEFAULT NULL,
`case2` DECIMAL(4,2) NULL DEFAULT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 0
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `mydb`.`item_models`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`item_models` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`make_id` INT(10) UNSIGNED NULL DEFAULT NULL,
`model_name` VARCHAR(45) CHARACTER SET 'utf8' NULL DEFAULT NULL,
`model_attr_id` INT(10) UNSIGNED NULL DEFAULT NULL,
`price` INT(10) UNSIGNED NULL DEFAULT NULL,
`currency` VARCHAR(3) CHARACTER SET 'utf8' NULL DEFAULT NULL,
`description` TEXT CHARACTER SET 'utf8' NULL DEFAULT NULL,
`picture` VARCHAR(255) CHARACTER SET 'utf8' NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `item_models_make_id_foreign` (`make_id` ASC),
INDEX `item_models_model_attr_id_foreign` (`model_attr_id` ASC),
INDEX `model_name` (`model_name` ASC),
CONSTRAINT `item_models_make_id_foreign`
FOREIGN KEY (`make_id`)
REFERENCES `mydb`.`makes` (`id`)
ON DELETE CASCADE,
CONSTRAINT `item_models_model_attr_id_foreign`
FOREIGN KEY (`model_attr_id`)
REFERENCES `mydb`.`model_attr` (`id`)
ON DELETE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 0
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `mydb`.`mod_usr_attr`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`mod_usr_attr` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`case1` DECIMAL(4,2) NULL DEFAULT NULL,
`case2` DECIMAL(4,2) NULL DEFAULT NULL,
`case3` DECIMAL(4,2) NULL DEFAULT NULL,
`case4` DECIMAL(4,2) NULL DEFAULT NULL,
`case4` DECIMAL(4,2) NULL DEFAULT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 0
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `mydb`.`owned_items`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`owned_items` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`model_id` INT(10) UNSIGNED NOT NULL,
`mod_usr_attr_id` INT(10) UNSIGNED NOT NULL,
`make_id` INT(10) UNSIGNED NOT NULL,
`model_attr` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`),
INDEX `owned_items_model_id_foreign` (`model_id` ASC),
INDEX `owned_items_mod_usr_attr_id_foreign` (`mod_usr_attr_id` ASC),
INDEX `owned_items_make_id_foreign` (`make_id` ASC),
INDEX `owned_items_model_attr_id_foreign` (`model_attr` ASC),
CONSTRAINT `owned_items_make_id_foreign`
FOREIGN KEY (`make_id`)
REFERENCES `mydb`.`makes` (`id`)
ON DELETE CASCADE,
CONSTRAINT `owned_items_model_attr_id_foreign`
FOREIGN KEY (`model_attr`)
REFERENCES `mydb`.`model_attr` (`id`)
ON DELETE CASCADE,
CONSTRAINT `owned_items_model_id_foreign`
FOREIGN KEY (`model_id`)
REFERENCES `mydb`.`item_models` (`id`)
ON DELETE CASCADE,
CONSTRAINT `owned_items_mod_usr_attr_id_foreign`
FOREIGN KEY (`mod_usr_attr_id`)
REFERENCES `mydb`.`mod_usr_attr` (`id`)
ON DELETE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 0
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `mydb`.`usr_attr1`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`usr_attr1` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`type` VARCHAR(45) CHARACTER SET 'utf8' NULL DEFAULT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 0
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `mydb`.`genders`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`genders` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`sex` VARCHAR(45) CHARACTER SET 'utf8' NULL DEFAULT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 0
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `mydb`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`users` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`username` VARCHAR(255) CHARACTER SET 'utf8' NOT NULL,
`email` VARCHAR(255) CHARACTER SET 'utf8' NOT NULL,
`password` VARCHAR(60) CHARACTER SET 'utf8' NOT NULL,
`attr1_id` INT(10) UNSIGNED NULL DEFAULT NULL,
`mod_usr_attr_id` INT(10) UNSIGNED NULL DEFAULT NULL,
`gender_id` INT(10) UNSIGNED NULL DEFAULT NULL,
`user_pic` VARCHAR(255) CHARACTER SET 'utf8' NULL DEFAULT NULL,
`remember_token` VARCHAR(100) CHARACTER SET 'utf8' NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `users_username_unique` (`username` ASC),
UNIQUE INDEX `users_email_unique` (`email` ASC),
INDEX `users_usr_attr1_id_foreign` (`attr1_id` ASC),
INDEX `users_mod_usr_id_foreign` (`mod_usr_attr_id` ASC),
INDEX `users_gender_id_foreign` (`gender_id` ASC),
CONSTRAINT `users_usr_attr1_id_foreign`
FOREIGN KEY (`attr1_id`)
REFERENCES `mydb`.`usr_attr1` (`id`)
ON DELETE CASCADE,
CONSTRAINT `users_gender_id_foreign`
FOREIGN KEY (`gender_id`)
REFERENCES `mydb`.`genders` (`id`)
ON DELETE CASCADE,
CONSTRAINT `users_mod_usr_id_foreign`
FOREIGN KEY (`mod_usr_attr_id`)
REFERENCES `mydb`.`mod_usr_attr` (`id`)
ON DELETE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 0
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `mydb`.`owned_item_user`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`owned_item_user` (
`user_id` INT(10) UNSIGNED NOT NULL,
`owned_item_id` INT(10) UNSIGNED NOT NULL,
INDEX `owned_item_user_user_id_index` (`user_id` ASC),
INDEX `owned_item_user_owned_item_id_index` (`owned_item_id` ASC),
CONSTRAINT `owned_item_user_collected_items_id_foreign`
FOREIGN KEY (`owned_item_id`)
REFERENCES `mydb`.`owned_items` (`id`)
ON DELETE CASCADE,
CONSTRAINT `owned_item_user_user_if_foreign`
FOREIGN KEY (`user_id`)
REFERENCES `mydb`.`users` (`id`)
ON DELETE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;