database - 使用 Symfony2 和 Doctrine 2 从数据库加载大数据

Question

我是 Symfony2 的初学者。

我有一个 Regions-Countries-States-Cities 数据库，其中包含超过 2,000,000 个结果。我有 8 个实体:

Region (recursive with itself) - RegionTranslationCountry - CountryTranslationState (recursive with itself) - StateTranslationCity - CityTranslation

问题是，当我想加载一个国家列表(例如，下拉列表中只有 250 个寄存器)时，Symfony+Doctrine 加载所有实体结构(所有国家的所有州，所有州的所有城市，及其各自的翻译)。

我认为它会占用大量内存。

正确的做法是什么？我可以只加载具有这种结构的国家(和翻译)吗？有什么想法吗？

Answer 1

对于未关联的对象，我遇到了同样的问题。最好的办法是使用 select2 的 ajax 加载 ( http://ivaynberg.github.com/select2/ )，这将在搜索框中提供有限数量的项目，并根据框中键入的内容缩小搜索范围。

一些事情需要编码:

一个javascript文件:

      $(document).ready(function(){
        $('.select2thing').select2({
          minimumInputLength:1
        ,width: "100%"
        ,ajax: {
          url: <<path>> + "entity/json"
         ,dataType: 'jsonp'
         ,quitMillis: 100
         ,data: function (term, page) {
         return {
            q: term, // search term
            limit: 20,
            page: page
         };
        }
    ,results: function (data, page) {
      var more = (page * 20) < data.total;
      return { results: data.objects, more: more };
    }
    }
    });

    }

Controller 中的一个 jsonAction:

    /**
    * Lists all Thing entities return in json format
    *
    */
    public function jsonAction(Request $request)
    {
      $em = $this->getDoctrine()->getManager();
      $rep = $em->getRepository('yourBundle:Thing');
      $qb = $rep->createQueryBuilder('e');

      $limit = $request->query->get('limit');
      $current = $request->query->get('current');
      $page=$request->query->get('page');
      $queries=$request->query->get('q');
      $qarray=explode(",", $queries);

      $entities=$rep->getJSON($qarray, $page, $limit);
      $total=$rep->getJSONCount($qarray);
      $callback=$request->query->get('callback');

      return $this->render('yourBundle:Thing:json.html.twig'
         , array(
             'entities'  => $entities
            ,'callback'  => $callback
            ,'total'     => $total
         )
      );
    }

一个 twig 模板(json.html.twig，可能会定制以显示更多内容)

    {{callback}}(
    { "objects" :
    [
    {% for entity in entities %}
    { "id": "{{entity.id}}", "text": "{{entity}}""}
    {% if not loop.last %},{% endif %}
    {% endfor %}
    ],
     "total": {{total}}
    }
    )

变压器:

    use Symfony\Component\Form\DataTransformerInterface;
    use Symfony\Component\Form\Exception\TransformationFailedException;
    use Doctrine\Common\Persistence\ObjectManager;
    use yourBundle\Entity\Thing;

    class ThingTransformer implements DataTransformerInterface
    {
        /**
         * @var ObjectManager
         */
        private $em;

        /**
         * @param ObjectManager $em
         */
        public function __construct(ObjectManager $em)
        {
            $this->em = $em;
        }

        /**
         * Transforms an object (thing) to a string (id).
         *
         * @param  Issue|null $thing
         * @return string
         */
        public function transform($thing)
        {
            if (null === $thing) {return "";}
            if (is_object($thing) && "Doctrine\ORM\PersistentCollection"==get_class($thing)){
              $entity->map(function ($ob){return $ob->getId();});
              return implode(",",$thing->toArray());
            }
            return $thing;
        }

        /**
         * Transforms a string (id) to an object (thing).
         *
         * @param  string $id
         * @return Issue|null
         * @throws TransformationFailedException if object (thing) is not found.
         */
        public function reverseTransform($id)
        {
            if (!$id) {
                return null;
            }

            //if (is_array($id)){
              $qb=$this->em
                ->getRepository('yourBundle:Thing')
                ->createQueryBuilder('t');
              $thing=$qb->andWhere($qb->expr()->in('t.id', $id))->getQuery()->getResult();

              if (null === $entity) {
                throw new TransformationFailedException(sprintf(
                    'A thing with id "%s" does not exist!',
                    $id
                ));
            }

            return $thing;
        }
    }

您使用 select2 控件的 Controller 必须将“em”传递给表单生成器:

  $editForm = $this->createForm(new ThingType()
     ,$entity
     ,array(
        'attr' => array(
            'securitycontext' => $sc
           ,'em'              => $this->getDoctrine()
                                      ->getEntityManager()
        )
     )
  );

然后在您的表单中输入:

  if (isset($options['attr']['em'])){ $em = $options['attr']['em'];} else {$em=null;}

  $transformer = new ThingTransformer($em);
  $builder->add(
      $builder->create('thing'
         ,'hidden'
         ,array(
             'by_reference' => false
            ,'required' => false
            ,'attr' => array(
                'class' => 'select2thing'
            )
         )
      )
      ->prependNormTransformer($transformer)
  );