So let's say, I got 20 entities in my database already, each of them having a position attribute to display what entity comes first on my website.
If I add a entity, I would like to actually make the position the first, so the position would be 0 at smallest as this would be my newest entity.
OR
If I remove an entity, I would like the other entities to adjust to this and fill the gap so to say.
Though there are already 20 entities in the database with positions ranging from 0-19.
All the positions can be rearranged hence the position attribute.
How would I go about this?
I know a solution by getting all the entities and iterating over them to add 1 to their position on new entity or by subtracting 1 to the position higher than the deleted one but that seems abit over the top.
Thanks to the SQL answer, I made the answer which was:
public function setPositions($position = 0){
$query = $this->createQueryBuilder()
->update('Entity', 'e')
->set('e.position', 'e.position + 1')
->where('e.position >= :position')
->setParameter('position', $position)
->getQuery();
$query->execute();
}
If you put this in the Repository of your entity you can call it in your controller like:
$this->getRepository('Entity')->setPositions();
or
$this->getRepository('Entity')->setPositions( $entityToBeDeleted->getPosition() );
you can do it in one mysql query
UPDATE TABLE myTable
SET position = position + 1
WHERE position >= MyPosition
MyPosition shoul be passed as parameter from php code