ORM Sort search results by match count?

Silverstripe Version: 4.4

I’m doing a basic search with:

    'Title:PartialMatch' => $terms, 
    'Content:PartialMatch' => $terms

I have also tried the FullText search.

DataObject::get()->filter('SearchFields:Fulltext', $terms);

I’d like to order the results by most matches.

Something similar to:

  MATCH('title') AGAINST($_GET['query']) * 10 as score1, 
  MATCH('content') AGAINST($_GET['query']) * 5 AS score2
FROM articles
WHERE MATCH (title, content) AGAINST($_GET['query'])
ORDER BY (score1) + (score2) DESC;

(stolen from SO)

Is this possible using the ORM? Or even the lower sql features of SS?

Not interested in Solr etc at this point.

When I’ve needed to do this kind of thing, I’ve always resorted to using SQLSelect(). I don’t think there’s any way to do it with the ORM easily.

Thanks for that info Tim.

In the end I couldn’t be bothered fighting with SQLSelect either so I used DB::prepared_query.

If it helps future readers:

$result = DB::prepared_query('
         , MATCH(Title) AGAINST(?) * 10 as score1
         , MATCH(Content) AGAINST(?) * 5 AS score2
        MATCH (Title, Content) AGAINST(?)
        (score1) + (score2) DESC', 
    [$data['FaqSearch'], $data['FaqSearch'], $data['FaqSearch']]

if ($result)
    $Faqs = ArrayList::create();

    while ($record = $result->next())

$PaginatedFaqs = PaginatedList::create(
1 Like