[SQL] Relevante gedeelte tekst tonen in zoekresultaten

[X-Lars]

Hallo,

Hoe kun je het relevantie deel van de tekst tonen in de resultaten waar op gezocht is? Google gebruikt dat ook. Dus bijvoorbeeld een zoekterm "pharetrea" in dit stuk tekst "Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Duis nulla augue, imperdiet id, pretium vel, pharetra eu, turpis. Nunc scelerisque orci nec odio." zou opleveren

code:

"..pretium vel, pharetra eu, turpis..."

Ik gebruik MySQL's Fulltext en PHP. Hier een voorbeeldquery:

SQL:

SELECT
    id,
    titel,
    intro,
    body,
    MATCH (titel, intro, body) AGAINST ('$string') AS score
FROM pagina
WHERE MATCH (titel, intro, body) AGAINST ('$string' IN BOOLEAN MODE)
ORDER BY score DESC

Het gaat in mijn geval logischerwijs om de kolommen intro en body.

Wie weet hier meer van, kan mij op weg helpen of doorverwijzen?

Edit: denk toch dat dit meer een PHP oplossing zal gaan opleveren. De resultaten doorzoeken op het trefwoord en vervolgens x woorden/chars ervoor en erna nemen met een regexp. Dacht/hoopte dat dit al in de query op te lossen zou zijn.

[ Voor 21% gewijzigd door X-Lars op 10-11-2005 10:55 ]

[Mac_Cain13]

Als ik het goed heb is het in MySQL ook mogelijk om in de query een RegExp op te nemen. Dus als je het in PHP met een RegExp op kan lossen moet je dat ook lukken in MySQL. Waarschijnlijk moet je met even in de manual graven wel kunnen vinden hoe je een RegExp moet gebruiken in je SQL.

Edit:
In de voorbeelden die ik in de manual kan vinden worden RexExp-en voornamelijk gebruikt om te matchen. Dus ik ben er niet helemaal zeker van of je er ook stukken tekst mee kan selecteren, maar het is het proberen waard natuurlijk.

[ Voor 35% gewijzigd door Mac_Cain13 op 10-11-2005 11:23 ]

[wasigh]

Ik neem aan dat Google dat gewoon bij het indexeren opslaat. Bij elk woord in een document de regel in het document waarin het voor komt.
Ik weet niet hoe mySQL die index opslaat. Echter zijn er volgens mij genoeg open source indexers waarbij je dat soort vaken vast in kunt stellen..

[X-Lars]

Bedankt, voor de antwoorden. Ik heb nu maar gewoon dit gedaan:

PHP:

    function getRelevantFragment($intro, $body, $needle) {
        $pos = strpos($intro, $needle);
        if ($pos === false) {
            $pos = strpos($body, $needle);
            if($pos <= 20) {
                $string = substr($body, 0, 40);
            } else {
                $string = substr($body, ($pos - 20), 40);
            }
            return $string;
        } else {
            if($pos <= 20) {
                $string = substr($intro, 0, 40);
            } else {
                $string = substr($intro, ($pos - 20), 40);
            }
            return $string;
        }
    }

De idee is duidelijk, ik moet het nog even verfijnen dat alleen een x aantal woorden wordt meegenomen, etc.

[frickY]

Je kunt met de string-functies van MySQL aan de gang gaan.
Met SUBSTRING() kun je een gedeelte van een string ophalen, en met STRPOS() de positie van een char/string bepalen. 'Met wat goochelen moet het haalbaar zijn om 3 woorden voor en 3 woorden na het gematchde woord te selecteren.. ik vraag me alleen af wat er dan van de performance overblijft.
Ik denk dat je PHP functie een nettere oplossing is.

[ Voor 9% gewijzigd door frickY op 10-11-2005 11:32 ]

[X-Lars]

Als payback voor mijn op het oog bijna nutteloze TS, hier de functie die ik uiteindelijk heb. Het is dus een functie geworden die 50 karakters rondom het keyword pakt (1 fragment per gebruikt keyword) en vervolgens de woorden uit het fragment in een array zet. De buitenste elementen worden (meestal) verwijderd, zodat halve woorden wegvallen. Vervolgens worden deze arrays met elkaar vergeleken, zodat fragmenten die teveel op elkaar lijken (wanneer keywords dicht bij elkaar staan) worden uitgefilterd. Uiteindelijk wordt de array weer samengevoegd en als fragment terug gegeven.

Het werkt heel aardig. Maar een nadeel is dat nu per keyword 1 fragment wordt terug gegeven (of minder). Bij bv. Google gebeurt dat op een mooiere manier. Verder was ik gewoon benieuwd of mensen hier wat aan hebben en of er betere algoritmes voor te bedenken zijn.

Noot: $string (= zoekstring) kan hier alleen uit de tekens [a-z0-9] bestaan, andere tekens zijn er voor het maken van deze fragmenten uitgehaald.

Eventueel kan ik screenshots plaatsen van zoekresultaten ter voorbeeld ende vermaeck.

PHP:

/**
 * Splits search string and finds one relevant fragment in
 * $intro or $body (in that order) per keyword.
 *
 * @param string $intro
 * @param string $body
 * @return string $string
 */  
   
function getRelevantFragments($intro, $body, $string) {

    $keywords = preg_split("/[^a-z0-9]+/", $string);
    
    // Returns relevant fragment from $intro or $body
    if(!function_exists('getFragment')) {
        function getFragment($intro, $body, $keyword) {
            $pos = strpos(strtolower($intro), $keyword);
            if ($pos === false) {
                $pos = strpos(strtolower($body), $keyword);
                if ($pos === false) {
                    return false;
                } else {
                    $haystack = $body;
                }
            } else {
                $haystack = $intro;
            }
            if(is_int($pos)) {
                return $pos <= 30 ? substr($haystack, 0, 50) : substr($haystack, ($pos - 25), 49);
            }
        }
    }
    
    // Get fragment for each keyword with the function above.
    $fragments = array();
    foreach($keywords as $keyword) {
        $fragment = getFragment($intro, $body, $keyword);
        if($fragment) $fragments[] = $fragment;
    }
    
    // Clear fragments of unwanted characters (unfinished words) at beginning and end.
    foreach($fragments as $key => $value) {
        
        // Split fragment by space (' ') and put in array.
        $aTokens[$key] = preg_split("/[\s]+/", $value); 
        
        // Split fragment by non-$keyword character and put in array (remove all characters not in [A-Za-z0-9]).
        $aCleanTokens[$key] = preg_split("/[^A-Za-z0-9]+/", $value); 

        // If first word in $aTokens is in $keywords, don't remove it;
        // which means it is the keyword, or at least a word (not unfinished).
        // Contains extra check if $fragment starts with non-$keyword characters
        // (which would be in $aCleanTokens[$key][0]).
        if(!in_array(strtolower($aTokens[$key][0]), $keywords) && !in_array(strtolower($aCleanTokens[$key][1]), $keywords)) {
            array_shift($aTokens[$key]);
        }
        
        // Remove last (unfinished) word
        array_pop($aTokens[$key]);
        
        // Add '...' to first word if fragment is not beginning of $intro or $body
        if(strlen($fragments[$key])==49) {
            $aTokens[$key][0] = '...'.$aTokens[$key][0];
        }
        
        // Rebuild $fragment[$key]
        $fragments[$key] = implode(' ', $aTokens[$key]);
    }
    
    // Loop $fragments to remove elements that are (almost) identical;
    // this is checked by the number of elements that intersect in
    // $aCleanTokens for the current fragment and the one it is compared
    // with. The limit is 4 now, which works well if the substr-length
    // is 50 (a few lines up here). Keep these numbers in touch with
    // eachother. Just try and find out.        
    $c = count($fragments);
    if($c > 1) {
        foreach($fragments as $key => $value) {
            if(count(array_intersect($aCleanTokens[$c-1-$key], $aCleanTokens[$key]))>=4 && $key != 0) {
                unset($fragments[$key]);
            }
        }
    }
    
    return $fragments;
}

[X-Lars]

Verder was ik gewoon benieuwd [...] of er betere algoritmes voor te bedenken zijn.

[Verwijderd]

Je zou bijvoorbeeld kunnen implementeren dat hij kijkt of er (binnen een redelijk aantal karakters, beide kanten op) een : ; , . " of ' (bijvoorbeeld) staat, zodat hij er hele zinnen uit vist.. meestal heb je daar veel meer aan dan een bepaald aantal karakters om je zoekterm heen.

[ Voor 3% gewijzigd door Verwijderd op 14-11-2005 19:08 ]