selector Selector with multiple words to reduce results

[godmok]

Hi everybody,

I am working on a project where I have a search form with an input field, that should search over a lot of PW fields (text and pagefields).

What I am trying to get is something like this:

$pages->find("title|body|sidebar|summary|intro|keywords|country.title%='word1 word2 word3'");

The problem is now the search input. If I insert two words (word1 word2) it should look into every field, just like:

$pages->find("title|body|sidebar|summary|intro|keywords|country.title%=word1, title|body|sidebar|summary|intro|keywords|country.title%=word2");

So far so good. but there is the possibility to add up a lot of words, and then MySQL is at its join limit and quits with an error. Is there a possibility to prevent this? I even limit the words for the input but this is not good.

On the more complex site I tried the fieldtype "cache". So far so good, but now there is the problem with pagefields that are searched too like "country.title". This is not working, as I read in the forum, and in the database there is only the ID of the pagefield in question. On a normal search by selector I would definitely use that, but here it is not the point, because the input is a word, not an ID. Example:

$pages->find("cached_fields_in_fieldtype_cache|country.title%=word1, cached_fields_in_fieldtype_cache|country.title%=word2");

I know the second example is not the best for this kind of search. It is easy to put an OR selector with the values together, but an AND selector (third example) is limited to the MySQL functionality.

I want to is a search ability like some might know from Joomla (no I am not a Joomla fan or developer, but the client is ). In Joomla there are the three possibilities to search from an input text:

• any word (same as selector operator "~=" ?)
• all words (same as selector operator "*=" or "%=" ?)
• exact wording (same as selector operator "*=" ?)

There is no limitation of words in the search input, so I am wondering how this is working.

So, what I got at the end are two options for the selector:

1:

$pages->find("cached_fields_in_fieldtype_cache|country.title*='wor d1 wo rd2 word3'"); // mostly no results at all if word is only part of the complete word. Word length limitation to at least 4 characters

The first option here is only working if the word is complete. If the search is "wor" and not "word1" then I get only result on one word input, but do I add another word to it, it gets no result.

2:

$pages->find("cached_fields_in_fieldtype_cache|country.title%=word1, cached_fields_in_fieldtype_cache|country.title%=word2, cached_fields_in_fieldtype_cache|country.title%=word3, ..."); // result limited by word count

 

Now I am almost on the model to remodel the search and limit it only to some parameters (as it should be...), but this would question the the clients "wish" for the search.

Another point is to extend the selector with more pagefields ("cached_fields_in_fieldtype_cache|country.title|method.title|town.title" etc.) that can not be put into the cache fieldtype. So the MySQL limit is coming back again... is it even possible to store the subfield of a pagefield in the cache field?

Does anyone know how it would be possible to get that kind of search running? Maybe a direct MySQL search?

• filter results by adding part or complete words (to find the one and only result)
• search with multiple words (no MySQL error because of join limitation -> I am already cutting off too many words)

Thanks for your help!

[Zeka]

Hi @godmok.

Some time ago I had almost the same questions as you and I came to this solution: 

$input->whitelist('q', $q);
    $qs = explode(" ", $q);
    foreach($qs as $key => $q){
      $qs[$key] = $sanitizer->selectorValue($q);
    }

    $selector = "template=product, product_cache%=". implode("|", $qs) ."";

    $matches = $pages->find($selector)

And it looks like it covers  

5 hours ago, godmok said:

• filter results by adding part or complete words (to find the one and only result)
• search with multiple words (no MySQL error because of join limitation -> I am already cutting off too many words)

 

[godmok]

Hi @Zeka,

thank you for your answer, and I am very sorry for my late feedback.

Your code is working fine if there are not any other fields next to the product_cache field. But if i want to add another one that has to find the same values from the query, then it fails. What I got right now is this:

$q = explode( " ", $q ); // create array
foreach ( $q as $key => $word ) {
    $q[ $key ] = $sanitizer->selectorValue( $word );
}
$q = implode( "|", $q );

My search input is "berlin massagen 10629 esalen" and the selector selector looks like this:

$matches = $users->find( "search_profile_cache|method.title*=berlin|massagen|10629|esalen" );

In this case I get a bunch of results. If I search for "berlin" I get less results than with more words. What I wanted to have is to filter out results with every added word.

Let's do it this way:

$matches = $users->find( "search_profile_cache*=berlin|massagen|10629|esalen, method.title*=berlin|massagen|10629|esalen" );

There are less results, and that is a good thing. But still not the result I need, because the word "esalen" is only in one profile availible. So if I search only by that word I get one result.

I don't know any more how to work that out. A single field is easy to handle but if there are more it gets ugly with multiple words.

How would it be possible to search like "berlin AND massagen AND 10629 AND esalen" in multiple fields to reduce to the results and not add?

[Robin S]

 

On 27/11/2016 at 0:01 AM, godmok said:

Example:

$pages->find("cached_fields_in_fieldtype_cache|country.title%=word1, cached_fields_in_fieldtype_cache|country.title%=word2");

 

@godmok, what is the maximum number of words you can put into this search query before you hit the MySQL limit? I would have thought that would be a lot of words; more than any person would type into a search input.

I have a site that explodes and loops over all search terms using the %= LIKE operator, searching within 12 fields. I can throw whole paragraphs of text into the search input without coming up against any MySQL limit.

[godmok]

Hi @Robin S,

at first there were eight fields and with nine words the limit was set. There were some problems with the cache field so I had to do it that way. Now it is working fine with it and was reduced to two fields for now, and I hit the limit at 88 words, but only because of the restricted limit of the input field with 255 characters. The Limit for the MySQL joints is 61. I will grab the %= LIKE operator again, as in the beginning.

I talked to my client and we will do the input field by cache field only. All page fields will be an added filter by a select or checkbox form field for now.

As I understand now, the only possibility to get the functionality is by adding all information into a cache field as text, or loop over all words and maybe hit the MySQL limit. Page fields won't work for cache fields. I make just like at the beginning and like @Robin S said: loop over every word and create a long selector. For now the impact is not a big deal. To be honest I think the performance is great! Just seen a little more than 20ms for creating the page at 88 words, still under 200ms in global if the site is generated. Just awesome!

It would be nice to get fields from a page field into the cache field optionally, like name, title and so on. Something like a ASM subselector?

Maybe anyone has an idea for a search solution, else I will let this topic open for now until I finish this or come up with an solution.

Thanks everyone!

[Robin S]

You might be interested in this recent thread about rendering page content to an 'index' field with a save hook. I only tested it briefly but with this approach you are in control of what is saved to the index - so for Page fields for example you can save the title or any other subfield you want.

 

[godmok]

That hook option would be awesome. It would be my first steps to it, but it looks promising. I will try that, but as it looks like I have to do it the long way, so it will be a form with multiple select and one input field for the search part of the site. When the site is live I will check out the hooks and implement it into the cache field.

I will let this topic open and mark it solved when I get it run. Maybe something will happen until then or somebody has another solution for this.

Thanks again!