Ordering search results by best match

My app has the following:

Genus hasMany Species
Species hasMany CommonName

and the corresponding belongsTo relationships as well. Each of these
models has a name field, and Genus and Species have several other
fields.

Say for example I have a Species Acer rubrum. This Species belongsTo
Genus Acer and has a CommonName Red Maple.

I want my users to be able to search the database for "Red Maple" and
get as a result Species Acer rubrum (with the CommonName Red Maple
highlighted to show that's what it matched. I can do this already.
Yay.

However if I have another Species with CommonName Bored Maple, I want
to display this result too, but lower in the list than Red Maple.
Since Bored comes before Red alphabetically I can't just order by
CommonName.name to get the result I want.

So what I know I could do is a series of finds:
1) Find exact match: CommonName.name => $searchStr
2) Find starting match: CommonName.name LIKE => $searchStr . '%'
3) Find internal match: CommonName.name LIKE => '%' . $searchStr . '%'

And then merge them into my results array.

However, this seems very inefficient. Is there a way I can use cake's
find() function to do this all in one go while ordering them in order
of best match? I have thought about this for quite some time and done
some research but I can't seem to find any clever solution.

I might be able to do a custom query with a bunch of SELECTs as above
combined with UNION or something, but this would be a pretty complex
query and not really much more efficient than what I already know how
to do.

Thanks in advance any genius here who has a solution!

-Clay

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to cake-php+unsubscribe@googlegroups.com
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---