CODEWITHSUNDEEP
databasedoctrine-ormsymfony
Metio_1993
Metio_1993

Reputation: 171

Doctrine searching on Entity without any filters

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 :)

Upvotes: 3

Views: 358

Answers (2)

COil
COil

Reputation: 7596

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.

Upvotes: 1

davidbonachera
davidbonachera

Reputation: 5670

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.

Upvotes: 1

Related Questions