使用Symfony2和Doctrine 2从数据库加载大数据 [英] Loading large data from database with Symfony2 and Doctrine 2

问题描述

我是Symfony2的初学者。

I'm beginner on Symfony2.

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

I have a Regions-Countries-States-Cities database with more of 2,000,000 results. I have 8 entities:

Region (recursive with itself) - RegionTranslation

Country - CountryTranslation

State (recursive with itself) - StateTranslation

City - CityTranslation

我想加载一个国家列表（例如在一个下拉列表中只有250个寄存器）Symfony + Doctrine加载所有实体结构（所有国家的所有状态，所有州的所有城市及其各自的翻译）。

The thing is that when I want to load a countries list (only 250 registers in a pulldown, for example) Symfony+Doctrine load all entities structure (all states of all countries, and all cities of all states, with their respective translations).

我认为它需要很多内存。

I think that it spends a lot of memory.

这是正确的方法是什么？我可以只加载这个结构的国家（和翻译）吗？任何想法？

What's the correct method to do it? Can I load only Country (and translations) with this structure? Any idea?

推荐答案

我有同样的问题，最好的办法是使用select2的ajax加载（ http://ivaynberg.github.com/select2/ ） ），这将在搜索框中提供有限数量的项目，还可以根据框中键入的内容进行窄搜索。

I have had the same problem for objected that are unassociated. Your best bet is to use select2's ajax loading (http://ivaynberg.github.com/select2/), which will give a limited number of items in the search box, and also narrow searches down by what is typed in the box.

有些事情需要编码：

一个javascript文件：

A javascript file:

      $(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 };
    }
    }
    });

    }

控制器中的jsonAction：

A jsonAction in the controller:

    /**
    * 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
         )
      );
    }

树枝模板（json.html.twig，

A twig template (json.html.twig, possibly customized to display more)

    {{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控件将必须通过'em'to the form builder：

Your Controller using the select2 control will have to pass the 'em' to the form builder:

  $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)
  );

这篇关于使用Symfony2和Doctrine 2从数据库加载大数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！