protected function execute(InputInterface $input, OutputInterface $output) { $client = new Client(); // Go to the booking.com website $crawler = $client->request('GET', 'http://www.booking.com/country.en-gb.html'); $crawler = $crawler->filter('body#b2countryPage > div#bodyconstraint > div#bodyconstraint-inner > div.lp_flexible_layout_content_wrapper > div#countryTmpl > div.block_third > div.block_header'); $progress = new ProgressBar($output,$crawler->count()); $progress->start(); $sumHotels = 0; $sumCountries = 0; $doctrine = $this->getContainer()->get('doctrine'); $em = $doctrine->getManager(); $em->getConnection()->exec( 'TRUNCATE TABLE countries'); foreach ($crawler as $domElement) { $CountryName = $domElement->getElementsByTagName('h2')->item(0)->textContent; $hotels = $domElement->getElementsByTagName('span')->item(0)->textContent; $integer = (int)$hotels; $sumHotels = $hotels + $sumHotels; $sumCountries = $sumCountries + 1; $countries = new Countries(); $countries->setCountry($CountryName); $countries->setHotels($integer); $em->persist($countries); //$em->flush(); $progress->advance(); } $em->flush(); $progress->finish(); $output->writeln(''); $output->writeln('All is ok. We are saving<info> '.$sumCountries.' </info>countries and<info> '.$sumHotels. ' </info>hotels'); }

When executing a cons.command: doctrine:schema:update , gives an error

An exception occurred while executing 'TRUNCATE TABLE countries':
SQLSTATE [42000]: Syntax error or access violation: 1701 Can't truncate for a key constraint ( symfony . Destinations, CONSTRAINT FK_2D3343C3F92F3E70 FOREIGN KEY ( country_id )

Here is the essence of Destination:

 namespace AppBundle\Entity; use Doctrine\ORM\Mapping as ORM; use AppBundle\Entity\Countries; /** * @ORM\Entity * @ORM\Table(name="destinations") */ class Destination { /** * @ORM\ManyToOne(targetEntity="Countries", inversedBy="destinations") * @ORM\JoinColumn(name="country_id", referencedColumnName="id") */ protected $country; /** * @ORM\Column(type="integer") * @ORM\Id * @ORM\GeneratedValue(strategy="AUTO") */ protected $id; /** * @return mixed */ public function getCountry() { return $this->country; } /** * @param mixed $country */ public function setCountry($country) { $this->country = $country; } }

Here is the essence of the Countries:

 namespace AppBundle\Entity; use Doctrine\ORM\Mapping as ORM; /** * @ORM\Entity * @ORM\Table(name="countries") */ class Countries { /** * @ORM\Column(type="integer") * @ORM\Id * @ORM\GeneratedValue(strategy="AUTO") */ private $id; /** * @ORM\Column(type="string") */ private $country; /** * @ORM\Column(type="integer") */ private $hotels; /** * @ORM\OneToMany(targetEntity="Destination", mappedBy="countries") */ protected $destinations; /** * @return mixed */ public function getDestinations() { return $this->destinations; } /** * @param mixed $destinations */ public function setDestinations($destinations) { $this->destinations = $destinations; } /** * @return mixed */ public function getId() { return $this->id; } /** * @return mixed */ public function getCountry() { return $this->country; } /** * @param mixed $country */ public function setCountry($country) { $this->country = $country; } /** * @return mixed */ public function getHotels() { return $this->hotels; } /** * @param mixed $hotels */ public function setHotels($hotels) { $this->hotels = $hotels; } }

    1 answer 1

    And what's the problem then? The error is clearly written that it does not allow you to clear the restriction table ( CONSTRAINT ).

    Error pure MySql. You cannot clear the table because There are restrictions on related entities. If you are going to bypass the ORM and execute requests directly, then process the errors yourself.

    First you need to clear the associated destinations table. And then countries . And wrap it all in a transaction. But it is better to use DELETE course.

    And if you really want to clear the table, then you can remove the restrictions:

     $cmd = $em->getClassMetadata(Countries::class); $connection = $em->getConnection(); $connection->beginTransaction(); try { $db = $connection->getDatabasePlatform(); $connection->query('SET FOREIGN_KEY_CHECKS=0'); $sql = $db->getTruncateTableSql($cmd->getTableName()); $connection->executeUpdate($sql); $connection->query('SET FOREIGN_KEY_CHECKS=1'); } catch (\Exception $e) { $connection->rollback(); }

    Ps. Be careful, because after this, all your Destination entities will refer to new Countries and there may be confusion.

    In fact, you need not to delete all countries, but to add new ones to the database, if there is none. Since the name of the country is unlikely to change, then you do not need to check the old countries for change, but only to receive new ones. It is better to redo the logic.

    • The same mistake. And where to insert this code? - Maxim Yakubenko 5:53 pm
    • And you can still write code variants, and then I suffer. - Maxim Yakubenko
    • @MaximYakubenko this code is needed instead of your $em->getConnection()->exec( 'TRUNCATE TABLE countries'); - korytoff