I have a query that searches a keyword from different columns
$queryBuilder = $this->entityManager->getRepository(Contact::class)->createQueryBuilder("o");
$queryBuilder->where($queryBuilder->expr()->orX(
$queryBuilder->expr()->like("o.FirstName", ":name"),
$queryBuilder->expr()->like("o.Surname", ":name"),
$queryBuilder->expr()->like("o.Email", ":name"),
$queryBuilder->expr()->like("o.Biography", ":name"),
$queryBuilder->expr()->like("o.Department", ":name"),
$queryBuilder->expr()->like("p.Name", ":name"),
$queryBuilder->expr()->like("pc.Name", ":name"),
$queryBuilder->expr()->like("CONCAT(o.FirstName, ' ', o.Surname)", ":name")
))
->leftJoin(ContactPublication::class, "c", "WITH", "o.ID = c.Contact_ID")
->leftJoin(Publication::class, "p", "WITH", "p.ID = c.Publication_ID")
->leftJoin(Publication::class, "pc", "WITH", "pc.Contact_ID = o.ID")
->setParameter("name", "%" . $value . "%");
$query = $queryBuilder->getQuery();
$contacts = $query->getResult();
The only issue is that I need to bring the rows that matches the most with two columns o.FirstName and o.Surname.
For instance, if I have a keyword abc and this keyword brings me three rows.
First row has abc matched in its o.Biography column
Second row has abc matched in its o.FirstName column
Third Row has abc matched in its o.Surname column
I want to get data as sorted like:
1st row abc matched in its o.FirstName column
2nd row abc matched in its o.Surname column
3rd row abc matched in its o.Biography column
Is there a filter in doctrine that does that?