[Brittany V]

Hi Steven –

Thank you for the support for the plugin thus far.

I have encountered a bug today with the way short term search queries are built. For instance, this bug is encountered when you search “125” looking for the organization entry entitled “INFORMATION SERVICES DIVISION (ISD) – Unit 125”. When searching 125 looking for that entry, no results get returned.

After debugging your code a bit, I noticed this was the query that was generated:

SELECT wp_connections.id FROM wp_connections WHERE 
( 
    family_name LIKE '125%' 
    OR first_name LIKE '125%' 
    OR middle_name LIKE '125%'
    OR last_name LIKE '125%'
    OR title LIKE '125%' 
    OR organization LIKE '125%' 
    OR department LIKE '125%' 
    OR contact_first_name LIKE '125%'
    OR contact_last_name LIKE '125%' 
)

Around line 2413 in class.retrieve.php of V8.5.23 of the connections plugin, you generate those LIKE/OR statements you see in the WHERE clause of the above query.

I believe you need to add %s in front of all of the $atts['fields']['entry']s (in this case all of '125%'s) in line 2413 (you’d modify the $like[] var on that line). The query, after this update to your plugin would be:

SELECT wp_connections.id FROM wp_connections WHERE 
( 
    family_name LIKE '%125%' 
    OR first_name LIKE '%125%' 
    OR middle_name LIKE '%125%'
    OR last_name LIKE '%125%'
    OR title LIKE '%125%' 
    OR organization LIKE '%125%' 
    OR department LIKE '%125%' 
    OR contact_first_name LIKE '%125%'
    OR contact_last_name LIKE '%125%' 
)

This is a fatal flaw because anytime a user searches for a word at the very end of an entry’s name, they would retrieve no results.

I verified that adding %s to the front of the short search term (term after the LIKE clause) would, indeed, generate results & that without these %s in the front of the search terms, no results are returned. (Verified using phpMyAdmin on my site)

Please e-mail me if you have questions. Would be happy to demonstrate how I found the bug over a Google hangout or similar.

Brittany

[Steven Zahm]

@ Brittany

This really is not a bug. When FULLTEXT queries are performed they are setup to do a “term starts with” query. FULLTEXT actually does not support “term contains” type of queries. So, when the LIKE query is created, I chose to use the “term start with” syntax in order to match the search performed by FULLTEXT queries.

If you want a LIKE query to perform a “term contains” searches, I can look into adding a filter that you can hook into to change the query. I do not think I will change the default behaviour. Let me know if you would like a filter and I’ll take a look on how it might be best to implement it.

[Brittany V]

Steven –

Ended up just modifying the short term search code to include %s before search terms. I think I hook into the SQL query generated would be helpful so that I won’t have to pay close attention to code changed when a plugin is updated (I manually update plugins on the backend of my site instead of using wordpress’s automatic update tool). Not a super high priority if no one else is complaining about it :)

[Steven Zahm]

@ Brittany

I added a new filter, cn_search_like_shortword which you can hook into which should allow you to do what you want. Here’s the link to the changes I made. I also included an example filter in the inline code documentation. So, if you make these same changes and add the new filter, you should get the results you want without having to worry about making changes when applying updates.

https://github.com/Connections-Business-Directory/Connections/commit/8e33655ca8dd80863bb4c7730f4b5092a3ae1599

Hope that helps!

[Author]

Posts