Full text search by supplier name and product name

[VadimSaratov]

I have a question about full text search. I have a requirement to fetch search results by supplier name and product name in one query. I found a way to get this by generating different texts for each product but maybe there is a better solution?

For example, I have a supplier name "Dixie Home" and a product name
"Mystic Rain Master"

I would like this product to appear in search results if I use one of the following search texts:
"Dixie Home"
Mystic Rain,
"Dixie Mystic"
"Dixie Home"
Mystic", etc.

[aimeos]

If you add a supplier to a product, the supplier name will be stored together with the other product texts, so this should work out of the box then: https://github.com/aimeos/aimeos-core/b ... #L739-L741

[VadimSaratov]

Thx for your answer.

It works if I use only the supplier name or only the product name, but not some words from the supplier name and some words from the product name in the same request

[aimeos]

Which Aimeos version do you use?

[VadimSaratov]

I use aimeos-laravel 2022.10

[aimeos]

That's strange. Can you please have a look into the mshop_index_text table if there's a record that contains both strings? By default, the product texts and the supplier names are concatenated into one string and stored as one record. Querying the MySQL full text index should then find the words from both, product and supplier name.

[VadimSaratov]

Hi Aimeos, sorry for such a late reply, yes mshop_index_text contains an entry with the supplier name and the product name

I also debugged the index manager and found this SQL query

Code: Select all

SELECT mpro."id"
FROM "mshop_product" mpro
         LEFT JOIN "mshop_index_catalog" AS mindca ON mindca."prodid" = mpro."id"
         LEFT JOIN "mshop_index_text" AS mindte ON mindte."prodid" = mpro."id"
WHERE ((mpro."siteid" IN ('', '1.'))
    AND (mindca."catid" IS NOT NULL AND
         ((mindte."siteid" IN ('', '1.')) AND mindte."langid" = 'en' AND POSITION('engineered' IN mindte."content") > 0 OR
          mpro."code" LIKE 'Engineered%' ESCAPE '#')
        AND ((mpro."status" = 1)
            AND (mpro."type" = 'event' OR mpro."start" IS NULL OR mpro."start" <= '2023-05-23 14:49:00')
            AND (mpro."end" IS NULL OR mpro."end" >= '2023-05-23 14:49:00'))))
GROUP BY mpro."id"
ORDER BY mpro."id"
LIMIT 10000 OFFSET 0

for example I have supplier name "Engineered Floors" and product name "Authentic Living"
and I can get search results if I use any of the word sets that match the order in "Engineered Floors Authentic Floors"

so the search for such strings will work: "Engineered", "Engineered Floors", "Authentic Living", "Floors Authentic", etc.

If I want to mix word order like: "Engineered Authentic", "Engineered Living", "Floors Floors"
then I need to create an extra entry in mshop_index_text with strings that will match that right?

[aimeos]

You are using the Standard index/text manager instead of the Mysql or Pgsql one. Thus, you get the behavior your are describing because the POSITION/LIKE search is very limited in what it can find.

[VadimSaratov]

Ohhh, thanks a lot, it works now