Hugo Hugo - 2 months ago 30x
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 :

| 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


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:


namespace CM\PlatformBundle\Entity;

use Doctrine\ORM\EntityRepository;

 * CartRepository
class CartRepository extends EntityRepository
    public function getLastCartIdForLine($lineId) {
            return $this->createQueryBuilder('c')
                        ->andWhere(' = :lineId')
                        ->setParameter('lineId', $lineId)
                        ->orderBy('', 'DESC')

And I finally do this :

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

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