Sorting search results by relevance in Laravel full-text search upin a MariaDB

Question

How can I sort by relevance against my search term in Laravel when performing a full-text search?

Migration

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::dropIfExists('posts');

        Schema::create('posts',function (Blueprint $table){
            // More columns will be added in next migrations.
            $table->id();
            $table->string('name');
            $table->fulltext(['name']);
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('posts');
    }
};

Model

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
   use HasFactory;
   protected $table="posts";
}

I perform a full-text search in a controller like this:

class PostController extends Controller
{
  public function search()
  {
     $searchterm = $request->get('searchterm');
     $qb = Post::query();
     
     if(!empty($searchterm)){
         $qb=$qb->whereFullText(['name'],$searchterm);
     }

     $page = $request->get('page')??1;
     $limit = $request->get('limit')??20;

     $results = $qb->offset(($page - 1) * $limit)
            ->simplePaginate($limit);

     return new JsonResponse($results,200);
  }
}

However, I need to figure out how to return the results ordered by relevance in my controller. I want the closest matches to appear first. How can I achieve this?

Answer 1

An approach it to place raw query parts such as:

class PostController extends Controller
{
  public function search()
  {
     $searchterm = $request->get('searchterm');
     $qb = Post::query();
     
     if(!empty($searchterm)){
        $qb=$qb->whereRaw("MATCH(name) AGAINST(? IN BOOLEAN MODE)",["*".$searchterm."*"])
                ->orderByRaw("MATCH(name) AGAINST(?) DESC", ["*".$searchterm."*"]);
     }

     $page = $request->get('page')??1;
     $limit = $request->get('limit')??20;

     $results = $qb->offset(($page - 1) * $limit)
            ->simplePaginate($limit);

     return new JsonResponse($results,200);
  }
}

I used the whereRaw and orderByRaw in order to sort and filter the results. This is applicable ONLY when using mysql or mariadb, and it is only its downside especially when implementing an application available for multiple rdbms (eg. your app supports both mysql and postgres).