我得到了以下实体:
<?php
namespace AppBundle\Entity;
use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
/**
* Productnum
*
* @ORM\Table(name="productnum")
* @ORM\Entity
*/
class Productnum
{
/**
* @var object
*
* @ORM\OneToMany(
* targetEntity="AppBundle\Entity\Products",
* mappedBy="productnum",
* cascade={"persist", "remove"}
* )
*/
protected $productnumInverse;
/**
* Constructor
*/
public function __construct()
{
$this->productnumInverse = new ArrayCollection();
}
/**
* Get productnumInverse
*
* @return Collection
*/
public function getProductnumInverse()
{
return $this->productnumInverse;
}
}
<?php
namespace AppBundle\Entity;
use Doctrine\ORM\Mapping as ORM;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
/**
* Products
*
* @ORM\Table(name="products")
* @ORM\Entity
*/
class Products
{
/**
* @var \AppBundle\Entity\Productnum
*
* @ORM\ManyToOne(targetEntity="AppBundle\Entity\Productnum", inversedBy="productnumInverse")
* @ORM\JoinColumns({
* @ORM\JoinColumn(name="productnum_id", referencedColumnName="row_id")
* })
*/
public $productnum;
/**
* @var object
*
* @ORM\OneToMany(
* targetEntity="AppBundle\Entity\Product_region", fetch="EAGER",
* mappedBy="productid",
* cascade={"persist", "remove"}
* )
*/
protected $productInverse;
/**
* Constructor
*/
public function __construct()
{
$this->productInverse = new ArrayCollection();
}
}
<?php
namespace AppBundle\Entity;
use AppBundle\AppBundle;
use AppBundle\Entity\Productnum_filial;
use Doctrine\ORM\Mapping as ORM;
/**
* Productnum_region
*
* @ORM\Table(name="productnum_region")
* @ORM\Entity
*/
class Productnum_region
{
//regular getters and setters here...
}
还有一个映射实体:
<?php
namespace AppBundle\Entity;
use Doctrine\ORM\Mapping as ORM;
/**
* Product_region
*
* @ORM\Table(name="product_region")
* @ORM\Entity
*/
class Product_region
{
/**
* @ORM\Column(name="id", type="integer")
* @ORM\Id
* @ORM\GeneratedValue(strategy="IDENTITY")
*/
private $id;
/**
* @var \AppBundle\Entity\Products
*
* @ORM\ManyToOne(targetEntity="AppBundle\Entity\Products")
* @ORM\JoinColumns({
* @ORM\JoinColumn(name="product_id", referencedColumnName="row_id")
* })
*
*/
private $productid;
/**
* @var \AppBundle\Entity\Productnum_region
*
* @ORM\ManyToOne(targetEntity="AppBundle\Entity\Productnum_region")
* @ORM\JoinColumns({
* @ORM\JoinColumn(name="region_id", referencedColumnName="id")
* })
*/
private $regionid;
}
在我的控制器中,我得到了以下代码
$sql = sprintf("SELECT p FROM 'AppBundle:Productnum' p");
$productnums = $em->createQuery($sql)
->setFirstResult($start)
->setMaxResults($length)
->getResult();
$data = [];
foreach($productnums as $productnum) {
$prods = '';
foreach($productnum->getProductnumInverse() as $product) {
$filials = [];
$regions = [];
if($product && $product->getAllregions()){
$regions[] = $filials[] = 'All';
} elseif($product && $product->getAllfilials()){
$filials[] = 'All';
$regs = $product->getProductInverse();
foreach($regs as $reg){
$regions[] = $reg->getRegionid()->getName();
}
}elseif($product){
$regs = $product->getProductInverse();
foreach($regs as $reg){
$fil = $reg->getRegionid()->getFilial()->getName();
if(!in_array($fil, $filials)){
$filials[] = $fil;
}
$regions[] = $reg->getRegionid()->getName();
}
}
}
问题是,在我的本地机器上,此代码运行良好,但在远程服务器上运行速度很慢。我在我的本地机器和服务器上打开了 Symfony 分析器,看到在我的本地机器上(这没问题)需要 336 个 DB(总共 1.5 秒)查询来完成大部分查询,如下所示;
SELECT t0.id AS id_1, t0.name AS name_2, t0.code AS code_3, t0.filial_id AS filial_id_4 FROM productnum_region t0 WHERE t0.id = ?
Parameters: [0 => 100]
和
SELECT t0.row_id AS row_id_1, ... t0.productnum_id AS productnum_id_21, t22.id AS id_23, t22.product_id AS product_id_24, t22.region_id AS region_id_25 FROM products t0 LEFT JOIN product_region t22 ON t22.product_id = t0.row_id WHERE t0.productnum_id = ?
Parameters: [0 => 945]
在我的服务器上总共有 36 个查询(总共 20 秒,BAD),其中一个(可能是最慢的)如下:
SELECT t0.row_id AS row_id_1, ... t0.productnum_id AS productnum_id_21, t22.id AS id_23, t22.product_id AS product_id_24, t22.region_id AS region_id_25
FROM products t0 LEFT JOIN product_region t22 ON t22.product_id = t0.row_id WHERE t0.row_id IN (?)
Parameters: [ 0 => [ 0 => 2, 1 => 97, 2 => 212, 3 => 225, 4 => 297, 5 => 355, 6 => 356, 7 => 482, 8 => 571, 9 => 737, 10 => 789
...MANY MANY MANY data here...
所以问题是不同机器上的相同代码怎么会转换为不同的查询,以及如何避免这种情况?
谢谢
看起来您的数据库设计或代码可能存在整体错误,需要您将 4 个foreach
循环和 2 个if
语句全部嵌套在一起。
要专门回答您的问题 - 您需要在查询中加入相应的实体 - Doctrine 不会为您做这件事。因此,当您执行此操作时:
foreach($productnum->getProductnumInverse() as $product) {
通过该循环的每次迭代,Doctrine 都会单独查询 that $product
,因为它没有在您的原始查询中选择它。这就是为什么您会看到 336 个数据库查询,而实际上您应该只看到一个。代替:
SELECT p FROM 'AppBundle:Productnum' p
您的查询应该更像这样:
SELECT p, pi, pip, pir
FROM AppBundle:Productnum p
JOIN p.productnumInverse pi
JOIN pi.product pip
JOIN pi.region pir
这应该会大大减少您正在运行的查询数量 - 理想情况下,您应该将检索所有数据的查询减少到 1 个。简而言之,Doctrine 不会加入关联实体,除非您明确告诉它。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句