mysql - 通过 ID 限制响应 (MySQL/CakePHP)

Question

处理以下序列的最佳做法是什么？

我有一张包含许多类(class)(私有(private)、半私有(private)、小组)的表格。这些类(class)在一周中的任何一天都有固定的时间范围(即早上 6 点到 11 点)。学生可以在设定的时间范围内以30分钟为增量选择一节课(周一私有(private)类(class)@ 9:00-9:30am)

我正在使用 CakePHP 并设置了大部分表格，但我在关系中迷失了方向。

基本上，我如何根据类(class)类型限制时间和长度的结果？

我还在脚手架...我不确定在脚手架模式下是否有办法限制结果:)

-- -----------------------------------------------------
-- Table `test_db`.`address`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`address` (
  `addressline1` VARCHAR(45) NOT NULL ,
  `addressline2` VARCHAR(45) NULL DEFAULT NULL ,
  `city` VARCHAR(45) NOT NULL ,
  `state_id` INT(2) NOT NULL ,
  `zipcode` MEDIUMINT(9) NOT NULL ,
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  INDEX `state_id` (`state_id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `test_db`.`groups`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`groups` (
  `name` VARCHAR(10) NOT NULL ,
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  UNIQUE INDEX `roletype_UNIQUE` (`name` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 4
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `test_db`.`lesson_type`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`lesson_type` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `type_id` SMALLINT(6) NOT NULL ,
  `length` VARCHAR(10) NOT NULL ,
  `cost` INT(11) NOT NULL ,
  `maxstudent` TINYINT(4) NOT NULL ,
  `location_id` INT(11) NOT NULL ,
  `typename` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `ID` (`id` ASC) ,
  UNIQUE INDEX `typename_UNIQUE` (`typename` ASC) ,
  INDEX `LessonCost` (`cost` ASC) ,
  INDEX `LessonLocation` (`location_id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 6
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `test_db`.`lessons`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`lessons` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `Student_id` INT(11) NOT NULL ,
  `weekday_id` TINYINT(4) NOT NULL ,
  `Time_id` INT(11) NOT NULL ,
  `trainer_id` INT(11) NOT NULL ,
  `Notes` VARCHAR(250) NULL DEFAULT NULL ,
  `lessontype_id` INT(11) NOT NULL ,
  `transaction_id` INT(11) NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `type_id` (`lessontype_id` ASC) ,
  INDEX `transfer_id_UNIQUE` (`transaction_id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 17
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `test_db`.`lgroups`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`lgroups` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `type` CHAR(15) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  UNIQUE INDEX `type_UNIQUE` (`type` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 4
DEFAULT CHARACTER SET = latin1
COMMENT = '     ' ;


-- -----------------------------------------------------
-- Table `test_db`.`location`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`location` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `locationname` VARCHAR(45) NOT NULL ,
  `locationaddress1` VARCHAR(80) NOT NULL ,
  `locationaddress2` VARCHAR(80) NULL DEFAULT NULL ,
  `locationcity` VARCHAR(45) NOT NULL ,
  `locationZip` VARCHAR(5) NOT NULL ,
  `locationphone` VARCHAR(12) NOT NULL ,
  `state_id` INT(11) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  INDEX `state_id` (`state_id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `test_db`.`lochours`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`lochours` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `location_id` INT(3) NOT NULL ,
  `ltype_id` INT(3) NOT NULL ,
  `isMonday` TINYINT(1) NULL DEFAULT '0' ,
  `isTuesday` TINYINT(1) NULL DEFAULT '0' ,
  `isWednesday` TINYINT(1) NULL DEFAULT '0' ,
  `isThursday` TINYINT(1) NULL DEFAULT '0' ,
  `isFriday` TINYINT(1) NULL DEFAULT '0' ,
  `isSaturday` TINYINT(1) NULL DEFAULT '0' ,
  `isSunday` TINYINT(1) NULL DEFAULT '0' ,
  `to_id` INT(3) NOT NULL ,
  `from_id` INT(3) NOT NULL ,
  `hours` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  UNIQUE INDEX `location_id_UNIQUE` (`location_id` ASC) ,
  UNIQUE INDEX `ltype_id_UNIQUE` (`ltype_id` ASC) ,
  UNIQUE INDEX `locationhours` (`hours` ASC) ,
  INDEX `starttime` (`to_id` ASC) ,
  INDEX `endtime` (`from_id` ASC) )
ENGINE = MyISAM
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `test_db`.`members`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`members` (
  `firstname` VARCHAR(60) NOT NULL ,
  `lastname` VARCHAR(60) NOT NULL ,
  `address_id` INT(11) NOT NULL ,
  `phone1` VARCHAR(45) NOT NULL ,
  `phone2` VARCHAR(45) NULL DEFAULT NULL ,
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `user_id` INT(11) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  UNIQUE INDEX `user_id_UNIQUE` (`user_id` ASC) ,
  UNIQUE INDEX `address_id_UNIQUE` (`address_id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 7
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `test_db`.`state`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`state` (
  `id` SMALLINT(5) NOT NULL AUTO_INCREMENT COMMENT 'PK: Unique state ID' ,
  `state` VARCHAR(32) NOT NULL COMMENT 'State name with first letter capital' ,
  `state_abbr` VARCHAR(8) NULL DEFAULT NULL COMMENT 'Optional state abbreviation (US is 2 capital letters)' ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_unique` (`id` ASC) )
ENGINE = MyISAM
AUTO_INCREMENT = 52
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `test_db`.`students`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`students` (
  `member_id` INT(11) NOT NULL ,
  `firstname` VARCHAR(60) NOT NULL ,
  `lastname` VARCHAR(60) NOT NULL ,
  `nickname` VARCHAR(45) NULL DEFAULT NULL ,
  `birthday` DATE NOT NULL ,
  `gender` VARCHAR(15) NOT NULL ,
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 4
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `test_db`.`time`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`time` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `timeslot` TIME NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  UNIQUE INDEX `timeslot_UNIQUE` (`timeslot` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 35
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `test_db`.`trainer`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`trainer` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `trainer_firstname` VARCHAR(45) NOT NULL ,
  `trainer_lastname` VARCHAR(45) NOT NULL ,
  `trainer_email` VARCHAR(45) NOT NULL ,
  `trainer_phone` VARCHAR(45) NOT NULL ,
  `address_id` INT(11) NOT NULL ,
  `user_id` INT(11) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `user_id_UNIQUE` (`user_id` ASC) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  UNIQUE INDEX `address_id_UNIQUE` (`address_id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `test_db`.`transaction_details`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`transaction_details` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `depositamount` INT(11) NOT NULL ,
  `depositdate` DATE NOT NULL ,
  `balance` INT(11) NULL DEFAULT NULL ,
  `paymenttype` VARCHAR(10) NOT NULL DEFAULT 'cash' ,
  `createdate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
  `transaction_id` INT(11) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  UNIQUE INDEX `createdate_UNIQUE` (`createdate` ASC) ,
  UNIQUE INDEX `transaction_id_UNIQUE` (`transaction_id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `test_db`.`transactions`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`transactions` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `user_id` INT(11) NOT NULL ,
  `details_id` INT(11) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `details_id_UNIQUE` (`details_id` ASC) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  INDEX `user_id` (`user_id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `test_db`.`users`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`users` (
  `username` CHAR(45) NOT NULL ,
  `password` CHAR(45) NOT NULL ,
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `email` VARCHAR(255) NOT NULL ,
  `group_id` INT(11) NOT NULL DEFAULT '1' ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `username_UNIQUE` (`username` ASC) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 11
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `test_db`.`weekday`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`weekday` (
  `id` TINYINT(4) NOT NULL AUTO_INCREMENT ,
  `day` CHAR(9) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  UNIQUE INDEX `day_UNIQUE` (`day` ASC) )
ENGINE = MyISAM
AUTO_INCREMENT = 8
DEFAULT CHARACTER SET = latin1;

Answer 1

如果您想更改具体显示的内容，您需要修改模型、 View 或 Controller 。这只能在您拆除脚手架后才能完成。您可以从命令行使用“bake”来用可编辑代码替换脚手架。这里有使用 bake 的教程:

Code-Generation-with-Bake