Symfony2: Update several rows in a table, after a field on another table is updated

Question

I'm trying to update the same value in several rows in a table when a field on another table is updated. Someone suggested me to do a PostUpdate Listener which I did, the problem is, I don't know who to retrieve the rows of the table with the several rows to update in order to change the field that I want. Is there an easier way to do this? The table table with the several rows to update doesn't have an ID value per se. It has several fields as key value and those values aren't unique.

Table with several rows to update:

/**
 * @ORM\Entity 
 * @ORM\Table(name="catalog_has_prize")
 * @Gedmo\Loggable
 */
class CatalogHasPrize
{
  /**
   * @ORM\Id
   * @ORM\ManyToOne(targetEntity="Catalog", inversedBy="catalog_has_prize")
   * @ORM\JoinColumn(name="catalog", referencedColumnName="id")
   * @Gedmo\Versioned
   */
  private $catalog;

  /**
   * @ORM\Id
   * @ORM\ManyToOne(targetEntity="Prizes\PrizesBundle\Entity\Prize", inversedBy="catalog_has_prize")
   * @ORM\JoinColumn(name="prize", referencedColumnName="id")
   * @Gedmo\Versioned
   */
  private $prize;

  /**
   * @ORM\Column(type="float", nullable=false)
   * @Gedmo\Versioned
   */
  private $point_value;

  /**
   * @ORM\Column(type="float", nullable=false)
   * @Gedmo\Versioned
   */
  private $fullfillment_fee;

  /**
   * @ORM\Column(type="float", nullable=false)
   * @Gedmo\Versioned
   */
  private $margin;

  /**
   * @ORM\Column(type="integer", nullable=false)
   * @Gedmo\Versioned
   */
  private $points;

  /**
   * @ORM\Column(type="integer", nullable=false)
   * @Gedmo\Versioned
   */
  private $auto_calculated_points;

  /**
   * @ORM\Column(type="integer", nullable=false)
   * @Gedmo\Versioned
   */
  private $treshhold;

  /**
   * @ORM\ManyToOne(targetEntity="Prizes\PrizesBundle\Entity\Company", inversedBy="catalog_has_prize")
   * @ORM\JoinColumn(name="company", referencedColumnName="id")
   * @Gedmo\Versioned
   */
  private $company;

  /**
   * @ORM\ManyToOne(targetEntity="Optime\AppStatusBundle\Entity\Status")
   * @ORM\JoinColumn(name="status", referencedColumnName="id")
   * @Gedmo\Versioned
   */
  private $status;

  /**
   * @Gedmo\Timestampable(on="create")
   * @ORM\Column(type="datetime")
   */
  private $created;

  /**
   * @Gedmo\Timestampable(on="update")
   * @ORM\Column(type="datetime")
   */
  private $modified;

  public function __construct( )
  {
  }

  public function setPrize( $prize )
  {
    $this->prize = $prize;
  }

  public function getPrize( )
  {
    return $this->prize;
  }

  public function setCatalog( $catalog )
  {
    $this->catalog = $catalog;
  }

  public function getCatalog( )
  {
    return $this->catalog;
  }

  public function setCompany( $company )
  {
    $this->company = $company;
  }

  public function getCompany( )
  {
    return $this->company;
  }

  public function setStatus( $status )
  {
    $this->status = $status;
  }

  public function getStatus( )
  {
    return $this->status;
  }

  public function setPointValue( $point_value )
  {
    $this->point_value = $point_value;
  }

  public function getPointValue( )
  {
    return $this->point_value;
  }

  public function setFullfillmentFee( $fullfillment_fee )
  {
    $this->fullfillment_fee = $fullfillment_fee;
  }

  public function getFullfillmentFee( )
  {
    return $this->fullfillment_fee;
  }

  public function setMargin( $margin )
  {
    $this->margin = $margin;
  }

  public function getMargin( )
  {
    return $this->margin;
  }

  public function setPoints( $points )
  {
    $this->points = $points;
  }

  public function getPoints( )
  {
    return $this->points;
  }

  public function setAutoCalculatedPoints( $points )
  {
    $this->auto_calculated_points = $points;
  }

  public function getAutoCalculatedPoints( )
  {
    return $this->auto_calculated_points;
  }

  public function setTreshhold( $treshhold )
  {
    $this->treshhold = $treshhold;
  }

  public function getTreshhold( )
  {
    return $this->treshhold;
  }

  public function getCreated( )
  {
    return $this->created->format( 'd/m/Y' );
  }

  public function getModified( )
  {
    return $this->modified->format( 'd/m/Y' );
  }

Table that holds the updated value:

/**
 * @ORM\Entity @ORM\Table(name="prize")
 * @Gedmo\Loggable
 */
class Prize
{
  /**
   * @ORM\Id
   * @ORM\Column(type="integer")
   * @ORM\GeneratedValue(strategy="AUTO")
   * @Gedmo\Versioned
   */
  private $id;

  /**
   * @ORM\Column(type="string", length=170, nullable=false)
   * @Gedmo\Translatable
   * @Gedmo\Versioned
   */
  private $name;

  /**
   * @ORM\Column(type="string", length=400, nullable=false)
   * @Gedmo\Translatable
   * @Gedmo\Versioned
   */
  private $description;

  /**
   * @ORM\Column(type="string", length=255, nullable=false)
   * @Gedmo\Versioned
   */
  private $img;

  /**
   * @ORM\Column(type="string", length=255, nullable=false)
   */
  private $thumb;

  /**
   * @ORM\ManyToOne(targetEntity="Optime\AppStatusBundle\Entity\Status", cascade={""})
   * @ORM\JoinColumn(name="status", referencedColumnName="id", onDelete="RESTRICT", onUpdate="CASCADE")
   * @Gedmo\Versioned
   */
  private $status;

  /**
   * @Gedmo\Timestampable(on="create")
   * @ORM\Column(type="datetime")
   */
  private $created;

  /**
   * @Gedmo\Timestampable(on="update")
   * @ORM\Column(type="datetime")
   */
  private $modified;

  /**
   * @ORM\OneToMany(targetEntity="CompanyCountryPrize", mappedBy="prize")
   */
  private $company_country_prizes;

  /**
   * @ORM\ManyToMany(targetEntity="Category")
   * @ORM\JoinTable(name="prize_has_category",
   *      joinColumns={@ORM\JoinColumn(name="prize", referencedColumnName="id")},
   *      inverseJoinColumns={@ORM\JoinColumn(name="category", referencedColumnName="id")}
   *      )
   */
  private $categories;

  /**
   * @ORM\ManyToMany(targetEntity="Brand")
   * @ORM\JoinTable(name="prize_has_brand",
   *      joinColumns={@ORM\JoinColumn(name="prize", referencedColumnName="id")},
   *      inverseJoinColumns={@ORM\JoinColumn(name="brand", referencedColumnName="id")}
   *      )
   */
  private $brands;

  /**
   * @ORM\OneToMany(targetEntity="Prizes\CatalogBundle\Entity\CatalogHasPrize", mappedBy="catalog")
   */
  private $catalog_has_prize;

  /**
   * @ORM\OneToMany(targetEntity="PrizeMarketInfo", mappedBy="prize")
   */
  private $market_infos;

  /**
   * @ORM\OneToMany(targetEntity="PurchasePrize", mappedBy="prize")
   */
  private $purchase;

  /**
   * @Gedmo\Locale
   * Used locale to override Translation listener`s locale
   * this is not a mapped field of entity metadata, just a simple property
   */
  private $locale;

  public function __construct( )
  {
    $this->company_country_prizes = new \Doctrine\Common\Collections\ArrayCollection( );
    $this->categories = new \Doctrine\Common\Collections\ArrayCollection( );
    $this->brands = new \Doctrine\Common\Collections\ArrayCollection( );
    $this->catalog_has_prize = new \Doctrine\Common\Collections\ArrayCollection( );
    $this->market_infos = new \Doctrine\Common\Collections\ArrayCollection( );
  }

  public function getId( )
  {
    return $this->id;
  }

  public function setImg( $img )
  {
    if ( substr( $img, -4, 1 ) == "." )
      $this->img = $img;
  }

  public function getImg( )
  {
    return $this->img;
  }

  public function setName( $name )
  {
    $this->name = $name;
  }

  public function getName( )
  {
    return $this->name;
  }

  public function setDescription( $description )
  {
    $this->description = $description;
  }

  public function getDescription( )
  {
    return $this->description;
  }

  public function setThumb( $thumb )
  {
    if ( substr( $thumb, -4, 1 ) == "." )
      $this->thumb = $thumb;
  }

  public function getThumb( )
  {
    return $this->thumb;
  }

  public function setStatus( \Optime\AppStatusBundle\Entity\Status $status )
  {
    $this->status = $status;
  }

  public function getStatus( )
  {
    return $this->status;
  }

  public function getCreated( )
  {
    return $this->created;
  }

  public function getModified( )
  {
    return $this->modified;
  }

  public function setCompanyCountryPrize( \Prizes\PrizesBundle\Entity\CompanyCountryPrize $company_country_prizes )
  {
    $this->company_country_prizes[] = $company_country_prizes;
  }

  public function getCompanyCountryPrize( )
  {
    return $this->company_country_prizes;
  }

  public function setCategory( \Prizes\PrizesBundle\Entity\Category $categories )
  {
    $this->categories[] = $categories;
  }

  public function getCategory( )
  {
    return $this->categories;
  }

  public function setBrand( \Prizes\PrizesBundle\Entity\Brand $brands )
  {
    $this->brands[] = $brands;
  }

  public function getBrand( )
  {
    return $this->brands;
  }

  public function setCatalogHasPrize( \Prizes\CatalogBundle\Entity\CatalogHasPrize $catalog_has_prize )
  {
    $this->catalog_has_prize[] = $catalog_has_prize;
  }

  public function getCatalogHasPrize( )
  {
    return $this->catalog_has_prize;
  }

  public function setMarketInfo( \Prizes\PrizesBundle\Entity\PrizeMarketInfo $market_infos )
  {
    $this->market_infos[] = $market_infos;
  }

  public function getMarketInfo( )
  {
    return $this->market_infos;
  }

  public function getPurchase( )
  {
    return $this->purchase;
  }

My attempt to do an Event Listener:

class DeactivatePrizesInCatalog {

    public function postUpdate(LifecycleEventArgs $args)
    {
        $entity = $args->getEntity();
        $entityManager = $args->getEntityManager();


        if ($entity instanceof Prize) {
            if ($entity->getStatus()->getName() == "Inactive"){
             $catalogs = $entityManager->getRepository('CatalogBundle:CatalogHasPrize')->findBy(array('prize' => $entity->getId(),'status' => 7));
            }

        }
    }
}

My problem is, I have no idea how to retrieve the rows and much less how to update them. Any help would be greatly appreciated.

Answer 1

This is what I did, and It worked

public function postUpdate(LifecycleEventArgs $args)
    {
        $entity = $args->getEntity();
        $entityManager = $args->getEntityManager();


        if ($entity instanceof Prize) { 
            if ($entity->getStatus()->getName() == "Inactive"){
         $statusinactive = $entityManager->getRepository('AppStatusBundle:Status')->find(8);
         $q = $entityManager->createQuery("UPDATE CatalogBundle:CatalogHasPrize c SET c.status = :statusid WHERE c.status = 7 AND c.prize = :prizeid")
                 ->setParameters(array('statusid' => $statusinactive ,'prizeid' => $entity->getId()));
         $rs = $q->getResult();
        }else if ($entity->getStatus()->getName() == "Active"){
            $statusinactive = $entityManager->getRepository('AppStatusBundle:Status')->find(7);
            $q = $entityManager->createQuery("UPDATE CatalogBundle:CatalogHasPrize c SET c.status = :statusid WHERE c.status = 8 AND c.prize = :prizeid")
                 ->setParameters(array('statusid' => $statusinactive ,'prizeid' => $entity->getId()));
            $rs = $q->getResult();
        }
    }

Answer 2

Your PostUpdate function should be declared in the entity class with the annotation @ORM\PostUpdate(). In class Prize, add :

/**
 * @ORM\PostUpdate()
 */
public function postUpdate(LifecycleEventArgs $args)
{
    // Your postupdate code here
}

Don't forget to tell Doctrine that your entity have Lifecycle callback with annotation @ORM\HasLifecycleCallbacks :

/**
 * @ORM\Entity @ORM\Table(name="prize")
 * @Gedmo\Loggable
 * @ORM\HasLifecycleCallbacks
 */
class Prize {
    // etc

The logic you use in your lifecycle function is OK : as the entity is fetched from Doctrine, you can then change it and it will be update in the database.

But be careful, there will be a problem with postUpdate: the database update is finished so the modification you do on your Catalog entity won't be sent the the db until you call another flush() operation. In the postUpdate function, it may trigger primary key violation exception. See this question that deals with this problem, onFlush or postFlush lifecycle event may be of help here.