doupu7651 2014-12-30 21:36
浏览 21

限制Symfony 2表单实体选择的SQL查询数量

I want to add to form a drop-down list to choose related entity. The problem is, that i need to include on list value from another one entity. Main entity (form) is 'Warehouse' related with 'Station' (on drop-down list) related with 'StationBrand'. I need to include station brand name on stations list.

Fragment of form class:

public function buildForm(FormBuilderInterface $builder, array $options)
{
    $builder
        ->add('numa1', 'number', array('precision' => 2))
        ->add('numa2', 'number', array('precision' => 2))
        ->add('numb1', 'number', array('precision' => 2))
        ->add('numb2', 'number', array('precision' => 2))
        ->add('station', 'entity', array(
            'required' => true,
            'class' => 'GUGUPanelBundle:Station'
        ));
}

Station entity:

class Station extends EntityBase {
/**
 * @ORM\Column(type="integer", options={"unsigned"=true})
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
protected $id;

/**
 * @ORM\Column(type="string", length=255)
 */
protected $adress;

/**
 * @ORM\Column(type="string", length=100)
 */
protected $city;

/**
 * @ORM\ManyToOne(targetEntity="StationBrand", inversedBy="stations")
 * @ORM\JoinColumn(name="brand_id", referencedColumnName="id")
 */
protected $brand;

/**
 * @ORM\OneToMany(targetEntity="Warehouse", mappedBy="station")
 */
protected $warehouses;

public function __toString() {
    $brand = $this->getBrand()->getName();
    return "$brand ({$this->getAdress()}, {$this->getCity()})";
}

Station brand entity:

class StationBrand extends EntityBase {
/**
 * @ORM\Column(type="integer", options={"unsigned"=true})
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
protected $id;

/**
 * @ORM\Column(type="string", length=100)
 */
protected $name;

/**
 * @ORM\OneToMany(targetEntity="Station", mappedBy="brand")
 */
protected $stations;
}

It works, but generates for N stations N+1 SQL queries - one for list and for each station query to get brand name. I want to minimise it to one query by adding to form class query_builder option:

public function buildForm(FormBuilderInterface $builder, array $options)
{
    $builder
        ->add('numa1', 'number', array('precision' => 2))
        ->add('numa2', 'number', array('precision' => 2))
        ->add('numb1', 'number', array('precision' => 2))
        ->add('numb2', 'number', array('precision' => 2))
        ->add('station', 'entity', array(
            'required' => true,
            'class' => 'GUGUPanelBundle:Station',
            'query_builder' => function(EntityRepository $er) {
                return $er->createQueryBuilder('fs')
                        ->select(['fsb', 'fs'])
                        ->join('GUGUPanelBundle:StationBrand', 'fsb', 'WITH', 'fs.brand = fsb.id')
                        ->where('fs.deleted IS NULL AND fsb.deleted IS NULL');
            }
        ));
}

As a result of above change i have single query, but there is somehow new option added. AS i cant make a good screenshot of expanded list, there is dump of html of select tag:

    <option value="1">Station1 (street 1, City1)</option>
    <option value="" selected="selected">Station3</option>
    <option value="2">Station2 (street 2, City2)</option>
    <option value="3">Station3 (street 3, City3)</option>

Always as second option there is a copy of last station name without address and city and it is always selected by default.

Why this happens? When i remove from query_builder line with select(['fs', 'fsb']) that strange option disappears but again i have N+1 SQL queries.

What i did wrong?

  • 写回答

0条回答 默认 最新

    报告相同问题?