Doctrine searching on Entity without any filters

Question

I was wondering if there is a way to search in entity without applying any filters. For Example I would like to build a textfiled in my template where a ajax post method is calling to a controller with purpose searching the whole entity.

My code:

$user = $this->getDoctrine()
        ->getRepository('AppBundle:QCE_SUBD')
        ->find('%'.$SearchParam.'%')
        ->getQuery();


    $DSUB = $user->getArrayResult();

    dump($DSUB);

I;m not sure how the function should be written, so if some one is willing to help it will be highly appreciate :)


Show source
| database   | doctrine2   | symfony3   2017-01-03 11:01 2 Answers

Answers ( 2 )

  1. 2017-01-04 05:01

    You should just create a function that return a JsonResponse with an array of your result.

    // In your controller
    
    /**                                                                                   
     * @Route("/ajax_action")
     */
    public function ajaxAction(Request $request)    
    {
        // Get the posted parameter from your ajax call
        $searchParam = $request->get('searchParam');
    
        // Request your entity
        $user = $this->getDoctrine()
            ->getRepository('AppBundle:QCE_SUBD')
            ->createQueryBuilder('q')
            ->where('q.username LIKE :searchParam')
            ->orWhere('q.otherColumn LIKE :searchParam')
            ->setParameter('searchParam', '%'.$searchParam.'%')
            ->getQuery();
    
        // Check if it's an ajax call
        if ($request->isXMLHttpRequest()) {         
            return new JsonResponse($user->getArrayResult();
        }
    
        // Return an error
        throw new \Exception('Wrong call!');
    }
    

    For the search part you need to implement a full text search, here is a tutorial on how to implement it : http://ourcodeworld.com/articles/read/90/how-to-implement-fulltext-search-mysql-with-doctrine-and-symfony-3

    P.S : You should be sure of what you need in your query. If you want it to be scalable, you should take a look at better search engine method as ElasticSearch or Solr.

  2. 2017-01-04 12:01

    You can inspire yourself from the following function. It iterates dynamically through all fields of the entity and depending on the type of the field a condition is applied to the query builder:

    /**
     * Creates the query builder used to get the results of the search query
     * performed by the user in the "search" view with a given "keyword".
     *
     * @param array       $entityConfig
     * @param string      $searchQuery
     * @param string|null $sortField
     * @param string|null $sortDirection
     * @param string|null $dqlFilter
     *
     * @return DoctrineQueryBuilder
     */
    public function createSearchQueryBuilder(array $entityConfig, $searchQuery, $sortField = null, $sortDirection = null, $dqlFilter = null)
    {
        /* @var EntityManager */
        $em = $this->doctrine->getManagerForClass($entityConfig['class']);
        /* @var DoctrineQueryBuilder */
        $queryBuilder = $em->createQueryBuilder()
            ->select('entity')
            ->from($entityConfig['class'], 'entity')
        ;
    
        $queryParameters = array();
        foreach ($entityConfig['search']['fields'] as $name => $metadata) {
            $isNumericField = in_array($metadata['dataType'], array('integer', 'number', 'smallint', 'bigint', 'decimal', 'float'));
            $isTextField = in_array($metadata['dataType'], array('string', 'text', 'guid'));
    
            if ($isNumericField && is_numeric($searchQuery)) {
                $queryBuilder->orWhere(sprintf('entity.%s = :exact_query', $name));
                // adding '0' turns the string into a numeric value 
                $queryParameters['exact_query'] = 0 + $searchQuery;
            } elseif ($isTextField) {
                $searchQuery = strtolower($searchQuery);
    
                $queryBuilder->orWhere(sprintf('LOWER(entity.%s) LIKE :fuzzy_query', $name));
                $queryParameters['fuzzy_query'] = '%'.$searchQuery.'%';
    
                $queryBuilder->orWhere(sprintf('LOWER(entity.%s) IN (:words_query)', $name));
                $queryParameters['words_query'] = explode(' ', $searchQuery);
            }
        }
    
        if (0 !== count($queryParameters)) {
            $queryBuilder->setParameters($queryParameters);
        }
    
        if (!empty($dqlFilter)) {
            $queryBuilder->andWhere($dqlFilter);
        }
    
        if (null !== $sortField) {
            $queryBuilder->orderBy('entity.'.$sortField, $sortDirection ?: 'DESC');
        }
    
        return $queryBuilder;
    }
    

    The source code comes from the EasyAdminBundle.

◀ Go back