Doctrine QueryBuilder sorts results by field priority

Question

I have an object Servicewith fields title, tagsand description. When searching for a service with QueryBuilder, I can get results sorted by field priority. For example, when I search for a term php, I want to get services with phpin their header at the top of the list, then services with phpin my tags and services with the search term in their description as the 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 is to perform a series of statements UNIONand then knock out duplicates at the same time, giving weight.

(Unchecked pseudo-SQL, ):

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

. :.

    $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 sql insted dql orm

Answer 3

I . , , , .

-, , Service , :

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

(foreach)

, :

• -,
  • -
  • -
  • serach

findSerivesByGivenSearchCriteria() ,

:

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;
}

"uniqness", Doctrine INDEX BY HIDDEN

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

INDEX BY QueryBuilder → fooobar.com/questions/400411/...

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

UNION?

symfony doctrine UNION, . . UNION Doctrine 2.0

, UNION Doctrine, NativeQuery ( ResultSetMappingBuilder )

Answer 4

(: @thinice query) . Symfony

class UserController extends Controller
{
    public function indexAction()
    {
        $conn = $this->get('database_connection');
        $users = $conn->fetchAll('SELECT * FROM users');

        // ...
    }
}