php - Symfony/Doctrine - 根据现有数据库值查询日期值

Question

我有两个实体 payroll week 和 payroll period，想编写一个查询来检查用户输入到 payroll period 表的开始日期和结束日期是否在已经填充的 payroll week 表中找到并更新它在数据库中使用该工资期 ID。每个类看起来像。这是我迄今为止对查询所做的。

工资期实体:

<?php

namespace com\twcl\agripayrollBundle\Entity;

use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;

/**
 * Payrollperiod
 *
 * @ORM\Table(name="PayrollPeriod")
 * @ORM\Entity
 */
class Payrollperiod
{
    /**
     * @var integer
     *
     * @ORM\Column(name="payrollperiodid", type="integer", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $payrollperiodid;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="startDate", type="datetime", nullable=false)
     */
    private $startdate;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="endDate", type="datetime", nullable=false)
     */
    private $enddate;

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

    public function getPayrollperiodid() {
        return $this->payrollperiodid;
    }

    public function getStartdate() {
        return $this->startdate;
    }

    public function getEnddate() {
        return $this->enddate;
    }

    public function getState() {
        return $this->state;
    }

    public function setPayrollperiodid($payrollperiodid) {
        $this->payrollperiodid = $payrollperiodid;
    }

    public function setStartdate(\DateTime $startdate) {
        $this->startdate = $startdate;
    }

    public function setEnddate(\DateTime $enddate) {
        $this->enddate = $enddate;
    }

    public function setState($state) {
        $this->state = $state;
    }

    /**
     * Render a payrollPeriodID as a string.
     *
     * @return string
     */
    public function __toString()
    {
        return (string) $this->getPayrollperiodid();
    }
}

Payrollweek 实体:

<?php

namespace com\twcl\agripayrollBundle\Entity;

use Doctrine\ORM\Mapping as ORM;
use Symfony\Component\Validator\Constraints as Assert;

/**
 * Payrollweek
 *
 * @ORM\Table(name="PayrollWeek", indexes={@ORM\Index(name="IDX_1B4F90669AD94696", columns={"payrollperiodid"})})
 * @ORM\Entity
 */
class Payrollweek
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="startDate", type="datetime", nullable=false)
     * @Assert\Type("DateTime")
     */
    private $startdate;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="endDate", type="datetime", nullable=false)
     * @Assert\Type("DateTime")
     * @Assert\Expression("this.getStartDate() < this.getEndDate()",
     *     message="The end date must be after the start date")
     */
    private $enddate;

    /**
     * @var integer
     *
     * @ORM\Column(name="normalHours", type="integer", nullable=true)
     */
    private $normalhours;

    /**
     * @var integer
     *
     * @ORM\Column(name="numOfDays", type="integer", nullable=true)
     */
    private $numofdays;

    /**
     * @var \Payrollperiod
     *
     * @ORM\ManyToOne(targetEntity="Payrollperiod")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="payrollperiodid", referencedColumnName="id", nullable=false)
     * })
     */
    private $payrollperiodid;

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

    public function getStartdate() {
        return $this->startdate;
    }

    public function getEnddate() {
        return $this->enddate;
    }

    public function getNormalhours() {
        return $this->normalhours;
    }

    public function getNumofdays() {
        return $this->numofdays;
    }

    public function getPayrollperiodid() {
        return $this->payrollperiodid;
    }

    public function setId($id) {
        $this->id = $id;
    }

    public function setStartdate(\DateTime $startdate) {
        $this->startdate = $startdate;
    }

    public function setEnddate(\DateTime $enddate) {
        $this->enddate = $enddate;
    }

    public function setNormalhours($normalhours) {
        $this->normalhours = $normalhours;
    }

    public function setNumofdays($numofdays) {
        $this->numofdays = $numofdays;
    }

    public function setPayrollperiodid($payrollperiodid) {
        $this->payrollperiodid = $payrollperiodid;
    }

    /** * Render StartDate and EndDate as a string.
     * * * @return string */

    public function __toString()
    {
        return (string) $this->getStartdate()->format('d-M-y').'/'.$this->getEnddate()->format('d-M-y');

    }

    public function __construct()
    {
        $this->PayrollweekType = new \Doctrine\Common\Collection\ArrayCollection();
    }
}

Payrollweek 实体存储库:

namespace com\twcl\agripayrollBundle\Entity;

use Doctrine\ORM\EntityRepository;

/**
 * PayrollweekRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class PayrollweekRepository extends EntityRepository
{
    public function payrollPeriodWeek(){
       return $this->EntityManager()->createquery(
               'select startDate and endDate from AcmeDemoBundle:Payrollweek 
               update payrollperiodid' 
               )->getresults() ;
    }
}

变化:

public function createAction(Request $request)
{
    $entity = new Payrollperiod();
    $form = $this->createCreateForm($entity);
    $form->handleRequest($request);

    if ($form->isValid()) {
        $em = $this->getDoctrine()->getManager();
        $payrollweek=new Payrollweek();
        $payrollperiod=$em->getRepository('comtwclagripayrollBundle:PayrollPeriod') ->findOneBy(['startDate' => $startDate, 'endDate' => $endDate]);
        if ($payrollperiod) {
        $payrollweek->setPayrollperiod($payrollperiod);
        }
        else{
        $this->addFlash('error','ERROR! Not a valid payroll week');
         return $this->redirect($this->generateUrl('payrollperiod'));
        }

        $em->persist($payrollweek);
        $em->flush();

        //return $this->redirect($this->generateUrl('payrollperiod_show', array('payrollperiodid' => $entity->getpayrollperiodid())));
    }

    return array(
        'entity' => $entity,
        'form'   => $form->createView(),
    );
}

Answer 1

首先您需要获取 PayrollPeriod id，然后相应地更新您的 Payrollweek 实体。您甚至不需要任何特殊的存储库功能。如果您在 Controller 中，它将看起来像这样:

$em = $this->getDoctrine()->getManager();

$payrollweek = // do whatever here to get a Payrollweek entity

$payrollperiod = $em->getRepository('agripayroll:\PayrollPeriod)
    ->findOneBy(['startDate' => $startDate, 'endDate' => $endDate]);

if ($payrollperiod) {
    $payrollweek->setPayrollperiod($payrollperiod);

    $em->persist($payrollweek);
    $em->flush();
}

您甚至不需要使用任何自定义存储库函数，尽管将它们放在那里也没什么坏处。我还将您的 $payrollperiodid 类成员更改为 $payrollperiod，因为它指的是相应的 Payrollperiod 实体，而不是单个外键整数 ID。