CODEWITHSUNDEEP
phpsymfonydoctrine-ormdoctrine
Doug
Doug

Reputation: 1890

SUM of column - Doctrine2 / Symfony2

So I'm currently trying to find the SUM of a Column in Doctrine/Symfony.

Currently I have it so a User enters products in an embedded form (Recipe with embedded 'ProductRecipe' associated to Product (1-m-1)) 'ProductRecipe.Amount * Product.Unitcost = Productcost' which works fine as is. Now I need to calculate the 'Recipe.recipecost' by Adding all the 'Productcost' for each recipe and store it in the 'Recipe' table in Recipecost.

I have tried following this example http://docs.doctrine-project.org/en/2.1/cookbook/aggregate-fields.html but cannot for the life get it to work. Everything I try throws an error's. Have tried doing it pre-persist, persist, in get and in set. My current error when trying to add a new recipe is this:

 An exception occurred while executing 'INSERT INTO Recipe (recipename, 
recipedesc, recipeyeild, recipecost, created_at, updated_at, u_id) VALUES 
(?, ?, ?, ?, ?, ?, ?)' with params ["test cost", "test", 1, null, 
"2014-03-22 17:04:56", null, 3]:

 SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'recipecost' 
 cannot be null

So obviously either the calculation foreach.productcost isn't either running before persist OR isn't running at all. The latter is what I have a feeling is happening, since even when trying to run it pre-persist it still brings up the same error.

What I currently have is:

//Recipe Entity

class Recipe
{
    private $id;
    private $recipecost;
    protected $product;

    public function __construct()
{
    $this->product = new \Doctrine\Common\Collections\ArrayCollection();
}
public function setRecipecost($recipecost)
{
    $this->recipecost = $recipecost;
    return $this;
}

public function getRecipecost()
{
    $recipecost = null; // note to self try 0
    foreach($this->product AS $productcost){
    $recipecost += $productcost->getProduct(); //note to self try getProductcost
    }
    return $this->recipecost;
}

//ProductRecipe Entity

class ProductRecipe
{
    private $id;
    private $amount;
    private $product;
    private $recipe;
    private $productcost;

    public function setProductcost($productcost) {
       $this->productcost = $productcost;
       return $this;
    }
    public function getProductcost() {
      return $this->productcost;
    }
public function __construct($productcost= 0, $product= '') {
    $this->productcost = $productcost;
    $this->product = $product;
    }

 //This function turns Costunit(from Product table) to Productcost. 
    /**
 * @ORM\PrePersist
 */
public function pc2amc() {
    $am = $this->amount;
    $cu = $this->product->getCostunit();
    $productcost = $am * $cu;
    $this->productcost = $productcost;
}

I hope I have covered everything, have ripped out most of the code I think is unrelated. Happy to post more if need be.

Upvotes: 0

Views: 2196

Answers (1)

Doug
Doug

Reputation: 1890

Have fixed my issue,

This function:

public function getRecipecost()
{
  $recipecost = 0;
  foreach($this->product AS $productcost){
  $recipecost += $productcost->getProductcost();
}
  return $this->recipecost;
}

Should have been this:

public function setRecipecost() //Set not Get to store in DB
{
    // $recipecost = 0; **Remove this line**
    foreach($this->product AS $productcost){
        $this->recipecost += $productcost->getProductcost(); //$recipecost now $this->recipecost.
    }
    return $this->recipecost;
}

And Recipe controller:

    public function createAction(Request $request)
{
    $entity = new Recipe();
    $product = new ProductRecipe(); //* Add this line *
    $entity->getProduct()->add($product); //* Add this line *
    $form = $this->createCreateForm($entity);
    $form->handleRequest($request);



    if ($form->isValid()) {
        $em = $this->getDoctrine()->getManager();
        $entity->setUser($this->get('security.context')->getToken()->getUser());

        $em->persist($product); //* Add this line *
        $em->persist($entity);
        $em->flush();

Now works a dream, hope this will be of use to anyone else in the same hole.

Upvotes: 1

Related Questions