Query to Delete foreign key in Symfony

Question

Can someone help me with this, to create a query that if the user tries to delete a foreign key it gives them an error rather than using an exception.

public function findByStudent($studentid, $id){
    return $this->getEntityManager()
            ->createQuery(
              'select p from AcmeDemoBundle:Student 
                    where studentid = :studentid AND id= :id
                    ')
            ->setParameter('student',$studentid)
            ->setParameter('id',$id)        


                    ;

}

UPDATES

$student= $em->getRepository('AcmeDemoBundle:Student')->findOneby($studentid, $courdeId, $LecturerId);
        if($student){
             $this->addFlash('error','ERROR! You cannot delete this Student');   
        }

        $em->remove($student);
        $em->flush();
        $this->addFlash('error','Student Deleted');
    }
    return $this->redirect($this->generateUrl('student'));

Student Entity

<?php

namespace Acme\DemoBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * Student
 *
 * @ORM\Table()
 * @ORM\Entity
 */
class Student
{
/**
 * @var integer
 *
 * @ORM\Column(name="studentid", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */

private $studentid;

/**
 * @var string
 *
 * @ORM\Column(name="name", type="string", length=255)
 */
private $name;

/**
 * @var string
 *
 * @ORM\Column(name="address", type="string", length=255)
 */
private $address;

/**
 * @var string
 *
 * @ORM\Column(name="Programme", type="string", length=255)
 */
private $programme;

/**
 * @var \DateTime
 *
 * @ORM\Column(name="Date of Birth", type="date")
 */
private $dateOfBirth;

/**
 * @var string
 *
 * @ORM\Column(name="Contact", type="string", length=255)
 */
private $contact;


/**
 * Get id
 *
 * @return integer 
 */
public function getId()
{
    return $this->id;
}

/**
 * Set studentid
 *
 * @param integer $studentid
 * @return Student
 */
public function setStudentid($studentid)
{
    $this->studentid = $studentid;

    return $this;
}

/**
 * Get studentid
 *
 * @return integer 
 */
public function getStudentid()
{
    return $this->studentid;
}

/**
 * Set name
 *
 * @param string $name
 * @return Student
 */
public function setName($name)
{
    $this->name = $name;

    return $this;
}

/**
 * Get name
 *
 * @return string 
 */
public function getName()
{
    return $this->name;
}

/**
 * Set address
 *
 * @param string $address
 * @return Student
 */
public function setAddress($address)
{
    $this->address = $address;

    return $this;
}

/**
 * Get address
 *
 * @return string 
 */
public function getAddress()
{
    return $this->address;
}

/**
 * Set programme
 *
 * @param string $programme
 * @return Student
 */
public function setProgramme($programme)
{
    $this->programme = $programme;

    return $this;
}

/**
 * Get programme
 *
 * @return string 
 */
public function getProgramme()
{
    return $this->programme;
}

/**
 * Set dateOfBirth
 *
 * @param \DateTime $dateOfBirth
 * @return Student
 */
public function setDateOfBirth($dateOfBirth)
{
    $this->dateOfBirth = $dateOfBirth;

    return $this;
}

/**
 * Get dateOfBirth
 *
 * @return \DateTime 
 */
public function getDateOfBirth()
{
    return $this->dateOfBirth;
}

/**
 * Set contact
 *
 * @param string $contact
 * @return Student
 */
public function setContact($contact)
{
    $this->contact = $contact;

    return $this;
}

/**
 * Get contact
 *
 * @return string 
 */
public function getContact()
{
    return $this->contact;
}

}

<entity name="AcmeDemoBundle\Entity\Course" table="Course" repository-class="AcmeDemoBundle\Entity\CourseRepository">
<indexes>
  <index name="IDX_1B4F90669AD94696" columns="studentid"/>
</indexes>
<id name="id" type="integer" column="id">
  <generator strategy="IDENTITY"/>
</id>
<field name="name" type="string" column="string" nullable="false"/>

<many-to-one field="studentid" target-entity="Student">
  <join-columns>
    <join-column name="studentid" referenced-column-name="studentid"/>
  </join-columns>
</many-to-one>

Updated Custom Repository

        public function findByStudentid($studentid, $id, tutorId)
{
     return $this->getEntityManager()
     ->createQuery('select s from AcmeDemoBundle:Student s
                    where s.studentid = :studentid AND s.id= :id or studentid = :studentid AND p.tutorId= :tutorId ')
              ->setParameter('studentid',$studentid)
            ->setParameter('id',$id)        
            ->setParameter('tutorId',$tutorId)  
            ->getResults();
     $student= $this->entityManager->getRepository('AcmeDemoBundle:Student')->findBy(['studentid' => $studentid]);
     if ($student){
         return false;
     } else {
         return true;
     }
}

What do you mean by gives them an error? Just wrap your function call with a try/catch and handle the Exception however you want. — Jason Roman, Jul 31 '17 at 14:05
Error message saying they can't delete Student, I have tried the try/catch syntax but was told not to use such — buju lloyd, Jul 31 '17 at 15:27
@bujulloyd your repository method accepts two parameters, but you are calling it with three arguments. — jkucharovic, Aug 01 '17 at 11:00

svgrafov · Accepted Answer · 2017-08-02T16:14:44.780

In your student repository create something like that:

public function isDeletable($student)
{
     //your conditions here
     $courses = $this->entityManager->getRepository('Courses')->findBy(['student' => $student]);
     $tutor = $this->entityManager->getRepository('Tutors')->findBy(['student' => $student]);
     if ($courses || $tutor){
         return false;
     } else {
         return true;
     }
}

and call this function before deleting. If it says true - delete record, if false - show error page.

score 0 · Answer 2 · answered Jul 31 '17 at 14:04

0

Use try catch syntax

use Doctrine\DBAL\Exception\ForeignKeyConstraintViolationException;

try {
    $em = $this->getDoctrine()->getManager();
    $student = $em->getRepository("AppBundle:Student")->findOneBy([
        'studentId' => $studentId,
        'id' => $id
    ]);
    if(!$student) { ... your code ... }

    $em->remove($student);
    $em->flush(); // it there is exception connected with FK, then
    // catch it below
} catch(ForeignKeyConstraintViolationException $e) {
    return $this->render(':error:page.html.twig',['error'=>$e]);
}

You can also catch

use Doctrine\DBAL\Exception\UniqueConstraintViolationException;

and many other exceptions connected with database. You do not type query in DQL if you only compare to given values of $studentId and $id. Method findOneBy is simplest solution. I propose to use DQL in more complicated queries.

If my answer seems to be unclear or you want to obtain something else, please precise your question and inform me in comment.

answered Jul 31 '17 at 14:04

Daniel

7,684
7
52
76

I used a try and catch syntax before but was told not use such, I should create a query that if the student id matches a course id or a lecturers id then it cannot be deleted therefore a error would be given – buju lloyd Jul 31 '17 at 14:09
So you can get student from repository without DQL and by instruction if(!$student) decide about your further logic and deleting or not other entities. – Daniel Jul 31 '17 at 14:24
I have updated the question to what I have tried now – buju lloyd Jul 31 '17 at 14:27
If I use this $student = $em->getRepository("AppBundle:Student")->findOneBy([ 'studentId' => $studentId, 'id' => $id ]); if(!$student) how would I know if it is a fk in course or lecturer table – buju lloyd Jul 31 '17 at 14:30
Who ever told you to not use try/catch is mistaken. You will cause a server error each time you try to delete a record that has FK references to that student. As Daniel pointed out (and probably myself in a separate by same question), you need to catch the exceptions that will be thrown. – Andrew Nolan Jul 31 '17 at 14:56

Query to Delete foreign key in Symfony

2 Answers2