不同的搜索词顺序在Symfony查询中应该具有相同的结果

Question

Currently, my search query search only exact match and correct order. But I need that order of words is not important.

For example, news title: Neymar to PSG. I got correct results in next searches

• Neymar PSG
• Neymaar to PSG

But i have no results for:

• PSG Neymar

This is my code:

// Create search tearm partial 
$searchTerms = explode(' ', $this->query->getTerm());
$searchTermPartial = '';
foreach($searchTerms as $searchTerm) {
    $searchTermPartial = $searchTermPartial . '%' . $searchTerm .'%';
}

//Search part in Query
    ->andWhere(
        $queryBuilder->expr()->orX(
            $queryBuilder->expr()->like(
                $queryBuilder->expr()->concat('translation.title', $queryBuilder->expr()->concat(
                    $queryBuilder->expr()->literal(' '), 'translation.shortDescription')), ":searchTermPartial"),
            $queryBuilder->expr()->eq('o.code', ":searchTerm")
        )

How to make that word order is not important ?

Answer 1

This is what is the content of your $searchTermPartial

//%PSG%%Neymar%

I have a table like this

CREATE TABLE public.rol
(
  id integer NOT NULL,
  role character varying(255) NOT NULL,
  CONSTRAINT rol_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.rol
  OWNER TO postgres;

the values of role field are [PSG Neymar,Neymar PSG,PSG, Neymar] (4 different rows)

When I execute the query with the content above this is the result

query select * from rol where role like '%PSG%%Neymar%'

result 1;"PSG Neymar"

the problem is that you are building wrong the parameter you should use it this way:

    foreach ($searchTerms as $key => $term) {
//.....
                $qb->andWhere($qb->expr()->like('r.role', ':term' . $key))
                    //.....
                    ->setParameter('term' . $key, '%' . $term . '%');
//.....
            }

the above code will output the following query

select * from rol where role like '%PSG%' and role like '%Neymar%'

with this result

1;"PSG Neymar"

2;"Neymar PSG"

If you use orWhere() option you could get the four records.

Hope it helps