[RESOLVED] Text searching and relevency sorting problem

Operandi

I am trying to perform a search on a text field and then order according to relevency.

For example I want records containing this text

%canon eos 5%

to be ranked higher than records containing

%canon% and %eos% and %5%

I cannot use FULLTEXT indexing because some of the search terms are too short, as you can see from the example.

This query extracts the correct number of records and the correct data but won't sort the records according to the relevency criteria stated above

//content is the text field

$query = "SELECT DISTINCT 
					suppliers.supplier_id, 
					suppliers.name, 
					suppliers.town, 
					suppliers.postcode,
					suppliers.phone
			FROM suppliers
			LEFT JOIN links
			ON links.supplier_id=suppliers.supplier_id
			WHERE
			links.content LIKE '%canon eos 5%'
			OR 	( links.content LIKE '%canon%'
			AND links.content LIKE '%eos%'
			AND links.content LIKE '%5%')";

How can I modify this query to display all of the %canon eos 5% matching records at the top of list?

As the results need to be paginated I have thought that traversing the recordset array assessing each element for relevency, re-ordering the array and then starting each page from a different pointer position would be too resource intensive.

Any thoughts would be very much appreciated

Thanks

Chris

Roger_Ramjet

You can change the minimum word length so you can use FTS.

rulian

Try

$query = "SELECT DISTINCT suppliers.supplier_id,
                    suppliers.name,
                    suppliers.town,
                    suppliers.postcode,
                    suppliers.phone
            FROM suppliers
            LEFT JOIN links
            ON links.supplier_id=suppliers.supplier_id
            WHERE
            links.content LIKE '%canon eos 5%'
            OR     ( links.content LIKE '%canon%'
            AND links.content LIKE '%eos%'
            AND links.content LIKE '%5%')
           ORDER BY char_length (concat(links.content)) DESC";

that should order them by string length descending so canon eos 5 would come before canon

Operandi

Hi Rulian

Thanks for the suggestion, however it doesn't work because it is sorting against the length of the text in the content field and not the length of the search term.

You can change the minimum word length so you can use FTS.

Yes this might work if we bring the length down to three characters, but would chop out the one and two character length terms. It wouldn't be feasible to run FTS on a one character length given the size of the database.

I'm still struggling with it, but thanks for the help though 🙂

Roger_Ramjet

Nope that will not work. All you are going to do is order by the length of the links.content field itself, not by the match.

If you insist on not using FTS then use a union query

$query = "SELECT DISTINCT
                    suppliers.supplier_id,
                    suppliers.name,
                    suppliers.town,
                    suppliers.postcode,
                    suppliers.phone
            FROM suppliers
            LEFT JOIN links
            ON links.supplier_id=suppliers.supplier_id
            WHERE
            links.content LIKE '%canon eos 5%' 
UNION
          SELECT DISTINCT
                    suppliers.supplier_id,
                    suppliers.name,
                    suppliers.town,
                    suppliers.postcode,
                    suppliers.phone
            FROM suppliers
            LEFT JOIN links
            ON links.supplier_id=suppliers.supplier_id
            WHERE
            links.content LIKE '%canon%'
            AND links.content LIKE '%eos%'
            AND links.content LIKE '%5%'";

The first query results will come before the second.

Roger_Ramjet

Operandi wrote:
Hi Rulian

Yes this might work if we bring the length down to three characters, but would chop out the one and two character length terms. It wouldn't be feasible to run FTS on a one character length given the size of the database.

I'm still struggling with it, but thanks for the help though 🙂

Just add the usual short words that you don't want (like is, and, or, a, etc) to the STOP LIST so that they are not indexed.

Operandi

Hi Roger

Many thanks for the union query suggestion, which works perfectly.

Following on from your points about FTS, I'll look more closely at that solution as it would be more scaleable as the database size grows.

Thanks, once again.