Filament•16mo ago

Table Builder + Scout: Search Result Order

Currently, if I use https://filamentphp.com/docs/3.x/tables/advanced#searching-records-with-laravel-scout to integrate table builder with scout, the search results are ordered by the id number of the record, meaning that the most relevant search result is often not at the top because it has a larger id value. I want the search results in the table builder to be ordered by order that scout returns. What would be the best way to approach this? For reference: this is the query that table builder tries to execute. When the search bar is filled, I don't want the results to be ordered by id, but by the order that scout returns.

SELECT
  *
FROM
  [ table ]
WHERE
  [ id ] IN (
    ...
  )
ORDER BY
  [ table ].[ id ] ASC OFFSET 10 ROWS FETCH next 10 ROWS ONLY

SELECT
  *
FROM
  [ table ]
WHERE
  [ id ] IN (
    ...
  )
ORDER BY
  [ table ].[ id ] ASC OFFSET 10 ROWS FETCH next 10 ROWS ONLY

7 Replies

Patrick Boivin•16mo ago

Can you show your version of applySearchToTableQuery() ?

VinnyOP•16mo ago

It has evolved quite a bit since then. But I will share what I discovered. At that point I was using the exact version of applySearchToTableQuery() from the filament docs. (I am pasting the one from the docs here because the one I have now is vastly different because I have worked around the issue already. see below)

protected function applySearchToTableQuery(Builder $query): Builder
{
    $this->applyColumnSearchesToTableQuery($query);
    
    if (filled($search = $this->getTableSearch())) {
        $query->whereIn('id', Post::search($search)->keys());
    }
 
    return $query;
}

protected function applySearchToTableQuery(Builder $query): Builder
{
    $this->applyColumnSearchesToTableQuery($query);
    
    if (filled($search = $this->getTableSearch())) {
        $query->whereIn('id', Post::search($search)->keys());
    }
 
    return $query;
}

Essentially if I do Post::search($q)->get() I get all the results in order because (from what I gathered, I could be wrong) scout handles the reordering internally. To compare, I did Post::query()->whereIn('id', Post::search('whatever')->keys()) and the order was messed up. So, while scout does use whereIn internally like the docs say, it handles reordering based on search relevancy internally also. To work around the messed up order, I appended a manual "ORDER BY" at the end of my query in applySearchToTableQuery() to order it based on the search keys that scout returns.

$orderByCases = [];
foreach ($searchResultKeys as $key => $value) $orderByCases[] = "WHEN {$value} THEN {$key}";
$orderByCasesString = implode(" ", $orderByCases);
$query->orderByRaw("CASE ID_NUM {$orderByCasesString} END");
$query->whereIn('id_num', $searchResultKeys);
return $query;

$orderByCases = [];
foreach ($searchResultKeys as $key => $value) $orderByCases[] = "WHEN {$value} THEN {$key}";
$orderByCasesString = implode(" ", $orderByCases);
$query->orderByRaw("CASE ID_NUM {$orderByCasesString} END");
$query->whereIn('id_num', $searchResultKeys);
return $query;

In MySQL, I believe you can use FIELD() to achieve the same effect but I use SQL Server so I had to go with this approach. It's a bit dirty but I couldn't figure out any other way to preserve search relevancy as returned by meilisearch. As I mentioned above, I have some other logic in the applySearchToTableQuery() method now because I've already "hacked" around the issue. The snippet I posted for ordering is just my workaround solution. Any other resolutions are appreciated!

Patrick Boivin•16mo ago

Hey @p_vinny, thanks for the follow-up, this is super interesting!

In MySQL, I believe you can use FILLED()

Did you mean FIELD()? If so, it appears that using CASE WHEN/THEN is the recommended workaround for SQL server. (I'm sure you've seen all the posts on stack overflow...)

VinnyOP•16mo ago

Yes, sorry, I meant FIELD(). And that was indeed the recommended alternative in stack overflow :P

Patrick Boivin•16mo ago

TIL! I've done workarounds in the past for similar issues... FIELD() will definitely simplify some of my code and make better use of the DB.

Robert Mann•11mo ago

@Patrick Boivin did you accomplish this with MySQL and if so would you mind sharing your new applySearchToTableQuery function so I don't have to reinvent the wheel?

Patrick Boivin•11mo ago

The context is a bit lost for me... I don't think I kept any of these tests. Maybe this can help, regarding the use of ORDER BY FIELD() in MySQL : https://dba.stackexchange.com/a/109126

Database Administrators Stack Exchange

How does ORDER BY FIELD() in MySQL work internally

I understand how ORDER BY clause works and how the FIELD() function works. What i want to understand is how the both of them work together to sort. How are the rows retrieved and how is the sort ...

Gaming

Programming

Table Builder + Scout: Search Result Order