Search in excerpt field too

Ok, now I understood: The Better Search plugin creates the SQL query completely by it selfs. After I found out this, I’ve found a solution to limit the results to the categories, I want to enclose in the search.

But I still don’t understand, why you do so much work. Just add the “score” to the fields of the normal search query should work too. Ok, you have to change the ORDER BY clausel too. But both of it, you could implement with two small filters, I think. Why did you implement so much code?

Right now, I understood how you realized the relevance implementation: MySQL do this nearly completely. ??

But right now, I don’t understand, why you didn’t add the excerpt field into the score calculation too? It would by very few work for you, I believe. But it would be results in a better order of the result: The author of the post mostly wrote the Excerpt of the post as a summary of it. So if the search pattern works for it too, this shows more relevance, instead of the match s inside the content.

So my suggestion to you: Add the post_excerpt field with a MATCH … AGAINST too. Maybe with a “5” multiplyer (between the post_title and post_content).

Addition, I would prefer, if you would use the standard implementation of querying the posts with the WP_Query class. In that case, it would be easier for webmasters like me to modify the query with the pre_get_posts filter. In that case, I just could define categories with a $query->set( 'cat', 1, 2, 3 ) instead of writing my own SQL code with the additional wp_term_relationships table.

… nevertheless I’m happy about the Better Search plugin! Thanks a lot for your work.

• This reply was modified 5 years, 5 months ago by tahtu.

Thank you for the feedback. Can you clarify by what you mean with the implementation of two small filters? Do you mean something like what I do with the Seamless mode enabled?

I can look into the excerpt – no one has asked for this so far and I wasn’t sure if people use that a lot

With the pre_get_posts filter of the WP_Query class, I can add filters for the SQL query, which the WP_Query will generate. An example from my site:

function my_pre_get_posts( $query ) {
if ( $query->is_search() )
$query->set( ‘cat’, 1 );
}
add_action( ‘pre_get_posts’, ‘my_pre_get_posts’ );

With this code inside my functions.php of my child theme, the default WP seach will only show post of the category with the term_id 1. So WP_Query generates the related WHERE clause for me and adds the needed wp_term_relationships table to the FROM clause.

Btw: This code works fine with Relevanssi too.

So this is an easy way for me to restrict the search to one category only. More information about the WP_Query class you can find here: WP_Query. The description of the category restriction you can find here on the same page. So you can find out, this is a very complex way to restrict all kinds of searches.

You could implement it by using the posts_fields and posts_search_orderby filters. Just all your MATCH ... AGAINST to the fields with the score alias and replace the whole ORDER BY clause with this score. That’s all and you could delete many of your code implementation. ??

About the Excerpt: I’m not sure, but it look like, Relevanssi does not implement the Excerpt too. I don’t understand this, since Imho Excerpt (content summaries) are much important for each post.

You could easily implent it inside you code by adding a this MATCH ... AGAINST part for the score calculation. If the authors of the post doesn’t fill them, it should not bother the Better Search plugin at all. But if the author filled them, the search result is more precise I think.

I spend more time about my search implementation – and I removed Relevanssi, since it’s much overloaded and takes too many resources.

Since I still think using the WP_Query class is the best solution, I’ve took a look to it again. Additional, I compared the SQL query of Better Search and the original WP_Query implementation.

While doing this, I recognized, you have to change something inside the WHERE clause too. You could implement that changes by using the posts_search filter. So I still think, you could replace a lot of your code by using the WP_Query class.

… and I decided to use Better Search, if you would do that. Other wise I would do this by my self, since I believe it’s the best solution not to implement too much unneeded code. So now, I will wait if you realize my suggestions before I implement my own solution.

I hope you don’t feel bothered with my many posts in this forum? This was my last one, if you don’t ask me something further more.

@tahtu

Thanks for the detailed testing and the feedback. I think I have an idea of what you are suggesting and I’ll look into that one.

Can you tell me if you’ve been running all your tests with the Seamless mode off? With Seamless mode, I use the filters – https://github.com/WebberZone/better-search/blob/master/includes/modules/seamless.php

One issue that I face is what I’ve called as Aggressive search. In this case the plugin will first search using Natural language mode, then using Boolean mode and then using LIKE. This only happens if there are no results found in the first case. I’m not sure how I could implement this using WP_Query as you’d only have a single query?

Im sorry, but I don’t really understand your question about the Seamless mode. Maybe my english is too bad to understand you. As far as I understood your question, you want to know how you can offer your filters further more, if you use the suggested WP_Query solution. Did I understood you well?

If you use the suggested WP_Query filters to implement Better Search … the webmaster (users of Better Search) can do the same. They can hook on the same filters with a higher or lower priority. So they can decide, to hook after Better Search. Than they can modify the SQL code, after you did it too.

Imho, this way would be the best. But in this case, all the webmaster, who still uses your filters has to modify his codes, since they have to hook on different filters up from now. Indeed you could also trow out you (old) filter inside the filters you hooked in. But in this case you would break the idea of the WordPress filters completely. I suggest not to do this.

Well, this problem (of changing the filter by the webmasters) occurs, since you didn’t use the standards of WordPress until now. This exactly is the reason, why you should allways!!! use existing standards. ??

About your Aggressiv search: I believe it’s the same problem, like you current implementation with your own SQL creation: You try to be better than other developer (people). This is a normal way from the most human people: They think, they would be better than other ones – but often this based on wrong thinking. Like I tried to tell you, your own SQL creation and offering filters inside of it is bad. Also from my point of view the whole Aggressiv Search is bad. Imho, you should remove it completely.

By using the MATCH ... AGAINST clause, you are using a feature of the MySQL server. You should not think, you would be able to do this better than the developer of MySQL. Yes, indeed you think you could everything better than other developer, but like mentioned, you can’t. So you should not try this, but you should trust the MySQL developers to do their job as well. And if the MATCH ... AGAINST search does not offer a result, please just accept this.

Relevanssi have also problems of this arrogant thinking: The believe to be able to implement a better index than the MySQL database can do this. But the wildcard search (for example search about “Relev*” (with a “*”) does not results posts with “Relevanssi”. But the most Internet users know this wildcard search patter from Google. So, it should be work inside the search on WordPress too.

Using MATCH ... AGAINST offers this wildcard solution – and more features. Because of this, I think this implementation is the best one which can be used together with WordPress. But the disvantage of it is: The large fulltext index inside the database. On large sites with very many posts inside of it, can get trouble with this. Maybe that’s the reason, why WordPress didn’t implement that solution.

Indeed developers like you tries to implement a lot of features. But you risk to overload your software with that. And than webmaster are not satisfied with it and don’t use it.

Because of this, I looking for a small solution with FULLTEXT index and MATCH ... AGAINST, ordered by relavance. But nothing more. This forces to create the indices and modifing the three mentioned WP_Query filters. That’s all. That improves the WordPress search very much. But still offers the great implementations of standard pre_get_posts manipulation for the webmasters, which is documented as well and works without Better Search as well too. Or with other words: If a webmaster still uses the pre_get_posts hook, it works as well further more after installing Better Search. So Better Search is much more compatible to WordPress with that solution … and the webmasters are happy about it. This should be your destination: Making the webmaster happy – not making yourself happy. (That is selfish, not loveful ?? )

Btw: I found an other problem of your SQL creation: It always search for publish posts. Also, for logged in admins. But they want to find draft posts too. Indeed you could implement a solution for this too. But why should you do this, if WordPress offers this feature already? This is wasting time because of selfish thinking…

Please sorry about my “selfish” blaming. I respect you and your work. I didn’t know the MATCH ... AGAINST feature of the MySQL database before. Addition, I learned a lot of executing SQL code inside WordPress too. So I’m really happy about your work. You teached me a lot of great knowledge, which helped me to expand my knowledge.

• This reply was modified 5 years, 5 months ago by tahtu.

Thanks for explanation. On the record, I do not think any of the implementation there is about arrogance – I definitely do not think I’m better than other developers, of WP, mySQL or anyone else! So I personally think that comment is unfair.
Although I don’t know them, I don’t think the developers of Relevanssi would consider their implementation arrogant. They have an excellent plugin for most users.

Better Search is built for users in mind not developers. Users want results to work out of the box and not worry about writing code to get that. Better Search does that – the aggressive mode also does that – sometimes you don’t get results which is why you need the LIKE search fall-back.

Most developers should not need this plugin as you rightly point out, you can just write your own filters and create the fulltext index without any plugin. And then put the code into a single mu-plugin which then works out of the box but isn’t flexible.

However, I do want to improve what can be done to make the plugin better compatible and make it also easier for developers so I will look to see how I can improve that aspect and try WP_Query for search results page.

Detecting ourself and finding out our own limitations are the greates problems for us humans. If you don’t follow what I told to you … maybe it’s a problem from you. But you don’t think about that. ??

I decided not to use Better Search, I implemented my own solution. If you are interested about it, please take a look to it. Otherwise please excuse me to post it.

Thanks a lot for teaching me, how it works.

  add_filter( 'posts_fields', 'nw_posts_fields', 10, 2 );
  function nw_posts_fields( $fields, $query ) {
    global $wpdb;
    
    if ( is_search() )
      $fields .= ', MATCH (' . $wpdb->posts . '.post_title) AGAINST (\'' . $wpdb->_escape($query->query['s']) . '\') * 10'
        . ' + MATCH (' . $wpdb->posts . '.post_excerpt) AGAINST (\'' . $wpdb->_escape($query->query['s']) . '\') * 5'
        . ' + MATCH (' . $wpdb->posts . '.post_content) AGAINST (\'' . $wpdb->_escape($query->query['s']) . '\') * 1 AS score';
  
    return $fields;
  }
  add_filter( 'posts_search', 'nw_posts_search', 10, 2 );
  function nw_posts_search( $fields, $query ) {
    global $wpdb;
    
    if ( is_search() )
      return ' AND (MATCH (' . $wpdb->posts . '.post_title) AGAINST (\'' . $wpdb->_escape($query->query['s']) . '\')'
        . ' OR MATCH (' . $wpdb->posts . '.post_excerpt) AGAINST (\'' . $wpdb->_escape($query->query['s']) . '\')'
        . ' OR MATCH (' . $wpdb->posts . '.post_content) AGAINST (\'' . $wpdb->_escape($query->query['s']) . '\'))';
    else
      return $fields;
  }
  add_filter( 'posts_search_orderby', 'nw_posts_search_orderby', 10, 2 );
  function nw_posts_search_orderby( $orderby, $query ) {
    global $wpdb;
    
    if ( is_search() )
      if ( $orderby )
        $orderby = 'score DESC, ' . $orderby;
      else
        $orderby = 'score DESC';
  
    return $orderby;
  }

This gives me this query:

SELECT SQL_CALC_FOUND_ROWS wp_posts.*, MATCH (wp_posts.post_title) AGAINST ('gott') * 10 + MATCH (wp_posts.post_excerpt) AGAINST ('gott') * 5 + MATCH (wp_posts.post_content) AGAINST ('gott') * 1 AS score
FROM wp_posts
LEFT JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE 1=1
AND ( wp_term_relationships.term_taxonomy_id IN (5,6,9) )
AND (MATCH (wp_posts.post_title) AGAINST ('gott')
OR MATCH (wp_posts.post_excerpt) AGAINST ('gott')
OR MATCH (wp_posts.post_content) AGAINST ('gott'))
AND wp_posts.post_type IN ('post', 'page', 'attachment')
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_author = 1
AND wp_posts.post_status = 'private')
GROUP BY wp_posts.ID
ORDER BY score DESC, wp_posts.post_title LIKE '%gott%' DESC, wp_posts.post_date DESC
LIMIT 0, 10

The category restriction is realized with this:

  add_action( 'pre_get_posts', 'nw_pre_get_posts' );
  function nw_pre_get_posts( $query ) {
    if ( $query->is_search() )
      $query->set( 'cat', 5, 6, 9 );
  }

… maybe too simple, to use a pugin for this… ??

• This reply was modified 5 years, 5 months ago by tahtu.
• This reply was modified 5 years, 5 months ago by tahtu.

Instead of using your aggressiv mode, maybe the Query Expansion could be a solution for you: https://dev.mysql.com/doc/refman/8.0/en/fulltext-query-expansion.html. (But I didn’t read this well, so maybe I’m wrong.)

Thanks for the code. Glad to know you were able to implement it directly.

I use posts_where instead of posts_search, but will explore the latter.

https://github.com/WebberZone/better-search/blob/master/includes/modules/seamless.php

Both of us learned something from the other and both of us can use it for his goals. So we both should be happy to meat another. But for me the problem is solved and I don’t think you want further assistance from me. So imho up from now we do not longer need to work together.

Thanks a lot and best wishes!

To search within custom fields (including excerpts and meta values) you can use WP Fulltext Search plugin (it’s not an advertisement, it’s just a way to solve this issue).

It breaks the search process to two stages: the first stage is indexing, where we put all the textual data to the special word-based index. Second stage is the search itself by requested word or word combinations.

It does not matter which MySQL engine you are using (MYISAM or InnoDB), because it does not use fulltext search (nor match/against technique).

You can find such principle in Relevanssi, but WPFTS in opposite extends standard WP search, so all 3rd party plugins and WP admin will start using WPFTS indexed search automatically.

Oh and if you have questions about WPFTS please feel free to check documentation (quite short, but clear I hope) or ask me directly!

@epsiloncool: I took a look into the code of WPFTS. I wasn’t be able to find a MATCH ... AGAINST solution. But I think that solution is the best one, since MySQL has much more experience, knowledge and tester like you. Because of them, I trust that solution more, than your implementation. I’m sorry.

But right now, I understood the BOOLEAN MODE of the MATCH ... AGAINST solution: It does not offer a relevance search. So I switched to the NATURAL MODE.

There are a lot of different solutions:

• WordPress Core search with simple LIKE '%pattern%'
• Better Search with several combinded searches
• Relevanssi and WPFTS with own Indexes
• A Google plugin with their search implementation
• A simple MATCH ... AGAINST solution

Seems, there is no right solution…

So I for myself decided to use the WordPress function (WP_Query class) with a MATCH ... AGAINST extension in NATURAL MODE. For my private usage (as admin) I implemented a further checkbox for the BOOLEAN MODE. May everbody choose an other solution… ??

Nevertheless thank you for showing me the WPFTS. Unfortunatly, I didn’t found it by myself. But additional, I do not install software with so less user votings, since I don’t want to risk some demages…