Search request with "AND" argument instead of "OR"

[jra83]

Q	A
Bug report?	[ ]
Feature request?	[ ]
Support request?	[ x ]

Hi everyone, I have been working on a cocorico for several day only, so big newby here. Sorry if my question has been answered but i couldn't find any clue... For my project I would like to make a search that takes into account the categories selected with a AND style condition. Ex: I have the following listings :

list1 ClassA / ClassB list2 ClassA list3 Class B

If I search ClassA I want 1 and 2 but if I search A&B i just want just and today it's coded so that I have 1/2/3 as results.

I did my best to dig into the code but I m stuck. So far I have understood that it begins in listingsearchcontrol.php

  `public function searchAction(Request $request)
     {
        //For drag map mode
         $isXmlHttpRequest = $request->isXmlHttpRequest() ? true : false;

    $markers = array('listingsIds' => array(), 'markers' => array());
    $listings = new \ArrayIterator();
    $nbListings = 0;

    /** @var ListingSearchRequest $listingSearchRequest */
    $listingSearchRequest = $this->get('cocorico.listing_search_request');
    $isXmlHttpRequest ? $listingSearchRequest->setSortBy('distance') : null;
    $form = $this->createSearchResultForm($listingSearchRequest);

    $form->handleRequest($request);

    if ($form->isSubmitted() && $form->isValid()) {
        $listingSearchRequest = $form->getData();

        $results = $this->get("cocorico.listing_search.manager")->search(
            $listingSearchRequest,
            $request->getLocale()
        );
        $nbListings = $results->count();
        $listings = $results->getIterator();
        $markers = $this->getMarkers($request, $results, $listings);

        //Persist similar listings id
        $listingSearchRequest->setSimilarListings($markers['listingsIds']);

        //Persist listing search request in session
        !$isXmlHttpRequest ? $this->get('session')->set('listing_search_request', $listingSearchRequest) : null;
    } else {
        foreach ($form->getErrors(true) as $error) {
            $this->get('session')->getFlashBag()->add(
                'error',
                /** @Ignore */
                $this->get('translator')->trans($error->getMessage(), $error->getMessageParameters(), 'cocorico')
            );
        }
    }`

I have watched in the listingsearchmanage.php with the function search ` public function search(ListingSearchRequest $listingSearchRequest, $locale) { //Select $queryBuilder = $this->getRepository()->getFindQueryBuilder();

    //Geo location
    $queryBuilder = $this->getSearchByGeoLocationQueryBuilder($listingSearchRequest, $queryBuilder);

    $queryBuilder
        ->andWhere('t.locale = :locale')
        ->andWhere('l.status = :listingStatus')
        ->setParameter('locale', $locale)
        ->setParameter('listingStatus', Listing::STATUS_PUBLISHED);

    //Dates
    $queryBuilder = $this->getSearchByDateQueryBuilder($listingSearchRequest, $queryBuilder);

    //Prices
    $priceRange = $listingSearchRequest->getPriceRange();
    if ($priceRange->getMin() && $priceRange->getMax()) {
        $queryBuilder
            ->andWhere('l.price BETWEEN :minPrice AND :maxPrice')
            ->setParameter('minPrice', $priceRange->getMin())
            ->setParameter('maxPrice', $priceRange->getMax());
    }

    //Categories
    $categories = $listingSearchRequest->getCategories();
    if (count($categories)) {
        $queryBuilder
          ->andWhere("llcat.category IN (:categories)")
            ->setParameter("categories", $categories);
    }

    //Characteristics
    $queryBuilder = $this->getSearchByCharacteristicsQueryBuilder($listingSearchRequest, $queryBuilder);

    //Order
    switch ($listingSearchRequest->getSortBy()) {
        case 'price':
            $queryBuilder->orderBy("l.price", "ASC");
            break;
        case 'distance':
            $queryBuilder->orderBy("distance", "ASC");
            break;
        default:
            $queryBuilder->orderBy("distance", "ASC");
            break;
    }
    $queryBuilder->addOrderBy("l.averageRating", "DESC");
    $queryBuilder->addOrderBy("l.adminNotation", "DESC");

    if (!$listingSearchRequest->getIsXmlHttpRequest()) {
        $event = new ListingSearchEvent($listingSearchRequest, $queryBuilder);
        $this->dispatcher->dispatch(ListingSearchEvents::LISTING_SEARCH, $event);
        $queryBuilder = $event->getQueryBuilder();
    }

    //Pagination
    if ($listingSearchRequest->getMaxPerPage()) {
        $queryBuilder
            ->setFirstResult(($listingSearchRequest->getPage() - 1) * $listingSearchRequest->getMaxPerPage())
            ->setMaxResults($listingSearchRequest->getMaxPerPage());
    }

    //Query
    $query = $queryBuilder->getQuery();
    $query->setHydrationMode(Query::HYDRATE_ARRAY);

    return new Paginator($query);`

And finally the listingrepository.php that gives the initial construction of the sql query :

      `class ListingRepository extends EntityRepository
                   {
                   /**
                   *
                   * @return \Doctrine\ORM\QueryBuilder
                   */
                  public function getFindQueryBuilder()
                 {
        $queryBuilder = $this->_em->createQueryBuilder()
        //Select
        ->select("partial l.{id, price, averageRating, certified, createdAt, commentCount}")
        ->addSelect("partial t.{id, locale, slug, title, description}")
        ->addSelect("partial llcat.{id, listing, category}")
        ->addSelect("partial ca.{id, lft, lvl, rgt, root}")
        ->addSelect("partial cat.{id, locale, name}")
        ->addSelect("partial i.{id, name}")
        ->addSelect("partial u.{id, firstName}")
        //->addSelect("partial ln.{id}")
        ->addSelect("partial ln.{id, city, route, country}")
        ->addSelect("partial co.{id, lat, lng}")
        ->addSelect("partial ui.{id, name}")
        ->addSelect("'' AS DUMMY")//

        //From
        ->from('CocoricoCoreBundle:Listing', 'l')
        ->leftJoin('l.translations', 't')
        ->leftJoin('l.listingListingCategories', 'llcat')
        ->leftJoin('llcat.category', 'ca')
        //Join::WITH: Avoid exclusion of listings with no categories (disable inner join)
        ->leftJoin('ca.translations', 'cat', Query\Expr\Join::WITH, 'cat.locale = :locale')
        ->leftJoin('l.images', 'i')
        ->leftJoin('l.user', 'u')
        ->leftJoin('u.images', 'ui', Query\Expr\Join::WITH, 'ui.position = 1')
        ->leftJoin('l.location', 'ln')
        ->leftJoin('ln.coordinate', 'co');
        // ->leftJoin('co.country', 'cy');

        //  $queryBuilder
       // ->addGroupBy('l.id');

       return $queryBuilder;`

But from all of this I cannot find a way to execute the way I want the research. I have watched in a lot of other files but I might have missed a step in the process. Could you please help me with this ?

thanks very much, JR

[jra83]

So I think I have the right way to implemetn it now but I am sturggling with syntax. So the SQL query could look like this in the repository finqueryBuilder : And I would add a "andwhere" clause on the allcat in the searchmanager with the $categories from the request originally in an array implode in a string. I think it should work but I can't find how to write it. Could you indicate me how to add the group_concat in the select, and to build a new andwhre condition ?

Much appriciated. JR

[jra83]

Hi everyone, I ended with a solution (not the best but still) so I give it here if it can help some lost soul. I used a having clause + a group_concat function. In the listingsearchmanager.php I added :

$categories = $listingSearchRequest->getCategories(); asort($categories); $strgcategories = implode(',',$categories); if (count($categories)) { $queryBuilder ->andWhere('llcat.category IN (:categories)') ->having("group_concat(llcat.listingcategoryid order by llcat.listingcategoryid asc) = :strgcategories") ->setParameter('categories', $categories) ->setParameter('strgcategories',$strgcategories);

And in the listingRepository.php I added :

$queryBuilder ->addGroupBy('l.id');

I also added the DQL class GroupConcat and updated the config.yml

This is not perfect, I would have prefered to modify the entities in order to add a field in the listinglistingcategory containing the concatenation and then be able to use a where clause on this field, but too complicated for me...

Now the behavoir is as wished. Only the listing with all the categories requested are showed.

Hope It will spare time for people wanting the same results ;)