61471

Symfony / Doctrine - query date values against existing database values

Question:

I have two entities payroll week and payroll period, want to write a query that checks if the start date and end date entered by the user to the payroll period table is found in the payroll week table which is populated already and update it with that payroll period id in the database. Each classes looks like. This is what I have done thus far regarding the query.

<h2>Payrollperiod Entity:</h2> <?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(); } } <h2>Payrollweek Entity:</h2> <?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(); } } <h2>Payrollweek Entity Repository:</h2>

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() ; } }

<strong>CHANGES:</strong>

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(), ); }

Answer1:

First you'll want to get the PayrollPeriod id, and then update your Payrollweek entity accordingly. You don't even need any special repository functions. If you were in a controller it would look like this:

$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(); }

You don't even need to use any custom repository functions, although it wouldn't hurt to put them in there. I'd also change your $payrollperiodid class member to $payrollperiod since it refers to the corresponding Payrollperiod entity, and not the individual foreign key integer id.

Answer2:

Here is an example. If your payroll period already exists that is needed for a PayrollWeek, you can build your form to return PayrollPeriods to select from instead of querying for it after the form is submitted.

In this example, Territory belongs to a Country. So the form will display a list of countries.

$builder ->add('name', TextType::class, [ 'label' => 'Territory Name', ]) ->add('abbrev', TextType::class, [ 'label' => 'Territory Abbreviation', ]) ->add('belongingCountry', EntityType::class, [ 'class' => 'AppBundle:Address\Country', 'choice_label' => 'name', 'label' => 'Country' ]);

In your case, if PayrollWeek belongs to PayrollPeriod. You can do the same.

$builder ->add('payrollperiodid', EntityType::class, [ 'class' => 'AppBundle:Payrollperiod', 'choice_label' => 'startdate' ])

You will have to mess with the choice_label a bit to format the display of the \DateTime as you want.

Recommend

  • Persisted date in database not equal to retrieved date
  • How to change the date format in bootstrap Datepicker?
  • Parse elements from href tags
  • Change HTTP URL in Worklight adapter
  • Redux Observable: How to return an action from a callback?
  • How to assign a prop value to a state in react
  • Null Pointer Exception when trying to send message from one activity to another
  • How to orderByValue onSnapshot using Firestore
  • How can we cache HLS video url once streamed
  • Dynamically update page based on an instance variable from Ajax
  • Unable to import a React Component in my Mocha test
  • How to find the distance (in pixels) between the Sprite and screen corners?
  • React 16 Error Boundary component (using componentDidCatch) shows uncaught error
  • Android Get the markers that are not clustered
  • Cannot read property setState of undefined in axios callback
  • Symfony2 Form is always empty after submitting
  • I can't create model objects using accepts_nested_attributes_for. It won't create the nest
  • Need help with Eval inside DataList
  • How to design resolution independent CSS elements?
  • Bundling fails because path mistaken for a controller path
  • Rails has_many :through association: save instance into join table
  • JSF disable inputbox with a checkbox in a ui:repeat on a composite component
  • SceneKit: Is it possible to cast an shadow on an Transparent Object?
  • Calling a controller from another
  • select context variables properties based on other variables in thymeleaf (dynamically)
  • Getting webGL error in autodesk viewer
  • Why is my Rails app redirecting to `data:,`?
  • Render html in springfox-swagger-ui
  • How can i use same custom fonts for both android and ios using react native
  • Passing Props from State to Child
  • Xamarin forms toggle button default color on IOS
  • use a single handler for multiple inputs onChange events
  • javascript / jquery scope differences between jQuery.each and normal for loop?
  • How to add a filter to Active Admin dashboard?
  • NoMethodError in Controller#action - undefined method `keynote' for #
  • Messed up characters in webpages (especially social media)
  • Undefined navigator.push React-native 0.43.4
  • redirect_to root_url and return unless @user.activated
  • Django invalid literal for int() with base 10
  • Modifying destination and filename of gulp-svg-sprite