Order like search by full matches then by partial matches.

Discussion in 'MySQL' started by stephan2307, Jan 19, 2016.

0
  1. #1
    Hi,

    I am working on a pretty complicated search on a custom WordPress site.

    We have a custom search with various filters.

    If we search for something like "chester" we get all the results for Chester but they also include Manchester, Chichester. We want to get them ordered so that the full matches appearing first and then the partial ones.

    Our current SQL
    

SELECT DISTINCT wp_posts.ID , wp_posts.post_title , wp_posts.post_name , wp_posts.post_content , meta1.meta_value as keywords 
FROM wp_posts 
INNER JOIN wp_postmeta as meta1 ON meta1.post_id = wp_posts.ID AND meta1.meta_key = 'location_keywords' 
INNER JOIN wp_postmeta as meta5 ON meta5.post_id = wp_posts.ID AND meta5.meta_key = 'parent_id' 
INNER JOIN wp_posts as wp2 ON wp2.ID=meta5.meta_value AND wp2.post_type='ciet_company' AND wp2.post_status='publish' 
WHERE wp_posts.post_status='publish' AND 
meta1.meta_value LIKE '%chester%' 
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_title 
ASC LIMIT 20
    Code (markup):
    Any help, hints or suggestions would be greatly appreciated.
     
    stephan2307, Jan 19, 2016 IP
  2. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    You can do a bit of a hack and get it to work without a huge amount of work. In the SELECT clause, you basically want to perform the same lookup as 'chester%' and assign those a higher priority.

    Also the DISTINCT wp_posts.ID is redundant since you are grouping by the same column.

    Try this:

    
SELECT
    wp_posts.ID,
    wp_posts.post_title,
    wp_posts.post_name,
    wp_posts.post_content,
    meta1.meta_value AS keywords,

IF (
    wp_posts.post_title LIKE 'chester%',
    1,
    0
) AS sort_priority
FROM
    wp_posts
INNER JOIN wp_postmeta AS meta1 ON meta1.post_id = wp_posts.ID
AND meta1.meta_key = 'location_keywords'
INNER JOIN wp_postmeta AS meta5 ON meta5.post_id = wp_posts.ID
AND meta5.meta_key = 'parent_id'
INNER JOIN wp_posts AS wp2 ON wp2.ID = meta5.meta_value
AND wp2.post_type = 'ciet_company'
AND wp2.post_status = 'publish'
WHERE
    wp_posts.post_status = 'publish'
AND meta1.meta_value LIKE '%chester%'
GROUP BY
    wp_posts.ID
ORDER BY
    sort_priority DESC,
    wp_posts.post_title ASC
LIMIT 20
    Code (SQL):
    There are probably other ways to do this. This method should have negligible overheard compared to some of the other ways I can think of to do the same thing like unions, stored procedures, or doing it with 2 queries in the application.
     
    jestep, Jan 21, 2016 IP
  3. sarahk

    sarahk iTamer Staff

    Messages:
    28,832
    Likes Received:
    4,541
    Best Answers:
    123
    Trophy Points:
    665
    #3
    It looks pretty damn complex but here's what I do on a cakePHP site I run

    scenario
    =====
    Someone has registered for an event and we want to know if they are already in the database.
    Matching on a company name is good, surname is useful, matching on firstname is not, matching on email is best, matching on mobile is best, matching on work ph isn't as reliable

    So we work through the possible data the user might have given and create an array called $relevance

    $relevance[] = "CASE WHEN `Membership`.`name` = '{$this->request->data['Registration']['tradingname']}' THEN 4 ELSE 0 END";
    Code (markup):
    we then have a a big array giving points. Membership name might get used more than one to give different numbers of points depending on an exact match or a partial match

    then into my list of fields that I want for the query I add in relevance and I use that to order the results
    $fields[] = '(' . implode(' + ', $relevance) . ') as `relevance`';
$order = array('relevance desc');
    Code (markup):
    To use that concept in your query you'd need to do something like this:

    $str = 'chester';
$relevance = array();
$relevance[] = "CASE WHEN `wp_posts`.`post_title` = '{$str}' THEN 4 ELSE 0 END";
$relevance[] = "CASE WHEN `wp_posts`.`post_title` like '%{$str}%' THEN 2 ELSE 0 END";
$relevance_string = '('.implode(' + ', $relevance) .') as `relevance`';

$sql = "SELECT DISTINCT wp_posts.ID , wp_posts.post_title , wp_posts.post_name , wp_posts.post_content , meta1.meta_value as keywords, {$relevance_string}
FROM wp_posts
INNER JOIN wp_postmeta as meta1 ON meta1.post_id = wp_posts.ID AND meta1.meta_key = 'location_keywords'
INNER JOIN wp_postmeta as meta5 ON meta5.post_id = wp_posts.ID AND meta5.meta_key = 'parent_id'
INNER JOIN wp_posts as wp2 ON wp2.ID=meta5.meta_value AND wp2.post_type='ciet_company' AND wp2.post_status='publish'
WHERE wp_posts.post_status='publish' AND
meta1.meta_value LIKE '%chester%'
GROUP BY wp_posts.ID
ORDER BY `relevance` DESC
ASC LIMIT 20";
    Code (markup):
     
    sarahk, Jan 21, 2016 IP