Doctrine QueryBuilder sort results by field priority

Question

I have Service entity with title, tags and description fields. When searching service with QueryBuilder how can I get results sorted by field priority. For example, when I search term php I want get services with php in their title at the top of the list, then services with php in their tags and services with search term in their description as last.

This is part of my Querybuilder:

    $qb = $this->createQueryBuilder('service');
    $qb->leftJoin('service.tags', 'tag');

    $conditions = array($conditions[] = $qb->expr()->eq('service.enabled', true));
    $conditions[] = $qb->expr()->eq('service.category', $categoryId);
    $term = '%' . $term . '%';
    $conditions[] = $qb->expr()->orX(
            $qb->expr()->like('service.title', "'$term'"),
            $qb->expr()->like('service.description',  "'$term'"),
            $qb->expr()->like('tag.name', "'$term'")
        );

    $conditions = call_user_func_array(array($qb->expr(), 'andX'), $conditions);

    $qb->where($conditions);

Answer 1

The best way to do this would be to perform a series of UNION statements and then weed out the duplicates at the same time giving weight.

(Unchecked pseudo-SQL to give you the idea):

SELECT id,title,tag,SUM(weight) score
FROM (
   SELECT id,title,tag, 100 as weight FROM service WHERE title LIKE '%foo%'
   UNION ALL
   SELECT id,title,tag, 10 as weight FROM service WHERE tags LIKE '%foo%'
   UNION ALL
   SELECT id,title,tag, 1 as weight FROM service WHERE description LIKE '%foo%'
) t
GROUP BY id
ORDER BY score DESC /* This sort by probably won't work; might need to do it a different way, but you get the idea */

Answer 2

You can use native query for this. Ex.:

    $em = $this->get('doctrine')->getManager();

    $sql = "
        select *
        from service s
        where
            s.title like '%xxx%'
            or s.tags like '%xxx%'
            or s.description  like '%xxx%'
        order by 
            s.title like '%xxx%' desc,
            s.tags like '%xxx%' desc,
            s.description  like '%xxx%' desc
    ";

    $rsm = new \Doctrine\ORM\Query\ResultSetMappingBuilder($em);
    $rsm->addRootEntityFromClassMetadata('\You\Entity\Service\Class', 's');

    $query = $em->createNativeQuery($sql, $rsm);

    $data = $query->getResult();

    dump($data);

http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/native-sql.html how to use sql insted of dql in orm

Answer 3

I would not try get the desired results in only one query. It will be too complicated and you'll spent to much time trying get what you want.

First, I assume you'd like to get a sorted Array of Service entities like this:

/** @var Service[] $results **/
$results = $this->getDoctrine()->getManager()->getRepository('YourBundle:YourServiceRepo')->findSerivesByGivenSerchCriteria($yourConditions);

so you can simply iterate (foreach) through it

If it's the case, concider following:

• create three separate methods whitin your service-repository to get
  1. services with search-criteria in their title - first priority
  2. services with search-criteria in their tags - second priority
  3. services with serach-criteria in their desc - third priority

and in your findSerivesByGivenSearchCriteria()method you call all three of them and could combine the found results in any order i'd like

for Example:

public function findSerivesByGivenSerchCriteria( $searchTirm ) {
    $foundServicesByTitle = $this->findServicesBySearchCriteriaInTitle( $searachTirm );
    $foundServicesByTag = $this->findServicesBySearchCriteriaInTags( $searchTirm );
    $foundServicesByDesc = $this->findServicesBySearchCriteriaInDesc( $searchTirm );

    // Hier you can combine found results in any order
    $results = [];
    if( false === empty($foundServicesByTitle ) )
    {
    // for example with array_merge ...
    // NOTE: If you choose array_merge() you have to make sure your $foundServicesByTitle, $foundServicesByTag, $foundServicesByDesc have unique array-indexes
    //       And you get Results like
    //       array( 'unique_id_1' => ServiceEntity, 'unique_id_2' => ServiceEntity ... ) 
     $results = array_merge($results, $foundServicesByTitle);
    }
    // .. do the same with other two
    return $results;
}

To get the "uniqness" try to combine Doctrine's INDEX BY and HIDDEN

INDEX BY -> http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#using-index-by

INDEX BY in QueryBuilder -> https://stackoverflow.com/a/15120793/348193

HIDDEN -> https://michelsalib.com/2012/03/04/doctrine2-ninja-with-the-hidden-keyword/

Why I wouldn't choose the UNION way?

Since you work with symfony and doctrine UNION isn't the best way. See How to write UNION in Doctrine 2.0

As you can see, UNION isn't supported by Doctrine and you have to use NativeQuery (which could be frustrating with ResultSetMappingBuilder and corect mapping and turning raw-results in to desired entities)