ORM Sort search results by match count?

Questions
1

Silverstripe Version: 4.4

I’m doing a basic search with:

...
$DataObject->filterAny([
    '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:

SELECT *, 
  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.

2

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.

3

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('
    SELECT 
        *
         , MATCH(Title) AGAINST(?) * 10 as score1
         , MATCH(Content) AGAINST(?) * 5 AS score2
    FROM 
        Faq
    WHERE 
        MATCH (Title, Content) AGAINST(?)
    ORDER BY 
        (score1) + (score2) DESC', 
    [$data['FaqSearch'], $data['FaqSearch'], $data['FaqSearch']]
);

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

    while ($record = $result->next())
    {
        $Faqs->push(Faq::create($record));
    }
}

$PaginatedFaqs = PaginatedList::create(
    $Faqs,
    $this->getRequest()
)->setPageLength(5);
1 Like