in reply to [OT] Weighted Search Results

I think something like this will work. Warning - really untested. 

my $sql = <<ESQL;
SELECT *,
       (name = 'Greg') + (age = 32) + (state = 'TX') AS score     
    FROM datable
    WHERE score > 0
    ORDER BY score DESC
ESQL

[download]
(how's that for a perl fig leaf? ;-)

Update: What was I thinking of? This looks much better, 

my $sql = <<ESQL;
SELECT *
    FROM datable
    WHERE  ((name = ?) + (age = ?) + (state = ?)) > 0
ESQL

[download]
You lose the score information and ordering, but that can be recovered in perl. You gain placeholders! as well as WHERE clause optimizations mentioned in replies.

After Compline,
Zaxo

Replies are listed 'Best First'.
Re: Re: [OT] Weighted Search Results
by eric256 (Parson) on May 20, 2004 at 16:12 UTC

    Brilliant idea. I poped into MySQL Control Center and discovered two things. You need parenthesis around the whole forumula and then you also need to use having instead of where. Where wont work with column aliases and having will.

    SELECT *,  ((State = 'CA') + (City = "Chico")) AS score  FROM `Address
+`
       HAVING score > 0
       ORDER BY score DESC

    [download]

    ___________
    Eric Hodges
[reply]
[d/l]
      This worked like an absolute champ! What a wonderful day this has turned out to be! As was noted by a colleague of mine, this of course coudl slow down dramatically for large datasets since the having is an unindexed column and woudl require some shuffling by MySQL in the background.


      CiceroLove
      Fates! We will know your pleasures: That we shall die, we know; 'Tis but the time, and drawing days out, that men stand upon. - Act III,I, Julius Caesar
[reply]
        For larger cases you could break it into to parts. First get all records that have at least one matching feild. Then go threw those and score them. You could use a temp table or nested select statments.

        ___________
        Eric Hodges
[reply]

In Section Seekers of Perl Wisdom