Symfony2使用Select2和Ajax搜索数据库

Question

My FAQ System is working well, but I want to include a search function with Select2.

What I got so far:

Select2 AJAX Script

    <script>
    $("#searchall").select2({
        ajax: {
            url: "/ajax/searchallSelect2",
            dataType: 'json',
            delay: 250,
            data: function (params) {
                return {
                    q: params.term, // search term
                    page: params.page
                };
            },
            processResults: function (data, params) {
                // parse the results into the format expected by Select2
                // since we are using custom formatting functions we do not need to
                // alter the remote JSON data, except to indicate that infinite
                // scrolling can be used
                params.page = params.page || 1;

                return {
                    results: data.items,
                    pagination: {
                        more: (params.page * 30) < data.total_count
                    }
                };
            },
            cache: true
        },
        escapeMarkup: function (markup) { return markup; }, // let our custom formatter work
        minimumInputLength: 1,
    });

AjaxController.php

    /**
 * @Route("/ajax/searchallSelect2", name="ajax_searchall_select2")
 */
public function searchallSelect2Action(Request $request)
{
    dump($request);
    exit;
    return $this->render('', array('name' => $name));
}

Select2 Form in "index.html.twig"

    <select id='searchall' style="width: 300px">
</select>

Firebug console output after typing in the select2 search field:

GET http://localhost:8000/ajax/searchallSelect2?q=test 200 OK 16ms

My question is, how do I implement the database searching? Simply with getRepository in the AjaxController?

        $allfaqs = $this->getDoctrine()
    ->getRepository('AppBundle:Faq')
        ->findAll();

Or do I need to search the database in the Ajax script?

Any help is greatly appreciated, thank you for reading!

Answer 1

Its up to you to write the queryBuilder in controller or in repository (Recommended) and call that in the controller. You shouldn't call ->findAll() in your repository as it will get you all data which you don't need. You need to filter data as per your request query parameter coming from ajax i.e. ?q=test. Below is a code snippet for your Controller.

In your controller :

/**
 * @Route("/ajax/searchallSelect2", name="ajax_searchall_select2")
 */
public function searchallSelect2Action(Request $request)
{
    $key = $request->query->get('q'); 

    // Find rows matching with keyword $key..

    $allfaqs = $this->getDoctrine()
    ->getRepository('AppBundle:Faq')->filterByKey($key);

    // customize your output as per Select2 requirement..
    return new JsonResponse($allfaqs);
}

In your Repository : (Considering you search against a 'name' property in FAQ.)

/**
 * @param string $key
 *
 * @return type
 */
public function filterByKey($key)
{
    $qb = $this->createQueryBuilder('faq')
        ->andWhere('faq.name LIKE :key')
        ->setParameter('key', '%' . $key . '%')
        ->getQuery();

    return $qb->getQuery()->execute();
}

Hope it helps! Let me know your concerns otherwise.