CODEWITHSUNDEEP
symfonydoctrine-ormdoctrinesymfony-2.3
Reynier
Reynier

Reputation: 2478

Handling composite keys in this model using Symfony2 and Doctrine2

I have a table stock:

CREATE TABLE IF NOT EXISTS `stock` (
  `product` VARCHAR(20) NOT NULL,
  `kcondition` TINYINT NOT NULL,
  `company` INT(11) NOT NULL,
  `status` INT(11) NOT NULL,
  `sku` VARCHAR(255) NULL,
  `unit` INT(11) NOT NULL,
  `width` DECIMAL(4,4) NULL,
  `height` DECIMAL(4,4) NULL,
  `weigth` DECIMAL(4,4) NULL,
  `length` DECIMAL(4,4) NULL,
  `created`  NOT NULL,
  `modified`  NULL DEFAULT NULL,
  `weigth_class` INT(11) NOT NULL,
  `length_class` INT(11) NOT NULL,
  PRIMARY KEY (`product`, `kcondition`, `company`),
  INDEX `stock_sku_fkey_idx` (`product` ASC),
  INDEX `fk_stock_n_stock_status1_idx` (`status` ASC),
  INDEX `fk_stock_company1_idx` (`company` ASC),
  INDEX `fk_stock_n_condition1_idx` (`kcondition` ASC),
  UNIQUE INDEX `sku_UNIQUE` (`sku` ASC, `product` ASC, `kcondition` ASC, `company` ASC, `status` ASC),
  INDEX `fk_stock_n_unit1_idx` (`unit` ASC),
  INDEX `fk_stock_n_weigth_class1_idx` (`weigth_class` ASC),
  INDEX `fk_stock_n_length_class1_idx` (`length_class` ASC),
  CONSTRAINT `fk_stock_company1`
    FOREIGN KEY (`company`)
    REFERENCES `company` (`id`)
    ON UPDATE CASCADE,
  CONSTRAINT `fk_stock_n_stock_status1`
    FOREIGN KEY (`status`)
    REFERENCES `n_stock_status` (`id`)
    ON UPDATE CASCADE,
  CONSTRAINT `stock_sku_fkey`
    FOREIGN KEY (`product`)
    REFERENCES `product` (`upc`)
    ON UPDATE CASCADE,
  CONSTRAINT `fk_stock_n_condition1`
    FOREIGN KEY (`kcondition`)
    REFERENCES `n_condition` (`id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  CONSTRAINT `fk_stock_n_unit1`
    FOREIGN KEY (`unit`)
    REFERENCES `n_unit` (`id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  CONSTRAINT `fk_stock_n_weigth_class1`
    FOREIGN KEY (`weigth_class`)
    REFERENCES `n_weigth_class` (`id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  CONSTRAINT `fk_stock_n_length_class1`
    FOREIGN KEY (`length_class`)
    REFERENCES `n_length_class` (`id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT)
ENGINE = InnoDB;

Then I have a table stock_detail which has a relation with previous stock as yours may see:

CREATE TABLE IF NOT EXISTS `stock_detail` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `product` VARCHAR(20) NOT NULL,
  `kcondition` TINYINT NOT NULL,
  `company` INT(11) NOT NULL,
  `price` DECIMAL(19,4) NOT NULL,
  `amount` INT NULL,
  `availability` INT NULL,
  PRIMARY KEY (`id`, `product`, `kcondition`, `company`),
  CONSTRAINT `fk_stock_detail_stock1`
    FOREIGN KEY (`product` , `kcondition` , `company`)
    REFERENCES `stock` (`product` , `kcondition` , `company`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT)
ENGINE = InnoDB;

I'm trying to build the right Entity for stock_detail. Since I need to use a AUTO_INCREMENT field there I don't know how to start, any help? How did yours handle with the right generation of ID's (PK)?

PS: I read docs here but isn't clear how this feet on those cases

Upvotes: 0

Views: 150

Answers (1)

hcoat
hcoat

Reputation: 2643

Doctrine doesn't allow an option to accomplish this on the DB side. However, one way of doing this is to get the last id value and and increment it. This can be accomplished by setting up stock detail repository like following:

class stockDetailRepository extends EntityRepository
{
    public function findLastEntry()
    {
        $limit = 1;

        return $this->getEntityManager()
                ->createQuery(
                    'SELECT s FROM YourBundle:StockDetail s 
                     ORDER BY s.id DESC'
                )
                ->setMaxResults($limit)
                ->getResult();
     }
...

Then use findLastEntry in your controller before calling persist

class stockDetailController extends Controller
{
    public function updateStaticDetailAction()
    {
        ...
        $em = $this->getDoctrine()->getManager();
        $lastEntry = $em
         ->getRepository('YourBundle:stockDetail')
         ->findLastEntry();
        $lastId = $lastEntry[0]->getId()+1;

        $staticDetail = new StaticDetail();
        $staticDetail->set...
        ...
        $staticDetail->setId($lastId);
        $em->persist($staticDetail);
        $em->flush();
        ...
    }
}

Of course this is a rough outline, but hopefully gets the idea across.

Upvotes: 1

Related Questions