Hugo Hugo - 4 months ago 41
MySQL Question

Doctrine2 / MySQL - Create an auto increment field on multiple columns

I wanted to know if it is possible to have an auto increment field based on multiple columns ?

Here is my problem :
I have a table which stores CARTS in LINES. I have a unique id for each carts but I also want to generate a unique id for each line. So I can have a unique CART ID inside a line.

Actually, I want to exactly do the same as in this example from the MySQL doc :

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

+--------+----+---------+
| grp | id | name |
+--------+----+---------+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+--------+----+---------+


The id increments within the grp column.

I'm a bit lost with primary key / index and I don't know if I have to use these in order to generate my ids.

I tried to add this to my symfony entity, but it didn't generate a new column with the id I want.

* @ORM\Table(indexes={@ORM\Index(name="line_idx", columns={"line_id"})})


Thanks for your help

Answer

Alright, I managed to do what I want, I'm not sure if it's the best way to do it but here is how I did:

I added my cart_id field :

/**
 * @var integer
 *
 * @ORM\Column(name="cart_id", type="integer", nullable=false)
*/
private $cart_id;

Then, I added a unique contraint, just to be sure everything is unique :

 * @ORM\Table(uniqueConstraints={@UniqueConstraint(name="cart_idx", columns={"line_id", "cart_id"})})

Then, I made a repository function to get the last cart_id for a given line (there is a ManyToOne relation between my cart and my line:

<?php

namespace CM\PlatformBundle\Entity;

use Doctrine\ORM\EntityRepository;

/**
 * CartRepository
 */
class CartRepository extends EntityRepository
{
    public function getLastCartIdForLine($lineId) {
            return $this->createQueryBuilder('c')
                        ->select('c.cart_id')
                        ->join('c.line','l')
                        ->andWhere('l.id = :lineId')
                        ->setParameter('lineId', $lineId)
                        ->orderBy('c.id', 'DESC')
                        ->setMaxResults(1)
                        ->getQuery()
                        ->getSingleScalarResult();
    }       
}

And I finally do this :

$cart = new Cart;
$lastCartId = $em->getRepository('CMPlatformBundle:Cart')->getLastCartIdForLine($lineId);
$cart->setCartId($lastCartId+1);
$em->persist($cart);

It works, not sure if it was the best way to handle this. Let me know if you find something better.